A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
Date/Time Related queries
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;
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;
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;
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
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;
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;
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.
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 '. ');
SELECT (Sysdate-trunc (sysdate)) * (*) * num_of_sec_since_morning from DUAL;
SELECT (TRUNC (sysdate+1)-sysdate) * $ num_of_sec_left from DUAL;Data dictionary Query
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 ';
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 ';
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;
This is another query statement that can get the name of the current pattern.
SELECT sys_context (' Userenv ', ' Current_schema ') from DUAL;
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
Back to Oracle database version
SELECT * from V$version;
Returns some system-default information
SELECT username, profile , default_tablespace, temporary_tablespace from dba_users;
Display character setting information for a database
SELECT * from Nls_database_parameters;
SELECT VALUE from v$system_parameter WHERE name = ' compatible ';
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;
Another DDL query to resize the table space
ALTER DATABASE datafile '/work/oradata/startst/star02d.dbf ' resize 2000M;
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;
To add a data file to a table space
ALTER tablespace data01 ADD datafile '/work/oradata/startst/data01.dbf ' SIZE 1000M autoextend OFF;
To increase the size of the specified table space
ALTER DATABASE datafile '/u01/app/test_data_01.dbf ' RESIZE 2G;
gives the actual size of the database in gigabytes
SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_data_files;
Gives the amount of space the data occupies in the database
SELECT SUM (bytes)/1024/1024/1024 as GB from Dba_segments;
The size of the user's space in megabytes
Select SUM (bytes/1024/1024) "Size" from dba_segments WHERE owner = ' &owner ';
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
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;
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;
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.
SELECT CREATED, TIMESTAMP, last_ddl_time from all_objects WHERE OWNER = ' MySchema ' and object_type = ' TABLE ' and object_name = ' employee_table ';
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;
SELECT Osuser, username, machine, program from v$sessionorder by Osuser;
SELECT program Application, COUNT (program) Numero_sesiones from V$sessiongroup by Programorder by Numero_sesiones D ESC;
SELECT username usuario_oracle, COUNT (username) numero_sesiones from V$sessiongroup by Usernameorder by Numero_ Sesiones DESC;
SELECT owner, COUNT (owner) number_of_objects from Dba_objectsgroup to Ownerorder by Number_of_objects DESC;Practical/math-related queries
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
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 ';
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;
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);
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;
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;
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;
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)
Start building with 50+ products and up to 12 months usage for Elastic Compute Service