Summary of 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.

Address: http://www.jbxue.com/db/19890.html

Date/time query

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

Copy codeThe Code is as follows:
Select trunc (SYSDATE, 'month') "First day of current MONTH"
From dual;

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

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
Select trunc (SYSDATE, 'Year') "YEAR First Day" from dual;

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

Copy codeThe Code is as follows: SELECT ADD_MONTHS (TRUNC (SYSDATE, 'Year'), 12)-1 "YEAR Last Day" FROM DUAL

5. Get the number of days in 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.

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
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 obtain the number of days for self-check on two different dates.

Copy codeThe Code is as follows:
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 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. 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 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.

Copy codeThe Code is as follows:
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)

Copy codeThe Code is as follows: SELECT (SYSDATE-TRUNC (SYSDATE) * 24*60*60 num_of_sec_since_morning from dual;

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

Copy codeThe Code is as follows: 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 ).

Copy codeThe Code is as follows:
SELECT table_name
FROM user_tables
WHERE table_name = 'table _ name ';

12. Check whether the 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.

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
SELECT DBMS_METADATA.get_ddl ('table', 'table _ name', 'user _ name') from dual;

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

Copy codeThe Code is as follows:
SELECT SYS_CONTEXT ('userenv', 'current _ scheme') from dual;

15. modify the 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.

Copy codeThe Code is as follows:
Alter session set CURRENT_SCHEMA = new_schema;

Database Management Query

16. Database Version Information

Back to Oracle database version

Copy codeThe Code is as follows:
SELECT * FROM v $ version;

17. Default database information

Returns some default system information.

Copy codeThe Code is as follows:
SELECT username,
Profile,
Default_tablespace,
Temporary_tablespace
FROM dba_users;

18. database character settings
Display the character settings of the database

Copy codeThe Code is as follows:
SELECT * FROM nls_database_parameters;

19. Obtain the Oracle version

Copy codeThe Code is as follows:
SELECT VALUE
FROM v $ system_parameter
WHERE name = 'compute ';

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.

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
Alter database datafile '/work/oradata/STARTST/STAR02D. dbf' resize 2000 M;

22. Check the automatic expansion switch of the table space

Check whether automatic expansion is enabled in the given tablespace.

Copy codeThe Code is as follows:
Select substr (file_name, 1, 50), autoextensible from dba_data_files;
(OR)
SELECT tablespace_name, autoextensible from dba_data_files;

23. add data files in the tablespace

Add a data file to a tablespace

Copy codeThe Code is as follows:
Alter tablespace data01 add datafile '/work/oradata/STARTST/data01.dbf'
SIZE 1000 m autoextend off;

24. Increase the size of data files

Increase the size of the specified tablespace.

Copy codeThe Code is as follows:
Alter database datafile '/u01/app/test_data_01.dbf' RESIZE 2G;

25. query the actual size of the database

Returns the actual size of the database in GB.

Copy codeThe Code is as follows:
Select sum (bytes)/1024/1024/1024 as gb from dba_data_files;

26. query the size of data in the database or database Usage Details
The space occupied by data in the database.

Copy codeThe Code is as follows:
Select sum (bytes)/1024/1024/1024 as gb from dba_segments;

27. query mode or user size
Size of user space in MB

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
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_value
Order by s. sid, t. piece;

Performance Query
29. query 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.

Copy codeThe Code is as follows:
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 NULL
Order by value desc;

30. query the progress of long database queries
Displays the progress of long queries in the running state.

Copy codeThe Code is as follows:
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> 0
Order by elapsed_seconds;

31. Obtain the current session ID, process ID, client ID, etc.
This is intended for users who want to use the process ID and session ID to make voodoo magic.

Copy codeThe Code is as follows:
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

Copy codeThe Code is as follows:
Select created, TIMESTAMP, last_ddl_time
FROM all_objects
Where owner = 'myschema'
AND OBJECT_TYPE = 'table'
AND OBJECT_NAME = 'employee _ table ';

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

Copy codeThe Code is as follows:
SELECT *
FROM (select rownum,
SUBSTR (a. SQL _text, 1,200) SQL _text,
TRUNC (
A. disk_reads/DECODE (a.exe cutions, 0, 1, a.exe cutions ))
Reads_per_execution,
A. buffer_gets,
A. disk_reads,
A.exe cutions,
A. sorts,
A. address
FROM v $ sqlarea
Order by 3 DESC)
Where rownum <10;

34. query and display the actual Oracle connection in the view

Copy codeThe Code is as follows:
SELECT osuser,
Username,
Machine,
Program
FROM v $ session
Order by osuser;

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

Copy codeThe Code is as follows:
SELECT program application, COUNT (program) Numero_Sesiones
FROM v $ session
Group by program
Order by Numero_Sesiones DESC;

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

Copy codeThe Code is as follows:
SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
FROM v $ session
Group by username
Order by Numero_Sesiones DESC;

37. Get the number of objects of the owner

Copy codeThe Code is as follows:
SELECT owner, COUNT (owner) number_of_objects
FROM dba_objects
Group by owner
Order by number_of_objects DESC;

38. Convert numeric values into text
For more information, see Converting number into words in Oracle.

Copy codeThe Code is as follows:
SELECT TO_CHAR (TO_DATE (1526, 'J'), 'jsp ') from dual;

Output:

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
-- Search a string foo_something in package source code
SELECT *
FROM dba_source
Where upper (text) LIKE '% FOO_SOMETHING %'
AND owner = 'user _ name ';

40. insert data into the 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.

Copy codeThe Code is as follows:
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, csv
Connect by REGEXP_SUBSTR (csv.csv data, '[^,] +', 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.

Copy codeThe Code is as follows:
SELECT *
FROM employees
Where rowid in (select max (ROWID) FROM employees );

(OR)

SELECT * FROM employees
MINUS
SELECT *
FROM employees
Where rownum <(select count (*) FROM employees );

42. Perform row data multiplication 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

Copy codeThe Code is as follows:
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_val
Group 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

Copy codeThe Code is as follows:
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 DUAL
Connect 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.

Copy codeThe Code is as follows:
-- Generate random number between 0 and 100
Select 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.

Copy codeThe Code is as follows:
SELECT 1
FROM TABLE_NAME
Where rownum = 1;

If you know some useful query statements that can reduce the burden on Oracle developers, please share them in the comments :)