Oracle databases often use classic queries

Source: Internet
Author: User
Tags chr

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.