45 very useful 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.

Select TRUNC (sysdate, ' month ') "first day of the current MONTH" from DUAL;

2 Get the last day of the current month the query is similar to the one above and takes care of the 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;

3 Get the first day of the current year the first day of the year is January 1, this query statement can be used in the stored procedure, need to do some calculations 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;

4 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

5 Gets 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_daysfrom DUAL;

6 Gets 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;

7 Gets the number of days between two dates use this statement to get two days of the self-test for a different date.

SELECT ROUND ((Months_between (' 01-feb-2014 ', ' 01-mar-2012 ') *, 0) Num_of_daysfrom DUAL; OR SELECT TRUNC (sysdate)-TRUNC (e.hire_date) from employees;

Note: If you need to query the number of days for a specific date, you can use the 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 the current year for each month, 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 (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, a),                             sysdate))           columns i INTEGER PATH '. ');

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

SELECT (Sysdate-trunc (sysdate)) * * Num_of_sec_since_morningfrom DUAL;

10 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

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 (in what context the query is run).

SELECT table_name from User_tableswhere table_name = ' table_name ';

12. Check if the specified column exists in the current table this is a simple query statement to check if the table has a specified column, which is useful when you try to add a new new to the table using ALTER table, which will prompt you if the column already exists.

SELECT column_name as FOUND from user_tab_colswhere table_name = ' table_name ' and column_name = ' column_name ';

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

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

SELECT sys_context (' Userenv ', ' Current_schema ') from DUAL;

15. Modify the current mode this is another query statement that can modify the current schema, which is useful when you want your script to run under the specified user, and this is a very safe way to do it.

ALTER SESSION SET current_schema = New_schema;

Database Management Queries

16. Database version information returned to Oracle database version

SELECT * from V$version;

17. The database default information returns some system default information

SELECT username, profile, Default_tablespace, temporary_tablespace from Dba_users;

18. Database Character setting information displays the character setting information for the database

SELECT * from Nls_database_parameters;

19. Get the Oracle version

SELECT VALUE from V$system_parameterwhere name = ' compatible ';

20. Store case-sensitive data, but the index is not case sensitive some times you may want to query the database for some independent data, may use UPPER (..) = UPPER (..) for case-insensitive queries, so you want the index is not case-sensitive, not occupy so much space, This statement happens to solve your needs.

CREATE TABLE tab (col1 VARCHAR2 (10)); CREATE INDEX idx1 on tab (UPPER (col1)); ANALYZE TABLE a COMPUTE STATISTICS;

21. Adjust tablespace without adding data file another DDL query to size the table space

ALTER DATABASE datafile '/work/oradata/startst/star02d.dbf ' resize 2000M;

22. Check the table space Auto-expansion switch query in a given table space whether the auto-expand switch is turned on

SELECT SUBSTR (file_name, 1,), autoextensible from Dba_data_files; (OR) SELECT Tablespace_name, autoextensible from Dba_data_files;

23. Adding data files to a tablespace add data files in a tablespace

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

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

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 in the database that the data occupies

SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_segments;

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

Select SUM (bytes/1024/1024) "size" from dba_segmentswhere owner = ' &owner ';

28. Query 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$se Ssion s, v$process p WHERE t.address = s.sql_address and p.addr = s.paddr (+) and T.hash_value = s.sq L_hash_valueorder by S.sid, t.piece;

29 performance-related queries 29. Query user CPU Usage This statement is used to show CPU usage per user and helps users 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;

30. Query database Long query progress shows the progress of long queries in operation

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) * 100, 2    ) "Complete (%)" From V$session_longops A, v$session b WHERE a.sid = B.sid and B.username not in (' SYS ', ' SYSTEM ') and TotalWork > 0ORDER by Elapsed_seconds;

31. Get the current session ID, process ID, client ID, etc. this is specifically provided to 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 bwhere A.add R = 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 the client process ID,

On Windows it is:separated the first # was the PROCESS ID on the client and 2nd the the THREAD ID.

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

SELECT CREATED, TIMESTAMP, last_ddl_time from all_objectswhere OWNER = ' MySchema ' and object_type = ' TABLE ' and object_name = ' employee_table ';

33. Query the first 10 reads for each execution

Sqlselect *  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

SELECT Osuser, username, machine, program from V$sessionorder by Osuser;

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

SELECT program application, COUNT [program] Numero_sesiones from V$sessiongroup to Programorder by Numero_sesiones DESC ;

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

SELECT username usuario_oracle, COUNT (username) numero_sesiones from V$sessiongroup by Usernameorder by Numero_sesione s DESC;

37. Get the number of objects owned by the owner

SELECT owner, COUNT (owner) number_of_objects from Dba_objectsgroup to Ownerorder by Number_of_objects DESC;

Practical/math-related queries

38. Convert Values to Text

SELECT To_char (to_date (1526, ' J '), ' JSP ') from DUAL;
Output: One thousand five hundred twenty-six

39. Query string in the source code of the package this query will search for ' foo_something ' on the source of all packages to help the user find a specific stored procedure or function call in the source.

--search a string foo_something in package source Codeselect * from Dba_sourcewhere UPPER (text) like '%foo_something% ' an D owner = ' user_name ';

40. Inserting comma-delimited data into a 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.

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, csvconnect by Regexp_substr (Csv.csvdata, ' [^,]+ ', 1, level) are not NULL;

41. Query the last record in the table This query statement 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 record, you can use this statement to query the table for the last record.

SELECT * from Employeeswhere ROWID in (select MAX (ROWID) from employees); (OR) SELECT * FROM Employeesminusselect * from Employeeswhere 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

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 (ABS (num))) * Val from Tbl, Sign_valgroup by Val;

43. Generate random Data in Oracle every developer wants to easily generate a bunch of random data to test the database, and the following query will satisfy you, and it can generate random data into the table in Oracle.

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,))               | | '-'               | | ROUND (Dbms_random. VALUE (1, a))               | | '-'               | | ROUND (Dbms_random. VALUE (1900,)),                ' DD-MM-YYYY ')                dob,            Dbms_random. STRING (' x ', Dbms_random. VALUE (+)) Address      from Dualconnect by level < 10000;

44. Generate random values in Oracle this is an old, generic, 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.

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

45. Check if the table contains any data this can be a lot of writing, you can use COUNT (*) to see the number of rows in the table, but this query statement is more efficient and fast, and we just want to know whether there is any data in the table.

SELECT 1 from Table_namewhere ROWNUM = 1;

45 very useful Oracle query statements