45 very useful Oracle query statements (from the open source Chinese community)

Date/Time Related queries

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

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

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

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

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

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

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

16. 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 '. ');

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

20. 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
22. 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 ';

24. 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 ';

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

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

30. 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
32. Database version Information

    Back to Oracle database version

    SELECT * from V$version;

34. Database default Information

    Returns some system-default information

    SELECT username, profile       ,       default_tablespace,       temporary_tablespace from  dba_users;

36. Database character setting information

    Display character setting information for a database

    SELECT * from Nls_database_parameters;

38. Get Oracle version

    SELECT VALUE from  v$system_parameter WHERE name = ' compatible ';

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

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

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

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

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

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

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

54. 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 ';

56. 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
58. 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;

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

62. 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 ');

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

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

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

66. Query the top 10 SQL for each execution read

    SELECT * FROM  (  select ROWNUM,                 SUBSTR (A.sql_text, 1, a) 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;

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

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

70. Querying and displaying groups that open connections by opening the connection program

    SELECT program Application, COUNT (program) Numero_sesiones from    V$sessiongroup by Programorder by Numero_sesiones D ESC;

72. 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_ Sesiones DESC;

74. Gets 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
76. Convert Values to Text

    More information can be viewed: converting number into words in Oracle

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

    Output:

    One thousand five hundred twenty-six

78. Querying strings in the source code of a 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.

    --search a string foo_something in package source Codeselect * from  dba_source WHERE UPPER (text) like '%foo_something % ' and owner = ' user_name ';

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

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

   SELECT * FROM  employees WHERE ROWID in (SELECT MAX (ROWID) from employees);(OR) SELECT * FROM Employeesminusselect *
        from Employees WHERE ROWNUM < (SELECT COUNT (*) from employees);

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

   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)  SE Lect EXP (SUM (LN (num))) * Val from    tbl, Sign_valgroup by Val;
        

6. generate random data at 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. For more information, you can view the Random Data in Oracle

   select level empl_id, MOD (rownu M, 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)), ' dd-mm-yyyy ') DOB, dbms_random. STRING (' x ', Dbms_random. VALUE (+)) address from Dualconnect to level < 10000; 

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

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

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

    SELECT 1 from  table_name WHERE ROWNUM = 1;

45 very useful Oracle query statements (from the open source Chinese community)