This article collects frequently used database queries. Database administrator privileges are required:
1. Querying for temporary table space usage
SELECT tablespace_name, tablespace_size/1024/1024 tablespace_size_mb, allocated_space/1024/1024 ALLOCATED _SPACE_MB, free_space/1024/1024 free_space_mb, to_char ((1-free_space/tablespace_size) *, ' 900.00 ') PER Centage_usedfrom Dba_temp_free_space;
2. Query the SID of the statement using the temp tablespace, serial#:
SELECT b.tablespace, b.segfile#, b.segblk#, ROUND ((( (b.blocks * p.value)/1024/1024/1024), 2) Size_ GB , A.sid, a.serial#, A.username, A.osuser, A.program, a.status from v$session A, V$sort_usage B, v$process C, V$ PARAMETER P WHERE p.name = ' db_block_size ' and a.saddr = b.session_addr and a.paddr = C.addrorder by SIZE_GB D ESC;
3. According to the above query serial#, query out the corresponding statement:
SELECT S.sid, s.serial#, t.sql_fulltext,t.sql_id,s.sql_hash_value,t.hash_value from V$session S, V$SQL T WHERE S.SQL_ ADDRESS = t.address and S.sql_hash_value = T.hash_valueand s.serial# = ' &serial# ';
4. Query the usage of tablespace:
SELECT TSU. Tablespace_name, Ceil (TSU. USED_GB) "Used GB"--15467,decode (Ceil (TSF. FREE_GB), Null,0,ceil (TSF. FREE_GB)) "Free GB", DECODE (100-ceil (TSF). Free_gb/tsu. used_gb*100), NULL, 100-ceil (TSF. Free_gb/tsu. used_gb*100)) "% used" from (SELECT tablespace_name, SUM (BYTES)/1024/1024/1024 used_gb from dba_data_files GROUP by Tablespace_name UNION all SELECT Tablespace_name | | ' **temp** ', SUM (BYTES)/1024/1024/1024 used_gb from dba_temp_files GROUP by Tablespace_name) TSU , ( SELECT Tablespace_name, SUM (BYTES)/1024/1024/1024 free_gb from dba_free_space GROUP by Tablespace_name ) Tsfwheretsu. Tablespace_name = TSF. Tablespace_name (+) and (TSF. Tablespace_name in (' &tablespace_name1 ', ' &tablespace_name1 '));
5. The session with long query execution time:
Select *from (SELECT * from (select VP). Start_time "Start Time", ROUND (VP. LAST_UPDATE_TIME-VP. start_time) *60*24*60) ELAPSED,VP. Message "message", DECODE (VP. TotalWork, 0, 0, ROUND (VP). SOFAR/VP. TotalWork, 2)) "Percent", VP. Time_remaining| | ' sec ' REMAINING from v$session_longops VP WHERE VP. SID = &session_id--The actual number of references to be replaced ) ORDER by 1 DESC
6. Querying the size of a database table
7. Querying database tables for dependencies:
SELECT * from dba_dependencies WHERE referenced_name= ' &object_name ';--Input object name
8. Output statements that create tables
Declarev_str LONG; CURSOR CUR isselect column_name| | ' ' | | DECODE (data_type, ' number ', DECODE (Nullif (Data_length, $), NULL, data_type, data_type| | ') (' | | data_length| | ') ', ' DATE ', data_type, data_type| | ' (' | | data_length| | ') ' ) | | ', ' colfrom dba_tab_cols WHERE table_name= ' &source_table_name ' and owner= ' & Source_table_owner ' ORDER by column_name ASC; BEGIN for REC in CUR LOOP v_str:=v_str| | REC. col| | CHR (ten); END LOOP; v_str:= ' CREATE TABLE ' | | ' &table_name ' | | ' ' | | ' (' | | CHR (10) | | v_str| | CHR (+) | | '); Dbms_output. Put_Line (V_STR); END;
9. Query table Structure
SELECT Dbms_metadata. GET_DDL (' table ', ' &table_name ', ' &schema ') from dual;--replace the table name with the schema of the table
10. Querying the character set of a database
SELECT * from Nls_database_parameters;
11. Query Database Link:
SELECT * from Dba_db_links WHERE db_link= ' &db_link ';
12. Querying the index on a data table
SELECT * from dba_indexes where owner= ' &owner ' and table_name= ' &table_name ';
13. Index columns used to query the data table
SELECT * from Dba_ind_columns where index_owner= ' &owner ' and table_name= ' &table_name ';
14. Querying the data table for stored procedures/functions/self-defined types/triggers/packages
SELECT * from Dba_source where onwer= ' &owner ' and type= ' &type ';
15. Query constraints
SELECT * FROM Dba_constraints;select * from All_constraints;select * from User_constraints;
16. Querying synonyms
SELECT * FROM Dba_synonyms;select * from All_synonyms;select * from user_synonyms;
17. Check the departure device
SELECT * FROM Dba_triggers;select * from All_triggers;select * from User_triggers;
18. Query view
SELECT * FROM Dba_views;select * from All_views;select * from User_views;
19. Query sequence
SELECT * FROM Dba_sequences;select * from All_sequences;select * from User_sequences;
20. Querying materialized views
SELECT * FROM Dba_mviews;select * from All_mviews;select * from User_mviews;
21. Querying database Objects
SELECT * FROM Dba_objects;select * from All_objects;select * from User_objects;
22. Querying Stored Procedures
SELECT * FROM Dba_procedures;select * from All_procedures;select * from User_procedures;
23. Querying Database Tables
SELECT * FROM Dba_tables;select * from All_tables;select * from User_tables;
24. Querying the columns of a database table
SELECT * FROM Dba_tab_columns;select * from All_tab_columns;select * to User_tab_columns;select * from Dba_tab_cols;sele CT * from All_tab_cols;select * from User_tab_cols;
25. Querying database table columns for comments
SELECT * FROM Dba_tab_comments;select * from All_tab_comments;select * from User_tab_comments;
26. View the run schedule for the query
Explain plan for SELECT * FROM Dict;select * from table (dbms_xplan.display);
Continuous update ...
--------------------------------------------------------------------------------------------------------------- -------------------------------------
Let's say that you're having trouble trying, or that my code is in the wrong place. Please correct me, thank you very much!
Contact information: [Email protected]
Copyright @: Reprint please indicate the source!
Oracle databases often use classic queries