- Gets the first day of the current month
Run this command to quickly return to the first day of the current month. You can replace "sysdate" with any date value to specify the date of the query.
Select TRUNC (sysdate, ' month ') "first day of the current MONTH" from DUAL;
- Get the last day of the current month
This query is similar to the one above and takes care of a leap year, so when the number is 29th in February, it returns 29/2. You can replace "sysdate" with any date value to specify the date of the query.
Select TRUNC (Last_day (sysdate)) "Last day of the current month" from DUAL;
- Gets the first day of the current year
The first day of every year is January 1, and this query statement can be used in a stored procedure that requires some calculation on the first day of the current year. You can replace "sysdate" with any date value to specify the date of the query.
Select TRUNC (Sysdate, ' year ') "Year first day" from DUAL;
- Gets the last day of the current year
Similar to the query statement above. You can replace "sysdate" with any date value to specify the date of the query.
Select Add_months (TRUNC (Sysdate, ' Year '), 1 "year last day" from DUAL
- Get the number of days in the current month
This statement is useful to calculate the number of days in the current month. You can replace "sysdate" with any date value to specify the date of the query.
SELECT CAST (To_char (Last_day (sysdate), ' DD ') as INT) number_of_days from DUAL;
- Get the number of days remaining in the current month
The following statement is used to calculate the number of days remaining in the current month. You can replace "sysdate" with any date value to specify the date of the query.
Select Sysdate, last_day (sysdate) "Last", Last_day (sysdate)-sysdate ' days left ' from DUAL;
- Get the number of days between two dates
Use this statement to get the number of days for two different date self tests.
SELECT ROUND ((Months_between (' 01-feb-2014 ', ' 01-mar-2012 ') *, 0) num_of_days from dual;orselect TRUNC ( Sysdate)-TRUNC (e.hire_date) from employees;
If you need to query for days of some specific date, you can use a second query statement. This example calculates the number of days the employee is in the job.
- Displays the date that the current year ends at the beginning and end of each month
This is a very smart query that displays the start and end dates of each month of the current year, which you can use to do some type of calculation. You can replace "sysdate" with any date value to specify the date of the query.
SELECT add_months (TRUNC (sysdate, ' MONTH '), i) start_date, TRUNC (Last_day (add_months, i)) sysdate F ROM XMLTABLE ( ' for $i in 0 to Xs:int (d) return $i ' passing XMLELEMENT ( D, Floor (Months_between (
add_months (TRUNC (Sysdate, ' year ')-1, N), sysdate)))) COLUMNS i INTEGER PATH '. ');
- Gets the number of seconds in the past so far (counting from 00:00)
SELECT (Sysdate-trunc (sysdate)) * (*) * num_of_sec_since_morning from DUAL;
- Get the number of seconds left today (until 23:59:59 end)
SELECT (TRUNC (sysdate+1)-sysdate) * $ num_of_sec_left from DUAL;Data dictionary Query
- Checks whether the specified table exists in the current database mode
This is a simple query that checks to see if the current database has the table you want to create, allowing you to rerun the CREATE table script, which also checks to see if the current user has created the specified table (based on the environment in which the query was run).
SELECT table_name from user_tables WHERE table_name = ' table_name ';
- Checks whether the specified column exists in the current table
This is a simple query to check if the table has a specified column, which is useful when you try to use ALTER table to add a new new to the table, and it will prompt you if the column already exists.
SELECT column_name as FOUND from user_tab_cols WHERE table_name = ' table_name ' and column_name = ' column_name ';
- Show Table Structure
This query statement displays the DDL state information for any table. Please note that we have submitted ' TABLE ' as the first message. This query statement can also be used to obtain DDL state information for any database object. For example, you can query the view's DDL information by replacing the first parameter with a ' view ' and the second by changing the name of the view.
SELECT dbms_metadata.get_ddl (' TABLE ', ' table_name ', ' user_name ') from DUAL;
- Get current mode
This is another query statement that can get the name of the current pattern.
SELECT sys_context (' Userenv ', ' Current_schema ') from DUAL;
- Modifying the current mode
This is another query statement that modifies the current schema, which is useful when you want your script to run under the specified user, and it's a very safe way to do it.
ALTER SESSION SET current_schema = New_schema;Database Management Queries
- Database version Information
Back to Oracle database version
SELECT * from V$version;
- Database default Information
Returns some system-default information
SELECT username, profile , default_tablespace, temporary_tablespace from dba_users;
- Database character setting information
Display character setting information for a database
SELECT * from Nls_database_parameters;
- Get Oracle version
SELECT VALUE from v$system_parameter WHERE name = ' compatible ';
- Store case-sensitive data, but the index is case-insensitive
Sometimes you might want to query a database for some independent data, you might use UPPER (..) = UPPER (..) for case-insensitive queries, so you want the index to be case-insensitive and take up so much space, this statement will solve your needs.
CREATE TABLE tab (col1 VARCHAR2 (10)); CREATE INDEX idx1 on tab (UPPER (col1)); ANALYZE TABLE a COMPUTE STATISTICS;
- To resize a tablespace without adding a data file
Another DDL query to resize the table space
ALTER DATABASE datafile '/work/oradata/startst/star02d.dbf ' resize 2000M;
- Automatic expansion switch for check table space
Query whether the auto-expand switch is turned on in a given table space
Select SUBSTR (file_name, 1,), autoextensible from Dba_data_files; (OR) Select Tablespace_name, autoextensible from Dba_ Data_files;
- Add a data file to a table space
To add a data file to a table space
ALTER tablespace data01 ADD datafile '/work/oradata/startst/data01.dbf ' SIZE 1000M autoextend OFF;
- Increase the size of the data file
To increase the size of the specified table space
ALTER DATABASE datafile '/u01/app/test_data_01.dbf ' RESIZE 2G;
- Querying the actual size of the database
gives the actual size of the database in gigabytes
SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_data_files;
- Querying the size of data in a database or database usage details
Gives the amount of space the data occupies in the database
SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_segments;
- Query mode or the size of the user
The size of the user's space in megabytes
Select SUM (bytes/1024/1024) "Size" from dba_segments WHERE owner = ' &owner ';
- Querying the last SQL query used by each user in the database
This query statement displays the last SQL statement used by each user in the current database.
SELECT S.username | | ' (' | | | s.sid | | ')-' | | S.osuser UNAME, s.program | | '-' | | s.terminal | | ' (' | | | s.machine | | ') ' PROG, s.sid | | '/' | | s.serial# SID, s.status "status", p.spid, sql_text sqltext from v$sqltext_with_newlines T, v$session S, v$process P WHERE t.address = s.sql_address and p.addr = s.paddr (+) and T.hash_value = S.sql_hash_ Valueorder by S.sid, t.piece;Performance-related queries
- Querying user CPU Usage
This statement is used to show each user's CPU usage, helping the user understand the database load situation
SELECT ss.username, SE. SID, value/100 cpu_usage_seconds from v$session ss, V$sesstat SE, v$statname sn WHERE se. statistic# = sn. Statistic# and NAME like '%cpu used by this session% ' and se. SID = ss. SID and ss.status = ' ACTIVE ' and ss.username is not nullorder by VALUE DESC;
- Query database Long Query progress
Show the progress of long queries running
SELECT A.sid, a.serial#, b.username, opname operation, target OBJECT, TRUNC (elapsed_seconds , 5) "ET (s)", to_char (start_time, ' HH24:MI:SS ') start_time, ROUND ((sofar/totalwork) * 2) "Complete (% ) "From v$session_longops A, v$session b WHERE a.sid = B.sid and B.username not in (' SYS ', ' SYSTEM ') C18/>and totalwork > 0ORDER by Elapsed_seconds;
- Gets the current session ID, process ID, client ID, and so on
This is intended for users who want to use the process ID and session ID to do some voodoo magic.
SELECT B.sid, b.serial#, a.spid ProcessID, b.process clientpid from v$process A, v$session b WHERE A.addr = b.paddr and B.audsid = USERENV (' SessionID ');