Oracle databases often use classic queries

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