Common Oracle Database classic queries and oracle database queries

Source: Internet
Author: User
Tags how to use sql

Common Oracle Database classic queries and oracle database queries

This document collects common database queries and requires the database administrator privilege:

1. query temporary tablespace 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) * 100, '900.00') PERCENTAGE_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 DESC;

3. Based on the SERIAL # found above, query 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 table space usage:

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,   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. query sessions that have been running for a long time:

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 (100 * VP. SOFAR/VP. TOTALWORK, 2) "Percent", VP. TIME_REMAINING | 'sec 'remaining from v $ SESSION_LONGOPS vp where vp. SID = & session_id -- the actual parameter to be replaced) order by 1 DESC );

6. query the database table size

SELECT SEGMENT_NAME, SUM (BYTES)/1024/1024/1024 GBFROM DBA_SEGMENTSWHERE SEGMENT_NAME = '& table_name' <span style = "font-family: Arial, Helvetica, sans-serif; "> -- the actual parameter to be replaced </span> group by SEGMENT_NAME;

7. query database table dependencies:

SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = '& object_name'; -- enter the Object Name

8. output the statement for creating a table

DECLAREV_STR LONG;CURSOR CUR ISSELECT COLUMN_NAME||' '||DECODE(DATA_TYPE,  'NUMBER', DECODE(NULLIF(DATA_LENGTH, 22), 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(10);       END LOOP;       V_STR:='CREATE TABLE'||' &TABLE_NAME'||' '||'('||CHR(10)||V_STR||CHR(10)||');';       DBMS_OUTPUT.PUT_LINE(V_STR);END;

9. query the table structure

SELECT DBMS_METADATA.GET_DDL ('table', '& table_name', '& scheme') from dual; -- Replace the TABLE name with the schema of the TABLE.

10. query the character set of the database

SELECT * FROM NLS_DATABASE_PARAMETERS;

11. query the database link used by the database:

SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='&db_link';

12. query the indexes in a data table

select * from dba_indexes where owner='&owner' and table_name='&table_name';

13. query the index columns used by the data table

select * from dba_ind_columns where index_owner='&owner' and table_name='&table_name';

14. query stored procedures, functions, custom types, triggers, and packages in a data table.

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. query Synonyms

select * from dba_synonyms;select * from all_synonyms;select * from user_synonyms;

17. query the departure Server

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. query the Materialized View
select * from DBA_MVIEWS;select * from ALL_MVIEWS;select * from USER_MVIEWS;

21. query database objects

select * from DBA_OBJECTS;select * from ALL_OBJECTS;select * from USER_OBJECTS;


22. query stored procedures

select * from DBA_PROCEDURES;select * from ALL_PROCEDURES;select * from USER_PROCEDURES;

23. query database tables

select * from DBA_TABLES;select * from ALL_TABLES;select * from USER_TABLES;

24. query columns in a database table

select * from DBA_TAB_COLUMNS;select * from ALL_TAB_COLUMNS;select * from USER_TAB_COLUMNS;select * from DBA_TAB_COLS;select * from ALL_TAB_COLS;select * from USER_TAB_COLS;

25. query the remarks of database table columns

select * from DBA_TAB_COMMENTS;select * from ALL_TAB_COMMENTS;select * from USER_TAB_COMMENTS;

26. view the query execution plan

explain plan for select * from dict;select * from table(dbms_xplan.display);


Updating...

Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!
How to query all tables in the current database using oracle

SELECT * FROM ALL_TABLES; table with permissions IN THE SYSTEM
SELECT * FROM DBA_TABLES; system table
SELECT * FROM USER_TABLES; the table under the current user

How to use SQL statements to query tables in an oracle database

-- Query tables under your current user
SELECT * FROM user_tables

-- Query the tables that can be accessed by your current user [that is, tables that can be accessed by yourself and other users]
SELECT * FROM all_tables

-- To query all the tables in the current database, you must have DBA permissions.
SELECT * FROM dba_tables

Related Article

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.