Oracle query statements

1. When the data in the database is accidentally deleted or deleted, you can use the following statement to query the data that has been deleted for a limited period of time:

Note: Use the Administrator to log on to the system:

Select * from table name as of timestamp sysdate-1/12 // query a table data two hours ago! Now that the data has been obtained two hours ago, let's continue to do it ..

What if the table is dropped ?? See the following:

Drop table name;

Restore the database after deleting the table by mistake:Absolutely OK, I have done such a thing, KhanBut remember which table names are deleted.
Flashback table name to before drop;

2. query to obtain the lock in the current database and unlock it:

Lock Query
SELECT/* + rule */s. username,
Decode (l. type, 'TT', 'table lock ',
'Tx ', 'row lock ',
NULL) LOCK_LEVEL,
O. owner, o. object_name, o. object_type,
S. sid, s. serial #, s. terminal, s. machine, s. program, s. osuser
FROM v $ session s, v $ lock l, dba_objects o
WHERE l. sid = s. sid
AND l. id1 = o. object_id (+)
AND s. username is not null;

Unlock
Alter system kill session 'sid, serial ';
If not. Directly reverse the kill process kill-9 spid In the OS

ORA-28000: account locked

The user is automatically locked multiple times because of incorrect password input.

Solution: alter user user_name account unlock;

3. statements related to permission query for database users:

1. view All users: select * from dba_user; select * from all_users; select * from user_users; 2. view user system permissions: select * from dba_sys_privs; select * from all_sys_privs; select * from user_sys_privs; 3. view user object permissions: select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; 4. view All roles: select * from dba_roles; 5. view User Roles: select * from dba_role_privs; select * from user_role_privs;

4. Several frequently used oracle views: note that the table name is in uppercase ....................

1. query all user information in oracle select * from dba_user; 2. only query the user and password select username, password from dba_users; 3. query the current user information select * from dba_ustats; 4. query the view text that can be accessed by the user. select * from dba_varrays; 5. query the text select * from dba_views; 6. query all indexes select * from user_indexes; query all tables select * from user_tables; query all constraints select * from user_constraints; query all objects select * from user_objects;

5. view the statements being executed in the current database, and then you can continue to do many things, such as querying the execution plan.

(1 ). view the sessions of related processes in the database Select. sid,. serial #,. program,. status, substr (. machine, 1, 20),. terminal, B. spid from v $ session a, v $ process B where. paddr = B. addr and B. spid = & spid; (2 ). view the locked objects in the database and related sessions select. sid,. serial #,. username,. program, c. owner, c. object_name from v $ session a, v $ locked_object B, all_objects c where. sid = B. session_id and c. object_id = B. object_id; (3 ). view the SQL select SQL _text from v $ sqlarea where address = (select SQL _address from v $ session where sid = & sid) being executed by the relevant session );

6. query the table structure: the table name is too large to write !!

Select t. COLUMN_NAME,
T. DATA_TYPE,
Nvl (t. DATA_PRECISION, t. DATA_LENGTH ),
Nvl (T. DATA_SCALE, 0 ),
C. comments
From all_tab_columns t, user_col_comments c
WhEre t. TABLE_NAME = c. table_name
And t. COLUMN_NAME = c. column_name
And t. TABLE_NAME = UPPER ('om _ EMPLOYEE_T ')
Order by t. COLUMN_ID

7. row-column swaps:

CREATE an example TABLE: create table t_col_row (id int, c1 VARCHAR2 (10), c2 VARCHAR2 (10), c3 VARCHAR2 (10); insert into t_col_row VALUES (1, 'v11', 'v21', 'v31'); insert into t_col_row VALUES (2, 'v12', 'v22', NULL); insert into t_col_row VALUES (3, 'v13', NULL, 'v33'); insert into t_col_row VALUES (4, NULL, 'v24', 'v34'); insert into t_col_row VALUES (5, 'v15 ', NULL, NULL); insert into t_col_row VALUES (6, NULL, NULL, 'v35'); insert into t_col_row VALUES (7, NULL); COMMIT; the following is column-to-row: CREATE view v_row_col as select id, 'c1 'cn, c1 cv FROM t_col_row union all select id, 'c2 'cn, c2 cv FROM t_col_row union all select id, 'c3' cn, c3 cv FROM t_col_row; below is a vertical table without null values: CREATE view v_row_col_notnull as select id, 'c1 'cn, c1 cv FROM t_col_row where c1 is not null union all select id, 'c2 'cn, c2 cv FROM t_col_row where c2 is not null union all select id, 'c3' cn, c3 cv FROM t_col_row where c3 is not null;

8. The following may be the oracle view frequently used by DBAs.

1. example: If hash_value: 3111103299 is known, query the SQL statement: select * from v $ sqltext where hashvalue = '000000' order by piece 2. view the SQL statements that consume the most resources: SELECT hash_value, executions, buffer_gets, disk_reads, parse_cils FROM V $ SQLAREA WHERE tables> 000or disk_reads> 1000000 ORDERBY buffer_gets + 100 * disk_reads DESC. view the resource consumption of an SQL statement: SELECT hash_value, buffer_gets, disk_reads, executions, parse_cils FROM V $ SQLARE A where hash_Value = 228801498AND address = hextoraw ('cbd8e4b0'); 4. query the dynamic execution plan of an SQL statement: first, use the following statement to find the address and hash_code SELECT SQL _text, address, hash_value FROM v $ SQL t where (SQL _text like '% FUNCTION_T (table name overwrite !) % ') Then: SELECT operation, options, object_name, cost FROM v $ SQL _plan WHERE address = 'c00000016bd6d248' AND hash_value = 664376056; 5. query the oracle version: select * from v $ version; 6. query database parameters: select * from v $ parameter 7. find your session Information select sid, OSUSER, USERNAME, MACHINE, process from v $ session where audsid = userenv ('sessionid'); 8. when the machine is known, find the session select sid, OSUSER, USERNAME, MACHINE, TERMINAL From v $ session where terminal = 'pts/tl 'AND machine = 'rgmdbs1'; 9. Find the SQL statement currently running by a specified session. Assume that sessionID is 100 select B. SQL _text from v $ session a, v $ sqlarea B where a. SQL _hashvalue = B. hash_value and a. sid = 100

9. Tree Structure connect by sorting:

Query the tree-like Data Structure and sort the data in a layer. SELECT last_name, employee_id, manager_id, level from employees start with employee_id = 100 connect by prior employee_id = manager_id <SPAN style = "BACKGROUND-COLOR: # ff0000"> order siblings by last_name; </SPAN> The following is the query result LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL --------------------------- ------------ ---------- King 100 1 Cambrault 148 100 2 Bates 172 148 3 Bloom 169 3 Fox 148 170 3 Kumar 148 3 Ozer. 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103 102 3 Austin 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106 103 4 Errazuriz 147 100 2 Ande 166 147 3 banda 167 147 3

10. Sometimes I write more things and forget the most basic SQL syntax. The basic oracle statements can be found here. Is a basic statement!

1. query constraints in the data dictionary: SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name = 'ployees'; // The table names here are in upper case! 2. Description of the table structure: desc Tablename 3. view the tables in the user's table select table_name from user_tables; 4. view the constraints on the column: SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'ployees'; 10 use variables to find the column names associated with the constraints in a table: select constraint_name, column_name from user_cons_columns where table_name = '& tablename' 12 the elements in the middle of the data dictionary query: SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'emp' %' OR object_na Me LIKE 'dept' % '14 query object type: select distinct object_type FROM user_objects; 17 Change Object Name: Table Name, view, sequence) rename emp to emp_newTable 18 Add Table comment: comment on table employees IS 'employee information'; 20 view structure: describe view_name 23 view Information in the data dictionary: select viewe_name, text from user_views 25 view the sequence in the data dictionary: select * from user_sequences 33 get all the time zone names: select * from v $ timezone_names 34 shows the time zone offset for the time zone 'us/Eastern 'select TZ_OFFSET (' US/Eastern ') from DUAL -- dual indicates 'dual' to display the current date and time in the current SESSION time zone: alter session set NLS_DATE_FORMAT = 'dd-MON-YYYY HH24: MI: ss'; -- alter session set TIME_ZONE = '-5:0'; -- modify the time zone select sessiontimezone, CURRENT_DATE from dual; -- a really useful statement! SELECT CURRENT_TIMESTAMP from dual; -- the returned time is the current date and time, containing the SELECT CURRENT_TIMESTAMP from dual; -- the returned time is the current date and time, excluding the time zone !!! 35 display the values of the database time zone and session time zone: select datimezone, sessiontimezone from dual; 13 common TABLE creation statements: create table dept (deptno NUMBER (2), dname VARCHAR2 (14 ), loc VARCHAR2 (13); 15 CREATE a TABLE using a subquery: create table dept80 as select employee_id, last_name, salary * 12 ANNSAL, hire_date FROM employees WHERE department_id = 80; 6 Add a column: // alter table EMP add column (dept_id number (7); error !! Alter table EMP add (dept_id number (7); 7 delete a column: alter table emp drop column dept_id; 8 add both column names and constraints: alter table EMP add (dept_id number (7) constraint my_emp_dept_id_fk references dept (ID); 9 Change columns: // note that constraints cannot be modified !! Alter table dept80 modify (last_name varchar2 (30); // here we use modify instead of alter! 24 add a row: insert into table_name values (); 5 add a primary key: alter Table EMP add constraint my_emp_id_pk primary key (ID); 11 add a new column with check constraints: alter table EMP add (COMMISSION number (2) constraint emp_commission_ck check (commission> 0) 16 delete a table: drop table emp; 19 CREATE a VIEW: create view empvu80 as select employee_id, last_name, salary FROM employees WHERE department_id = 80; 21 Delete view: drop view view_name 22 find 5 people with the highest salary. Top-n analysis) in-row view) select rownum, employee_id from (select employee_id, salary from employees order by salary desc) where rownum <5; 26 create Synonyms: create synonym name for original name or create public synonym name for original name 27 create a sequence: note that the sequence in the table is not shown here !!) Create sequence dept_deptid_seq increment by 10 start with 120 MAXVALUE 9999 nocache nocycle 28 use SEQUENCE: insert into dept (ID, NAME) values (DEPT_ID_SEQ.nextval, 'admin'); 29 CREATE index: // The nonunique INDEX is used by default, unless the Keyword: unique create index emp_last_name_idx ON employees (last_name); 30 user Creation: error may occur. For details, see help.) create user username (user name) identified by oracle (password) default tablespace data01 (tablespace name // exists in the system tablespace by default) quota 10 M (Set the size. The maximum value is unlimited.) on tablespace name // The quota must be allocated! 31 create a ROLE: create ROLE manager grant the ROLE permissions: grant create table, create view to manage grant the User ROLE: grant manager to DENHAAN, KOCHHAR (two users) 32 assign permissions: GRANT update (department_name, location_id) ON instances TO scott, manager; REVOKE permissions REVOKE select, insert ON orders ments FROM scott; 36 extract year, month, and day FROM time: use the extract select extract (year from sysdate) year, extract (month from sysdate), extract (day from sysdate) from dual; 37 use the Function The date after several months: to_yminterval '01-02 ') indicates that the day of January 1, February cannot be reached !! Select hire_date, hire_date + to_yminterval ('01-02 ') as hire_date_new from employees where department_id = 20 how many days after the date: Add a number to the direct date! Select hire_date + 3 from employees where department_id = 20 38 General time functions: MONTHS_BETWEEN ('01-SEP-95 ', '11-JAN-94') -- number of months between two dates, returns a floating point number ADD_MONTHS ('11-JAN-94 ', 6) -- adds the number of months NEXT_DAY ('01-SEP-95', 'Friday ') -- LAST_DAY ('01-FEB-95 ') Next Friday -- last day of the month! ROUND (SYSDATE, 'month') -- ROUND to month round (SYSDATE, 'Year') -- ROUND to year trunc (SYSDATE, 'month') -- phase month trunc (SYSDATE, 'Year') -- truncation YEAR 39 group statement: and advanced application statement: SELECT department_id, job_id, SUM (salary), COUNT (employee_id) FROM employees group by department_id, job_id; use having for constraints: 1. group by rollup: In n + cases, SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id <60 group by rollup (depa Rtment_id, job_id); 2. group by cube: SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id <60 group by cube (department_id, job_id); 3. when grouping is used to obtain the columns in a row, only 1 and 0 are returned. If it is null, 1 is returned. Otherwise, 0 is returned. (do not reverse it !) SELECT distinct DEPTID, job_id JOB, SUM (salary), GROUPING (department_id) GRP_DEPT, GROUPING (job_id) GRP_JOB FROM employees where grouping <50 group by rollup (partition, job_id); 4. grouping sets: SELECT department_id, job_id, manager_id, avg (salary) FROM employees group by grouping sets (department_id, job_id), (job_id, manager_id); Use subquery in 40from: Return SEL of the average salary and employee information of each department larger than that of the Department. ECT a. last_name, a. salary, a. department_id, B. salavg FROM employees a, -- the following is a subquery, mainly returning a set of data! (SELECT department_id, AVG (salary) salavg FROM employees group by department_id) B WHERE. department_id = B. department_id AND. salary> B. salavg; use of 41exists statement: SELECT employee_id, last_name, job_id, department_id FROM employees outer -- select in the following exists can SELECT any character or number where exists (select 'x' FROM employees WHERE manager_id = outer. employee_id); 42 The with statement: WITH dept_costs AS (-- defines a temporary SELECT d. department_name, SUM (e. salary) AS dept_total -- defines a temporary column dept_total FROM employees e, orders ments d WHERE e. department_id = d. department_id group by d. department_name),/* Note the comma */avg_cost AS (select sum (dept_total)/COUNT (*) AS dept_avg FROM dept_costs) -- the second temporary table references the column between the temporary table and the previously defined temporary table! SELECT * FROM dept_costs WHERE dept_total> (SELECT dept_avg FROM avg_cost) order by department_name; --- the preceding temporary table 43 traversal tree is used in the final query statement: SELECT employee_id, last_name, job_id, manager_id FROM employees start with employee_id = 101 connect by prior manager_id = employee_id; -- bottom-up traversal tree. 44. UPDATE employees SET job_id = 'sa _ MAN ', salary = salary + 1000, department_id = 120 WHERE first_name | ''| last_name = 'Douglas Grant '; update table (SELECT projs FROM dept d WHERE d. dno = 123) p SET p. budgets = p. budgets + 1 WHERE p. pno IN (0, 123,456 );

11. Import and export the dmp file:

Imp username/password @ database ignore = y file = backup file log = D: \ DBtest \ db_bak \ imp. log

Exp system/manager @ TEST file = d: \ daochu. dmp full = y

12. Large Object field blob: view the blob field size:

SelectDbms_lob.getLength(Field name)FromTable Name;

13. The following are interesting SQL statements, which may be exactly what you need:

-- Create a view that can only be accessed during working hours create or replace view newviewemp as select * from table name where exists (select 1 from dual where sysdate> = to_date (to_char (sysdate, 'yyyy-mm-dd') | '08: 00: 00', 'yyyy-mm-dd hh24: mi: ss') and sysdate <to_date (to_char (sysdate, 'yyyy-mm-dd') | '18: 00: 00', 'yyyy-mm-dd hh24: mi: ss '))

14. Execute ddl statements in the stored procedure:

Create Or Replace Procedure My_Proc
Sqlddl Varchar2 (1000 );
Begin
Sqlddl: = 'create table MyTable (ID Number (5), Name Varchar2 (20 ))';
Dbms_Output.Put_Line (Sqlddl );
Execute Immediate Sqlddl;
End;

This article is from the "IT elite Concentration Zone" blog. For more information, contact the author!