45 useful Oracle query statements

45 useful Oracle query statements
Obtain 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; obtain the last day of the current month. The query statement is similar to the preceding statement and takes full care of the leap year, so when there is 29th in February, 29/2 will be 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; 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; obtain the last Day of the current Year, which is 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 this statement is very useful, the number of days in the current month can be calculated. 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; obtain 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; obtain 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; or select trunc (sysdate) -TRUNC (e. hire_date) FROM employees; if you want to query the number of days of a specific date, you can use the second query statement. This example calculates the number of days an employee takes to work. Display 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 of the current year, you can use this to calculate some types. 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 '. '); obtain the number of seconds in the past today (starting from) SELECT (SYSDATE-TRUNC (SYSDATE) * 24*60*60 num_of_sec_since_morni Ng from dual; 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 checks 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 allows 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'; 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'; displays the DDL status information 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; obtain the current mode. This is another query statement that obtains the NAME of the current mode. SELECT SYS_CONTEXT ('userenv', 'current _ scheme') from dual; modify the current mode. This is another query statement that can modify the current mode, it is very safe and useful when you want your script to run under a specified user. Alter session set CURRENT_SCHEMA = new_schema; Database Management returns the Oracle database version SELECT * FROM v $ version for querying database version information; database returns some default system information such as SELECT username, profile, default_tablespace, temporary_tablespace FROM dba_users; SELECT * FROM nls_database_parameters; obtain Oracle version 3 select value from v $ system_parameter WHERE name = 'compute '; store case-sensitive data, but the index is case-insensitive. In some cases, you may want to query some independent data in the database. UPPER (..) may be used (..) = UPPER (...) is used for 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; adjust the tablespace that does not add data files to another DDL query to adjust the tablespace size. alter database datafile '/work/oradata/STARTST/STAR02D. dbf 'resize 2000 M; check whether the automatic expansion switch of the table space is enabled in the specified tablespace. select substr (file_name, 1, 50), autoextensible from dba_data_files; (OR) SELECT tablespace_name, autoextensible from dba_data_files; add data files in the tablespace ADD the data file alter tablespace data01 add datafile '/work/oradata/STARTST/data01.dbf' SIZE 1000 m autoextend off to the TABLESPACE; increase the data file size to the specified tablespace. alter database datafile '/u01/app/test_data_01.dbf' RESIZE 2G; returns the actual size of the database in GB. select sum (bytes)/1024/1024/1024 as gb from dba_data_files; query the size of data in the database or the size of Data occupied by the database. select sum (bytes)/1024/1024/1024/as gb from dba_segments; query mode or user Select sum (bytes/1024/1024) "size" FROM dba_segments WHERE owner = '& owner '; query the last SQL statement 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 query queries the user's CPU usage. This statement is used Displays the CPU usage of each user, helping you understand the database load. 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; query the progress of a persistent query in the database. SELECT. sid,. serial #, B. username, opname OPERATION, target OBJE CT, 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. sid = B. sid AND B. username not in ('sys ', 'system') AND totalwork> 0 order by elapsed_seconds; get the current session ID, process ID, the client ID is provided to users who want to use the process ID and session ID to make voodoo magic. SELECT B. sid, B. serial #,. spid processid, B. process clientpid FROM v $ process a, v $ session B WHERE. 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. query the SELECT statement of the last SQL statement executed in a specific mode or table CREATED, TIMESTAMP, last_ddl_time FROM all_objects where owner = 'myscheme' AND OBJECT_TYPE = 'table' AND OBJECT_NAME = 'employee _ table '; query the first 10 SQL statements read by each execution. SELECT * FROM (SELECT ROWNUM, SUBSTR (. SQL _text, 1,200) SQL _text, TRUNC (. disk_reads/DECODE (a.exe cutions, 0, 1, a.exe cutions) reads_per_execution,. buffer_gets,. disk_reads, a.exe cutions,. sorts,. address FROM v $ sqlarea a order by 3 DESC) where rownum <10; query and display the actual Oracle connection SELECT osuser, username, machine, program FROM v $ sessionORDER BY osuser in the view; query and display the SELECT program application, COUNT (program) Numero_Sesiones FROM v $ sessionGROUP BY programORDER BY Numero_Sesiones DESC; query and display the number of sessions between Oracle users and users SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones FROM v $ sessionGROUP BY usernameORDER BY Numero_Se Siones DESC; obtain the number of objects owned BY the owner. SELECT owner, COUNT (owner) number_of_objects FROM dba_objectsGROUP BY ownerORDER BY number_of_objects DESC; practical/mathematical-related queries 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 queries the string in the source code of the package. This query statement searches for 'foo _ SOMETHING 'on the source code of all packages ', it can help users 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 '; 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.csv data,' [^,] + ', 1, LEVEL) pivot_char from dual, csvCONNECT BY REGEXP_SUBSTR (csv.csv data, '[^,] +', 1, LEVEL) is not null; the query statement for the last record in the query table 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 one, 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 ); perform row-data multiplication in Oracle. The 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; every developer wants to easily generate a pile of random data in Oracle to test the database. The following query statement can satisfy you, 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,500 000), 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,201 0), 'dd-MM-YYYY ') dob, DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50) address from dualconnect by level <10000; generate a random value in Oracle. This is a common Oracle random value 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 100 select round (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num from dual; 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, and we just want to know whether the table contains any data. SELECT 1 FROM TABLE_NAME where rownum = 1; if you know some useful query statements, it can reduce the burden on Oracle developers, so let's comment on it :)