Typical Oracle database queries

Typical 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!