45 very useful summary of Oracle query statements

Date/Time Related queries

1. Get 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.

The code is as follows:
Select TRUNC (sysdate, ' month ') "first day of the current MONTH"
From DUAL;

2. 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.

The code is as follows:
Select TRUNC (Last_day (sysdate)) "Last day of the current month"
From DUAL;

3. Get 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.

The code is as follows:
Select TRUNC (Sysdate, ' year ') "Year first day" from DUAL;

4. Get 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.

The code is as follows: Select Add_months (TRUNC (Sysdate, ' Year '), 1 "year last day" from DUAL

5. 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.

The code is as follows:
SELECT CAST (To_char (Last_day (sysdate), ' DD ') as INT) number_of_days from DUAL;

6. 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.

The code is as follows:
SELECT Sysdate,
Last_day (sysdate) "Last",
Last_day (sysdate)-sysdate "Days left"
From DUAL;

7. Get the number of days between two dates
Use this statement to get the number of days for two different date self tests.

The code is as follows:
SELECT ROUND ((Months_between (' 01-feb-2014 ', ' 01-mar-2012 ') *, 0) num_of_days from DUAL;
OR
SELECT 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.

8. Displays the date of the current year until 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.

The code is as follows:
SELECT add_months (TRUNC (sysdate, ' MONTH '), i) start_date,
TRUNC (Last_day (add_months (sysdate, i)) end_date
From XMLTABLE (
' for $i with 0 to Xs:int (D) return $i '
Passing XMLELEMENT (
D
Floor (
Months_between (
Add_months (TRUNC (Sysdate, ' year ')-1, 12),
sysdate)))
COLUMNS i INTEGER PATH '. ');

9. Get the number of seconds in the past so far (counting from 00:00)

The code is as follows: SELECT (Sysdate-trunc (sysdate)) * * $ num_of_sec_since_morning from DUAL;

10. Get the number of seconds left today (until 23:59:59)

The code is as follows: SELECT (TRUNC (sysdate+1)-sysdate) * * * * num_of_sec_left from DUAL;

Data dictionary Query

11. Check if 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).

The code is as follows:
SELECT table_name
From User_tables
WHERE table_name = ' table_name ';

12. Check if 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.

The code is as follows:
SELECT column_name as FOUND
From User_tab_cols
WHERE table_name = ' table_name ' and column_name = ' column_name ';

13. Display 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.

The code is as follows:
SELECT dbms_metadata.get_ddl (' TABLE ', ' table_name ', ' user_name ') from DUAL;

14. Get the current mode
This is another query statement that can get the name of the current pattern.

The code is as follows:
SELECT sys_context (' Userenv ', ' Current_schema ') from DUAL;

15. Modify 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.

The code is as follows:
ALTER SESSION SET current_schema = New_schema;

Database Management Queries

16. Database version Information

Back to Oracle database version

The code is as follows:
SELECT * from V$version;

17. Database default Information

Returns some system-default information

The code is as follows:
SELECT username,
Profile
Default_tablespace,
Temporary_tablespace
From Dba_users;

18. Database Character setting information
Display character setting information for a database

The code is as follows:
SELECT * from Nls_database_parameters;

19. Get the Oracle version

The code is as follows:
SELECT VALUE
From V$system_parameter
WHERE name = ' compatible ';

20. 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.

The code is as follows:
CREATE TABLE tab (col1 VARCHAR2 (10));
CREATE INDEX idx1
On tab (UPPER (col1));
ANALYZE TABLE a COMPUTE STATISTICS;

21. Adjust the tablespace without adding data files

Another DDL query to resize the table space

The code is as follows:
ALTER DATABASE datafile '/work/oradata/startst/star02d.dbf ' resize 2000M;

22. Check the table space for automatic expansion switches

Query whether the auto-expand switch is turned on in a given table space

The code is as follows:
SELECT SUBSTR (file_name, 1,), autoextensible from Dba_data_files;
(OR)
SELECT Tablespace_name, autoextensible from Dba_data_files;

23. Adding data files to the Tablespace

To add a data file to a table space

The code is as follows:
ALTER tablespace data01 ADD datafile '/work/oradata/startst/data01.dbf '
SIZE 1000M autoextend OFF;

24. Increase the size of the data file

To increase the size of the specified table space

The code is as follows:
ALTER DATABASE datafile '/u01/app/test_data_01.dbf ' RESIZE 2G;

25. Querying the actual size of the database

gives the actual size of the database in gigabytes

The code is as follows:
SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_data_files;

26. Querying the size of data in a database or database usage details
Gives the amount of space the data occupies in the database

The code is as follows:
SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_segments;

27. Query mode or the size of the user
The size of the user's space in megabytes

The code is as follows:
Select SUM (bytes/1024/1024) "Size"
From Dba_segments
WHERE owner = ' &owner ';

28. 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.

The code is as follows:
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_value
ORDER by S.sid, t.piece;

Performance-related queries
29. Query the user CPU usage rate

This statement is used to show each user's CPU usage, helping the user understand the database load situation

The code is as follows:
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 NULL
ORDER by VALUE DESC;

30. Query database Long Query progress
Show the progress of long queries running

The code is as follows:
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 ')
and totalwork > 0
ORDER by Elapsed_seconds;

31. Get the current session ID, process ID, client ID, etc.
This is intended for users who want to use the process ID and session ID to do some voodoo magic.

The code is as follows:
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 ');

v$session. SID and V$session. serial# is the database process ID
v$process. SPID is the database server background process ID
v$session. Process is a client process ID, on the Windows it is:separated the first # was the process ID on the customer and 2nd one is the Threa D ID.

32. Query for a specific pattern or the last SQL statement executed in the table

The code is as follows:
SELECT CREATED, TIMESTAMP, Last_ddl_time
From All_objects
WHERE OWNER = ' MySchema '
and object_type = ' TABLE '
and object_name = ' employee_table ';

33. Query the first 10 SQL for each execution read

The code is as follows:
SELECT *
From (SELECT ROWNUM,
SUBSTR (A.sql_text, 1, Sql_text),
TRUNC (
A.disk_reads/decode (a.executions, 0, 1, a.executions))
Reads_per_execution,
A.buffer_gets,
A.disk_reads,
A.executions,
A.sorts,
A.address
From V$sqlarea A
ORDER by 3 DESC)
WHERE ROWNUM < 10;

34. Querying and displaying the actual Oracle connection in the view

The code is as follows:
SELECT Osuser,
Username
Machine
Program
From V$session
ORDER by Osuser;

35. Query and display the group that opened the connection by opening the connection program

The code is as follows:
SELECT program Application, COUNT (program) Numero_sesiones
From V$session
GROUP by Program
ORDER by Numero_sesiones DESC;

36. Query and display the number of sessions for users and users connected to Oracle

The code is as follows:
SELECT username usuario_oracle, COUNT (username) numero_sesiones
From V$session
GROUP by username
ORDER by Numero_sesiones DESC;

37. Get the number of objects owned by the owner

The code is as follows:
SELECT owner, COUNT (owner) number_of_objects
From Dba_objects
GROUP by Owner
ORDER by Number_of_objects DESC;

38. Convert Values to text
The code is as follows:

SELECT To_char (to_date (1526, ' J '), ' JSP ') from DUAL;

Output:

The code is as follows:
One thousand five hundred twenty-six

39. Querying strings in the source code of the package

This query will search the source code of all packages for ' foo_something ', which can help the user to find a specific stored procedure or function call in the sources.

The code is as follows:
--search a string foo_something in package source code
SELECT *
From Dba_source
WHERE UPPER (text) like '%foo_something% '
and owner = ' user_name ';

40. Insert comma-delimited data into the table

When you want to insert a comma-separated string into a table, you can use other query statements, such as in or not. Here we convert ' aa,bb,cc,dd,ee,ff ' to a table containing AA,BB,CC and so on, so you can easily insert these strings into other tables and quickly do some related operations.

The code is as follows:
With CSV
As (SELECT ' aa,bb,cc,dd,ee,ff '
As CSVData
From DUAL)
SELECT regexp_substr (Csv.csvdata, ' [^,]+ ', 1, level) Pivot_char
From DUAL, CSV
CONNECT by Regexp_substr (Csv.csvdata, "[^,]+ ', 1, level) are not NULL;

41. Querying the last record in a table

This query is straightforward, there is no primary key in the table, or if the user is unsure whether the record maximum primary key is the most recent, this statement can be used to query the last record in the table.

The code is as follows:
SELECT *
From Employees
WHERE ROWID in (SELECT MAX (ROWID) from employees);

(OR)

SELECT * FROM Employees
Minus
SELECT *
From Employees
WHERE ROWNUM < (SELECT COUNT (*) from employees);

42. Doing row data multiplication in Oracle

This query statement uses some complex mathematical functions to multiply the values of each row. For more information, please refer to: Row Data multiplication in Oracle

The code is as follows:
With TBL
As (SELECT-2 num from DUAL
UNION
SELECT-3 num from DUAL
UNION
SELECT-4 num from DUAL),
Sign_val
As (SELECT case MOD (COUNT (*), 2) when 0 then 1 ELSE-1 END val
From TBL
WHERE num < 0)
SELECT EXP (SUM (LN (num))) * Val
From TBL, Sign_val
GROUP by Val;

43. Generate random data in Oracle

Every developer wants to be able to easily generate a bunch of random data to test the database, the following query will satisfy you, it can generate random data in Oracle to insert into the table.

The code is as follows:

SELECT level empl_id,
MOD (ROWNUM, 50000) dept_id,
TRUNC (Dbms_random. VALUE (+, 500000), 2) salary,
DECODE (ROUND (dbms_random. VALUE (1, 2)), 1, ' M ', 2, ' F ') gender,
To_date (
ROUND (Dbms_random. VALUE (1, 28))
|| ‘-‘
|| ROUND (Dbms_random. VALUE (1, 12))
|| ‘-‘
|| ROUND (Dbms_random. VALUE (1900, 2010)),
' Dd-mm-yyyy ')
Dob
Dbms_random. STRING (' x ', Dbms_random. VALUE (+)) address
From DUAL
CONNECT by level < 10000;

44. Generate random values in Oracle

This is the normal old random numeric generator of Oracle. This can generate a random number between 0-100, and if you want to set the range of values yourself, then change the multiplier.

The code is as follows:
--generate random number between 0 and 100
SELECT ROUND (dbms_random. VALUE () * () + 1 as random_num from DUAL;

45. Check if the table contains any data

This can be written in many words, you can use COUNT (*) to see the number of rows in the table, but the query statement is efficient and fast, and we just want to know if there is any data in the table.

The code is as follows:
SELECT 1
From table_name
WHERE ROWNUM = 1;

45 very useful summary of Oracle query statements