45 useful Oracle query statements

Here we will introduce more than 40 useful Oracle query statements, covering date operations, server information retrieval, execution status retrieval, database size calculation, and other queries. These are essential skills for all Oracle developers! Obtain the first day of the current month by running this command

Here we will introduce more than 40 useful Oracle query statements, covering date operations, server information retrieval, execution status retrieval, database size calculation, and other queries. These are essential skills for all Oracle developers! Obtain the first day of the current month. Run this command.

Here we will introduce more than 40 useful Oracle query statements, covering date operations, server information retrieval, execution status retrieval, database size calculation, and other queries. These are essential skills for all Oracle developers!

Date/time query

1. Obtains the first day of the current month.

   Run this command to quickly return the first day of the current month. You can replace "SYSDATE" with any date value to specify the date to be queried.

   SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"     FROM DUAL;

2. Returns the last day of the current month.

   This query statement is similar to the preceding statement and takes full care of the leap year. Therefore, when there is a 29th in February, 29/2 is returned. You can replace "SYSDATE" with any date value to specify the date to be queried.

   SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"     FROM DUAL;

3. Obtain the first day of the current year

   The first day of each year is January 1. This query statement can be used in the stored procedure. You need to perform some calculations on the first day of the current year. You can replace "SYSDATE" with any date value to specify the date to be queried.

   SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

4. Returns the last day of the current year.

   Similar to the preceding query statement. You can replace "SYSDATE" with any date value to specify the date to be queried.

   SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

5. Returns the number of days of the current month.

   This statement is very useful and can calculate the number of days in the current month. You can replace "SYSDATE" with any date value to specify the date to be queried.

   SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days  FROM DUAL;

6. Get the remaining days of the current month

   The following statement is used to calculate the remaining days of the current month. You can replace "SYSDATE" with any date value to specify the date to be queried.

   SELECT SYSDATE,       LAST_DAY (SYSDATE) "Last",       LAST_DAY (SYSDATE) - SYSDATE "Days left"  FROM DUAL;

7. Returns the number of days between two dates.

   Use this statement to obtain the number of days for self-check on two different dates.

   SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)          num_of_days  FROM DUAL;ORSELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

   If you want to query the days of a specific date, you can use the second query statement. This example calculates the number of days an employee takes to work.

8. Displays the start and end dates of each month from the current year to the previous month.

   This is a clever query statement used to display the start and end dates of each month in the current year. You can use this statement for some types of calculations. You can replace "SYSDATE" with any date value to specify the date to be queried.

   SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,       TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date  FROM XMLTABLE (          'for $i in 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. Obtain the number of seconds that have passed until now (starting from)

   SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning  FROM DUAL;

10. Obtain the remaining seconds for today (until 23:59:59)

    SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left  FROM DUAL;

    Data Dictionary Query
11. Check whether a specified table exists in the current database mode.

    This is a simple query statement used to check whether the current database has the table you want to create and allow you to re-run the table creation script, this can also check whether the current user has created a specified table (based on the environment in which the query statement runs to query ).

    SELECT table_name  FROM user_tables WHERE table_name = 'TABLE_NAME';

12. Check whether a specified Column exists in the current table.

    This is a simple query statement to check whether a specified Column exists in the TABLE. It is useful when you try to use alter table to add a new column to the TABLE, it will prompt you whether the column already exists.

    SELECT column_name AS FOUND  FROM user_tab_cols WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

13. Display table structure

    The query statement displays the DDL status of any table. Note that we have submitted 'table' as the first information. This query statement can also be used to obtain the DDL status information of any database object. For example, you only need to replace the first parameter with 'view', and the second parameter with the VIEW name to query the view ddl information.

    SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

14. Get Current Mode

    This is another query statement that can obtain the name of the current mode.

    SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

15. Modify Current Mode

    This is another query statement that can modify the current mode. It is very useful when you want your script to run under a specified user, and this is a safe method.

    ALTER SESSION SET CURRENT_SCHEMA = new_schema;

    Database Management Query
16. Database Version Information

    Back to Oracle database version

    SELECT * FROM v$version;

17. Default database information

    Returns some default system information.

    SELECT username,       profile,       default_tablespace,       temporary_tablespace  FROM dba_users;

18. Database character settings

    Display the character settings of the database

    SELECT * FROM nls_database_parameters;

19. Obtain the Oracle version

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

20. Store case-sensitive data, but the index is case-insensitive

    Sometimes you may want to query some independent data in the database, and you may use UPPER (..) = UPPER (..) to perform case-insensitive queries, so you want to make the index case-insensitive and not take up so much space. This statement can meet your needs.

    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 is used to adjust the tablespace size.

    ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

22. Checklist automatic expansion Switch

    Check whether automatic expansion is enabled in the given tablespace.

    SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;(OR)SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

23. Add a data file to a tablespace

    Add a data file to a tablespace

    ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'    SIZE 1000M AUTOEXTEND OFF;

24. Increase the size of data files

    Increase the size of the specified tablespace.

    ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;

25. Query the actual size of a Database

    Returns the actual size of the database in GB.

    SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

26. Queries the size of data in the database or database Usage Details

    The space occupied by data in the database.

    SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

27. Query mode or user size

    Size of user space in MB

    SELECT SUM (bytes / 1024 / 1024) "size"  FROM dba_segments WHERE 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$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 Query
29. Queries the user's CPU usage
    

    This statement is used to display the CPU usage of each user and help the user understand the database load.

    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 the progress of a persistent Database Query

    Displays the progress of long queries in the running state.

    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. Obtains the current session ID, process ID, and client ID.

    This is intended for users who want to use the process ID and session ID to make 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 ID of the database process.

• V $ PROCESS. SPID is the ID of the background PROCESS of the database server.

• V $ SESSION. process is the client process id, ON windows it IS: separated the first # is the process id on the client AND 2nd one is the thread id.

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

    SELECT CREATED, TIMESTAMP, last_ddl_time  FROM all_objects WHERE     OWNER = 'MYSCHEMA'       AND OBJECT_TYPE = 'TABLE'       AND OBJECT_NAME = 'EMPLOYEE_TABLE';

33. Queries the first 10 SQL statements read by each execution.

    SELECT *  FROM (  SELECT ROWNUM,                 SUBSTR (a.sql_text, 1, 200) 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. Query and display the actual Oracle connection in the view

    SELECT osuser,         username,         machine,         program    FROM v$sessionORDER BY osuser;

35. Query and display the groups that open the connection by opening the Connection Program

    SELECT program application, COUNT (program) Numero_Sesiones    FROM v$sessionGROUP BY programORDER BY Numero_Sesiones DESC;

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

    SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones    FROM v$sessionGROUP BY usernameORDER BY Numero_Sesiones DESC;

37. Get the number of objects of the owner

    SELECT owner, COUNT (owner) number_of_objects    FROM dba_objectsGROUP BY ownerORDER BY number_of_objects DESC;

    Practical/mathematical-related queries
38. Convert numeric values into text

    For more information, see Converting number into words in Oracle.

    SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;

    Output:

    one thousand five hundred twenty-six

39. Query strings in the source code of the package

    This query statement searches for 'foo _ SOMETHING 'in the source code of all packages to help you find specific stored procedures or function calls in the source code.

    --search a string foo_something in package source codeSELECT *  FROM dba_source WHERE UPPER (text) LIKE '%FOO_SOMETHING%' AND owner = 'USER_NAME';

40. Insert data into a table separated by commas

    When you want to insert strings separated by commas into a table, you can use other query statements, such as IN or not in. Here, we convert 'aa, BB, CC, DD, EE, ff' to a table containing AA, BB, and CC as a row, in this way, you can easily insert these strings into other tables and perform related operations quickly.

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) IS NOT NULL;

41. Query the last record in the table

    This query statement is very direct. If the table does not have a primary key, or you are not sure whether the maximum primary key of the record is the latest record, you can use this statement 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);

42. Multiplication of row data in Oracle

    This query statement uses some complex mathematical functions to multiply the values of each row. For more information, see 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)  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 pile of random data to test how good the database is. The following query statement can satisfy your needs. It can generate random data in Oracle and insert it into the table. For more information, see Random Data in Oracle

    SELECT LEVEL empl_id,           MOD (ROWNUM, 50000) dept_id,           TRUNC (DBMS_RANDOM.VALUE (1000, 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 (20, 50)) address      FROM DUALCONNECT BY LEVEL < 10000;

44. Generate random values in Oracle

    This is a common Oracle random number generator. This generates a random value between 0 and 100. If you want to set the value range, you can change the multiplier.

    --generate random number between 0 and 100SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

45. Check whether the table contains any data

    You can use count (*) to view the number of rows in the Table. However, this query statement is efficient and fast, in addition, we just want to know whether the table contains any data.

    SELECT 1  FROM TABLE_NAME WHERE ROWNUM = 1;

Original article address: 45 very useful Oracle query statements. Thank you for sharing them.