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

Source: Internet
Author: User
Tags mathematical functions session id cpu usage

Date/Time Related queries

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

      Select TRUNC (Last_day (sysdate)) "Last day of the current month" from     DUAL;
    3. 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;
    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. 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;
    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.

      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.

      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.

    8. 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 '. ');
    9. 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;
    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. 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 ';
    12. 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 ';
    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 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. 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
    16. Database version Information

      Back to Oracle database version

      SELECT * from V$version;
    17. Database default Information

      Returns some system-default information

      SELECT username, profile       ,       default_tablespace,       temporary_tablespace from  dba_users;
    18. Database character setting information

      Display character setting information for a database

      SELECT * from Nls_database_parameters;
    19. Get Oracle version
      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.

    21. 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;
    22. 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;
    23. 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;
    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 the data occupies in the database

      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

      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.

      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
    29. 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;
    30. 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;
    31. 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.

    32. 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 ';
    33. 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;
    34. Querying and displaying the actual Oracle connection in the view
      SELECT Osuser,         username, machine, program from         v$sessionorder by    Osuser;
    35. 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;
    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_ Sesiones DESC;
    37. 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
    38. 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;


      One thousand five hundred twenty-six
    39. 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 ';
    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;
  1. 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);
  2. 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;
  3. 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; 
  4. 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;
  5. 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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.