Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the common sql in Oracle DBA". In the daily operation, I believe many people have doubts about the common sql in Oracle DBA. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is the common sql in Oracle DBA?" Next, please follow the editor to study!

1. SQL with slow execution of Oracle query every day

2. Oracle query the dependency relationship between locks

3. Oracle finds the source SID of the dependency relationship between locks.

4. Oracle queries the usage of each tablespace-improvement

5. Oracle regularly checks indexes that are of little significance.

6. Oracle checks index usage on a monthly basis (email feedback)

7. Oracle is a partition table, but the condition is SQL without partition condition.

8. There are two problems hidden in the inconsistent order of Oracle table structure.

9. Oracle view table properties: "Table name (comment) / column name (comment) / whether the field is NULL"

10. Oracle finds the names of PROCEDURE/FUNCTION in a packet 'PACKAGE BODY''.

Little SQL

Connect ~

/ * View Oracle error number message * / [oracle@lottery ~] $oerr ora 600max * clear screen ~ * / SQL > clear screen/* Registration oracle snooping * / SQL > alter system register;/* View the number of DB in OS * / [oracle@lottery ~] $ps-ef | grep oracle$ORACLE_SID | grep LOCAL=NO | wc-lamp * query the number of connections in the current process in the database * / select count (*) from vprocessors from v$session * check the number of connections in the current session in the database * / select count (*) from v$session / * View the total number of database user connection sessions * / select username,count (username) from gv$session where username is not null group by username;/* query database maximum number of connections / processes * / select name,value from v$parameter where name in ('processes','sessions'); = > show parameter processes/sessions optimization ~

/ * find the execution plan through SQL_ID * / select * from table (dbms_xplan.display_cursor ('br8d2xs44sga8')); / * find the SQL text through SQL_ID * / select * from gv$sqlarea s where s.sqlroomid=' wait event for br8d2xs44sga8';/* to view the database * / SELECT * FROM gv$session_wait where sid in (SELECT sid FROM gV$SESSION WHERE STATUS='ACTIVE' and username is not null and sidestep userenv ('sid')) / * check whether the statistical information of the table is correct * / SELECT TABLE_NAME,NUM_ROWS,LAST_ANALYZED FROM USER_TABLES T WHERE TABLE_NAME=' Table';-- # used to view the gap between the last statistics and the actual implementation of the table / * View all field information of the table * / select * from user_tab_columns where table_name= 'Table'; / * count the whole user * / begin DBMS_STATS.gather_schema_stats ('user', cascade= > TRUE,no_invalidate= > false); end;/* Statistics Table * / begin DBMS_STATS.GATHER_TABLE_STATS ('user', 'Table', cascade= > TRUE); end / * View the last DML time of the table * / select max (ora_rowscn), scn_to_timestamp (max (ora_rowscn)) from table; basic information ~

/ * check the remaining tablespaces * / select TABLESPACE_NAME,sum (round (bytes/1024/1024/1024,2)) from dba_free_space a group by tablespace_name;/* query memory allocation * / select component,current_size/1024/1024 MB, user_specified_size/1024 MB from v$memory_dynamic_components where currentsized sizespaces * alternate FROM DBA_SEGMENTS GROUP BY OWNER * View user size * / SELECT OWNER,SUM (memory) FROM DBA_SEGMENTS GROUP BY OWNER / * View the database default tablespace * / select * from database_properties 's where s.description like'% default%tablespace'/* to view the temporary tables in the database * / select * from user_tables u where u.temporarytemporarys temporary tables * * View the 11g alert file location * / select value from vault diagrams in the database;-- > the process number of show parameter diagnostic_dest/* 's current reply * / select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1)); permissions ~

/ * View the permissions of the resource role * / select * from role_sys_privs where role='RESOURCE'; / * View the users in the database who have granted dba permissions * / SELECT * FROM DBA_ROLE_PRIVS S WHERE S.GRANTEDROLER = 'DBA';dblink~

/ * create DBLINK statement * / create public database link dblink name connect to user identified by password using'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = IP address) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = instance name))'; other ~

/ * find the time corresponding to the snapshot SNAP_ID * / select * from sys.wrh$_active_session_history;/* to view the index owner! = the owner of the table * / SELECT owner,index_name,index_type,table_owner,table_name,table_type FROM dba_indexes where owner ownership tableowner owner * View the table of the (read-only) attribute in the library * / select table_name,status,read_only from dba_tables where read_only='YES' # change table properties alter table table read only (read write); (11g new features) # Note: index creation / modification has no effect on read-only tables [tablespaces]! Because the index modifies the data dictionary, it has nothing to do with the table / * View the basic information of the partition table query * / SELECT TABLE_NAME,column_name,PARTITION_NAME,HIGH_VALUE LESS_ Than value, TABLESPACE_NAME FROM USER_TAB_PARTITIONS tp join USER_PART_KEY_COLUMNS tpc on tp.table_name=tpc.name;/* to view all sessions logged in by a user * / SELECT 'ALTER SYSTEM KILL SESSION''| | SID | |','| SERIAL# | |' ', S.* FROM V$SESSION S WHERE USERNAME=' user 'AND statuses cannot delete the currently connected user / * View the type corresponding to the command_type value * / SELECT * FROM vSecretsqlCommand;-- [v$sqlarea.command_type, v$session.command] / * View the value passed by a sql_id binding variable * / SELECT * FROM V$SQL_BIND_CAPTURE 's where s.sql_id in (' fdc8mt5xnjx2a') and CHILD_ADDRESS=2 / * find the sequence last_number*/SELECT * FROM USER_SEQUENCES S WHERE S.SEQUENCENAMEN SEQUENCES -used when the database with two servers is the same version, same listening, same instance / * oracle view standby library delay time * / SELECT ((substr (value,2,2) * 24 + substr (value,5,2)) * 60+substr (value,8,2)) * 60+substr (value,-2) TIME FROM gv$dataguard_stats where name = 'apply lag'

/ * check that the session state is set to "killed" * / select a.spidrecoveryb.sidrecoveryb.serialauthorityb.username from v$process arewrect session b where a.addr=b.paddr and b.status= 'KILLED'

-- > the session in which the system layer releases the DB kill state; (linux:kill-9 spid; Windows: orakill orcl pid)

SELECT distinct 'ALTER SYSTEM KILL SESSION''| | SID | |','| | s.SERIAL# | |';'

/ * ORA-00054: resource is busy, but specify O.OWNER, O.OBJECT_NAME, S.STATUS, LOCKWAIT FROM V$SESSION S JOIN V$LOCKED_OBJECT LO

Obtain resources by NOWAIT, or expire with timeout * / ON LO.SESSION_ID = S.SID JOIN DBA_OBJECTS O ON O.OBJECT_ID = LO.OBJECT_ID and S.STATUSrequests active 'AND OBJECT_NAME in (' table name')

Big SQL

1. Check the SQL that is being executed in the database:

SELECT SE.INST_ID,-- instance

SQ.SQL_TEXT, / * SQL text * /

SQ.SQL_FULLTEXT, / * SQL full text * /

SE.SID, / * unique identification of a session. Usually, before analyzing a session, you need to obtain the SID of the session. , /

-- SE.SERIAL#, / * session serial number * /

SQ.OPTIMIZER_COST AS COST_, / * COST value * /

SE.LAST_CALL_ET CONTINUE_TIME, / * execution time is in seconds (time may be a single sql or the entire function) * /

CEIL ((SYSDATE-SE.PREV_EXEC_START) * 24: 60: 60) seconds difference, / * this part is used to determine the execution time of a single sql when the execution time is the whole function * /

SE.PREV_EXEC_START, / * SQL execution start of the last executed SQL statement*/

SE.EVENT, / * wait for events * /

SE.LOCKWAIT, / * whether to wait for LOCK (SE,P) * /

SE.MACHINE, / * the machine name of the client. (WORKGROUP\ PC-201211082055) * /

SQ.SQL_ID, / * SQL_ID*/

SE.USERNAME, / * create the user name of the session * /

SE.LOGON_TIME / * Landing time * /

-- SE.TERMINAL, / * the name of the terminal on which the client runs. (PC-201211082055) * /

-, SQ.HASH_VALUE, / * the HASH value generated by a SQL * /

-- SQ.PLAN_HASH_VALUE / * executes the hash value of SQL (parsed hash value). After associating with SQL_ADDRESS and querying other SQL-related views, you can query the SQL statement currently executing in the session * /

FROM GV$SESSION SE, / * session information. Each session connected to the ORACLE database can correspond to a record in this view. According to the information in this view, you can query the user used by the session, the SQL statement being executed or just executed * /

/ * [GV$SQLAREA multiple nodes] * /

GV$SQLAREA SQ / * tracks shared CURSOR information in all SHARED POOL, including execution times, logical reads, physical reads, etc. * /

WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE

AND SE.STATUS = 'ACTIVE'

AND SE.SQL_ID = SQ.SQL_ID

AND SQ.INST_ID = SE.INST_ID

AND SE.USERNAME is not null

-- filter condition

-- AND SE.USERNAME = 'FWSB'-- user name

-- AND SQ.COMMAND_TYPE IN (2,3,5,6,189)

-- AND SE.SID! = USERENV ('SID') / * misuse of rac cluster environment * /

-- AND MACHINE! = 'WORKGROUP\ MHQ-PC'

Second, slow SQL execution every day:

SELECT S.SQL_TEXT

S.SQL_FULLTEXT

S.SQL_ID

ROUND (ELAPSED_TIME / 1000000 / (CASE)

WHEN (EXECUTIONS = 0 OR NVL (EXECUTIONS, 1) = 1) THEN

one

ELSE

EXECUTIONS

END)

2) "execution time'S'"

S.EXECUTIONS "number of execution"

S.OPTIMIZER_COST "COST"

S.SORTS

S.MODULE,-- connection mode (JDBC THIN CLIENT: program)

-- S.LOCKED_TOTAL

S.PHYSICAL_READ_BYTES "physical Reading"

-- S.PHYSICAL_READ_REQUESTS "physical read request"

S.PHYSICAL_WRITE_REQUESTS "physical Writing"

-- S.PHYSICAL_WRITE_BYTES physical write request

S.ROWS_PROCESSED "return rows"

S.DISK_READS "disk read"

S.DIRECT_WRITES "direct path writing"

S.PARSING_SCHEMA_NAME

S.LAST_ACTIVE_TIME

FROM GV$SQLAREA S

WHERE ROUND (ELAPSED_TIME / 1000000 / (CASE)

WHEN (EXECUTIONS = 0 OR NVL (EXECUTIONS, 1) = 1) THEN

one

ELSE

EXECUTIONS

END)

2) > 5-100 0000 microseconds = 1s

-- AND S.PARSING_SCHEMA_NAME = USER

AND TO_CHAR (S.LAST_LOAD_TIME, 'YYYY-MM-DD') =

TO_CHAR (SYSDATE, 'YYYY-MM-DD')

AND S.COMMAND_TYPE IN (2,3,5,6,189) / * values correspond to types 2:INSERT, 3:SELECT, 6:UPDATE, 7:DELETE, 189:MERGE query V$SQLCOMMAND*/

AND MODULE = 'JDBC Thin Client'

ORDER BY "execution time'S'" DESC

3. Check the SQL of unbound variables:

SELECT V.SQL_ID

V.SQL_FULLTEXT

V.PARSING_SCHEMA_NAME

FM.EXECUTIONS_COUNT

FM.ELAPSED_TIME

FROM (SELECT L.FORCE_MATCHING_SIGNATURE MATHCES

MAX (L.SQL_ID | | L.CHILD_NUMBER) MAX_SQL_CHILD

DENSE_RANK () OVER (ORDER BY COUNT (*) DESC) RANKING

ROUND (SUM (ROUND / 1000000 / (CASE)

WHEN (EXECUTIONS = 0 OR NVL (EXECUTIONS, 1) = 1) THEN

one

ELSE

EXECUTIONS

END)

5)) ELAPSED_TIME

SUM (L.EXECUTIONS) EXECUTIONS_COUNT

FROM V$SQL L

WHERE TO_CHAR (TO_DATE (LAST_LOAD_TIME, 'YYYY-MM-DD HH24:MI:SS')

'YYYY-MM-DD') = TO_CHAR (SYSDATE-1,' YYYY-MM-DD')-- LAST_LOAD_TIME of the day (VARCHAR type, LOADED INTO THE LIBRARY CACHE TIME)

AND L.MODULE LIKE'% JDBC%'-Program connection

AND L.FORCE_MATCHING_SIGNATURE 0

AND L.PARSING_SCHEMA_NAME = UPPER ('& USERNAME')-user

AND L.COMMAND_TYPE IN (2,3,5,6,189)-- Command types 2:INSERT, 3:SELECT, 6:UPDATE, 7:DELETE, 189:MERGE query V$SQLCOMMAND

GROUP BY L.FORCE_MATCHING_SIGNATURE

HAVING COUNT (*) > 5) FM

V$SQL V

WHERE FM.MAX_SQL_CHILD = (V.SQL_ID | | V.CHILD_NUMBER)

AND EXECUTIONS_COUNT > = 50-after more than 50 times of execution, filter and rewrite first, then slowly reduce the range.

ORDER BY FM.RANKING

-- V$SQL_BIND_CAPTURE-- the record contains variables to get the table.. Including ROWNUMsysdate-8

Group by trunc (first_time), to_char (first_time, 'Dy')

Order by 1

5. Check the progress of SQL execution:-- display the status of database operations that have been running for more than 6 seconds

SELECT A.SID

A.SERIAL#

OPNAME

TARGET,-- object

TO_CHAR (START_TIME, 'YYYY-MM-DD HH24:MI:SS') START_TIME,-start time

(SOFAR / TOTALWORK) * 100PROGRESS,-schedule ratio

TIME_REMAINING-- estimate the remaining time

ELAPSED_SECONDS,-- run time'S'

A.SQL_ID

FROM V$SESSION_LONGOPS A

WHERE SID =

* where SID and SERIAL# match those in V$SESSION

* OPNAME: refers to the name of the operation that has been executed for a long time. Such as: TABLE SCAN

* TARGET: the OBJECT_NAME being operated. Such as: TABLEA

* TARGET_DESC: describes the content of TARGET

* SOFAR: this requires attention, indicating the number of work to be done, such as how many blocks have been scanned.

* TOTALWORK: the total number of target objects (expected). Such as the number of blocks.

* START_TIME: the start time of the process

* LAST_UPDATE_TIM: time of the last call to SET_SESSION_LONGOPS

* TIME_REMAINING: estimate how much time it will take to complete (in seconds)

* ELAPSED_SECONDS: from the start time to the last update time

* MESSAGE: a complete description of the operation, including progress and operation content.

* USERNAME: same as in V$SESSION.

* SQL_ADDRESS: associated V$SQL

* SQL_HASH_VALUE: associated V$SQL

* QCSID: it is mainly used together for parallel queries.

The query foreign key field has no index in the primary key table

SELECT C.*

C1.r_constraint_name

C2.table_name

T.NUM_ROWS

'create index idx_' | | c.table_name | |' _'| | column_name | |'on'| |

C.table_name |'('| | column_name | |');'

FROM USER_CONS_COLUMNS C

JOIN USER_CONSTRAINTS C1

ON C1.CONSTRAINT_NAME = C.CONSTRAINT_NAME

AND C1.CONSTRAINT_TYPE ='R'

AND (C.TABLE_NAME, C.COLUMN_NAME) NOT IN

(SELECT TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS I)

JOIN USER_TABLES T

ON T.TABLE_NAME = C.TABLE_NAME

Join USER_CONSTRAINTS c2

On c1.r_constraint_name = c2.constraint_name

Blog: why do foreign key columns of child tables need to be indexed? Http://blog.itpub.net/17203031/viewspace-701832/

* * Test by yourself [when the foreign key field is not indexed]

* * update foreign key table, primary key table delete does not allow any data; but the scope of update session1 and the set field is not where field can be executed. After indexing, changing the data of the where field will report an error.

7. View soft and hard parsing and cursor count

SELECT / * A.SID recording materials / / * A.STATISTICS writing codes /

SUM (A.VALUE)

B.NAME

(CASE

WHEN NAME = 'PARSE COUNT (TOTAL)' THEN

'represents the total number of parses'

WHEN NAME = 'PARSE COUNT (HARD)' THEN

'represents the number of times of hard parsing'

WHEN NAME = 'SESSION CURSOR CACHE COUNT' THEN

'indicates the number of cursors cached'

WHEN NAME = 'SESSION CURSOR CACHE HITS' THEN

'represents the number of times the cursor was found from the cache'

WHEN NAME = 'OPENED CURSORS CURRENT' THEN

'represents the number of cursors opened in SESSION'

END)

FROM V$SESSTAT A, V$STATNAME B

WHERE A.STATISTIC# = B.STATISTIC#

AND B.NAME IN ('PARSE COUNT (HARD)'

'PARSE COUNT (TOTAL)'

'SESSION CURSOR CACHE COUNT'

'SESSION CURSOR CACHE HITS'

'OPENED CURSORS CURRENT')

-- AND SID=11

GROUP BY B.NAME

ORDER BY NAME

-- # is used to measure the software / hardware parsing / cursor sharing ratio.

Eighth, view the session and lock objects of uncommitted things

SELECT DISTINCT S.SID

S.SERIAL#

S.MACHINE

L.SQL_TEXT

S.LAST_CALL_ET

'ALTER SYSTEM KILL SESSION''| | S.SID | |','| | S.SERIAL# | |

'';'

LO.ORACLE_USERNAME

LO.OS_USER_NAME

AO.OBJECT_NAME

LO.LOCKED_MODE

FROM V$SESSION S

V$TRANSACTION T

V$SQL L

V$LOCKED_OBJECT LO

DBA_OBJECTS AO

WHERE S.TADDR = T.ADDR

AND S.PREV_SQL_ADDR = L.ADDRESS

AND AO.OBJECT_ID = LO.OBJECT_ID

AND LO.SESSION_ID = S.SID

9. Go to the database to find the executed SQL through the PID in the system:

SELECT A.USERNAME, A.PROGRAM, B.SPID, C.SQL_TEXT, C.SQL_FULLTEXT

FROM V$SESSION A, V$PROCESS B, V$SQLAREA C

WHERE A.PADDR = B.ADDR

AND A.SQL_HASH_VALUE = C.HASH_VALUE

AND A.STATUS = 'ACTIVE'

AND A.USERNAME NOT IN ('SYS',' SYSTEM', 'SYSMAN')

AND A.SID! = USERENV ('SID')

AND B.SPID = fill in PID

10. Create statements after comparing the results of sequence / index differences (for example, comparing A user index with B user, creating A user with more B users in B user)

[both of the following SQL need to be executed in the absence of sequence/index A users]

-- # creation statement of SEQUENCE:

SELECT 'CREATE SEQUENCE' | | SEQUENCE_NAME | | 'MINVALUE' | | MIN_VALUE | |

'MAXVALUE' | | MAX_VALUE | | 'START WITH' | | LAST_NUMBER | |

'INCREMENT BY' | | INCREMENT_BY | | (CASE

WHEN CACHE_SIZE = 0 THEN

'NOCACHE'

ELSE

'CACHE' | | CACHE_SIZE

END) | |';'

FROM USER_SEQUENCES W

WHERE-filter out the SEQUENCE of the logged-in user

NOT EXISTS (SELECT 1

FROM USER_SEQUENCES@DB_SINOSOFT W1

WHERE W.SEQUENCE_NAME = W1.SEQUENCE_NAME)

-- # create statements for index difference results

SELECT 'CREATE' | | INDEX_TYPE | | 'INDEX' | | INDEX_NAME | |'ON'| |

TABLE_NAME |'('| | LISTAGG (CNAME,',') WITHIN GROUP (ORDER BY COLUMN_POSITION) | |');'

FROM (SELECT IC.INDEX_NAME

IC.TABLE_NAME

IC.COLUMN_NAME CNAME

IC.COLUMN_POSITION

COUNT (IC.INDEX_NAME) OVER (PARTITION BY IC.INDEX_NAME, IC.TABLE_NAME) CON

I.INDEX_TYPE

FROM USER_IND_COLUMNS@DB_SINOSOFT IC

JOIN USER_INDEXES@DB_SINOSOFT I

ON I.INDEX_NAME = IC.INDEX_NAME

WHERE

-- filter out the INDEX of logged-in users

NOT EXISTS

(SELECT 1

FROM USER_IND_COLUMNS IC1

WHERE IC1.INDEX_OWNER = UPPER ('& TO_USERNAME')

AND IC.INDEX_NAME = IC1.INDEX_NAME)

Filter out the primary key to avoid index creation. The error object already exists when creating the primary key.

AND IC.INDEX_NAME NOT IN

(SELECT C.CONSTRAINT_NAME FROM USER_CONSTRAINTS@DB_SINOSOFT C)

ORDER BY IC.INDEX_NAME, IC.COLUMN_POSITION)

GROUP BY INDEX_TYPE, CON, INDEX_NAME, TABLE_NAME

11. View the objects of hotspot blocks

SELECT A.HLADDR, A.FILE#, A.DBABLK, A.TCH, A.OBJ, B.OBJECT_NAME

FROM X$BH A, DBA_OBJECTS B

WHERE (A.OBJ = B.OBJECT_ID OR A.OBJ = B.DATA_OBJECT_ID)

AND A.HLADDR = '0000000054435000'-V$SESSION_WAIT.P1RAW

UNION

SELECT HLADDR, FILE#, DBABLK, TCH, OBJ, NULL

FROM X$BH

WHERE OBJ IN (SELECT OBJ

FROM X$BH

WHERE HLADDR = '0000000054435000'

MINUS

SELECT OBJECT_ID

FROM DBA_OBJECTS

MINUS

SELECT DATA_OBJECT_ID FROM DBA_OBJECTS)

AND HLADDR = '0000000054435000'

ORDER BY 4

11. Check the size / total of a user's table

SELECT T.TABLE_NAME

TC.COMMENTS

T.NUM_ROWS

ROUND (SUM (S.BYTES / 1024 / 1024 / 1024)) GB

FROM USER_TABLES T

JOIN USER_SEGMENTS S

ON S.SEGMENT_NAME = T.TABLE_NAME

JOIN USER_TAB_COMMENTS TC

ON TC.TABLE_NAME = T.TABLE_NAME

GROUP BY T.TABLE_NAME, TC.COMMENTS, T.NUM_ROWS

ORDER BY NUM_ROWS DESC NULLS LAST

12. Recompile invalid storage / package statements:

SELECT 'ALTER' | | (CASE

WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

'PACKAGE' ELSE OBJECT_TYPE

END) | |'| | OWNER | |'. | | OBJECT_NAME | | 'COMPILE' | | (CASE)

WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN

'BODY;' ELSE'; 'END)-- display COMPILE unless the type is PACKAGE BODY and returns PACKAGE, and other normal display types, which are PACKAGE BODY display COMPILE BODY

OWNER

OBJECT_NAME

OBJECT_TYPE

STATUS

O.CREATED

LAST_DDL_TIME

FROM DBA_OBJECTS O

WHERE STATUS = 'INVALID'-- > Storage status' invalid'

13. Oracle looks at the usage of each table space and the maximum and minimum chunks:

SELECT UPPER (F.TABLESPACE_NAME) "tablespace name"

D.TOT_GROOTTE_MB Tablespace size (M)

D.TOT_GROOTTE_MB-F.TOTAL_BYTES "Space used (M)"

TO_CHAR (ROUND ((D.TOT_GROOTTE_MB-F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100)

2)

'990.99') "usage ratio"

F.TOTAL_BYTES "Free Space"

F.MAX_BYTES "maximum Block (G)"

FROM (SELECT TABLESPACE_NAME

ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) TOTAL_BYTES

ROUND (MAX (BYTES) / 1024 / 1024 / 1024, 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

(SELECT DD.TABLESPACE_NAME

ROUND (SUM (DD.BYTES) / 1024 / 1024 / 1024, 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

14. Oracle checks the usage of TEMP tablespaces:

SELECT F.BYTES_FREE + F.BYTES_USED TOTAL_BYTES

F.BYTES_FREE + F.BYTES_USED-NVL (P.BYTES_USED, 0) FREE_BYTES

D.FILE_NAME

NVL (P.BYTES_USED, 0) USED_BYTES

FROM SYS.V_$TEMP_SPACE_HEADER F

DBA_TEMP_FILES D

SYS.V_$TEMP_EXTENT_POOL P

WHERE F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME

AND F.FILE_ID (+) = D.FILE_ID

AND P.FILE_ID (+) = D.FILE_ID

-- > equivalent to

SELECT TABLESPACE_NAME

TF.TABLESPACE_SIZE

TF.FREE_SPACE

TF.TABLESPACE_SIZE-TF.FREE_SPACE

FROM DBA_TEMP_FREE_SPACE TF

15. Oracle uses several SQL to check the progress of the rollback:

SELECT DISTINCT KTUXESIZ FROM X$KTUXE WHERE KTUXESTA = 'ACTIVE'

SELECT USED_UBLK FROM V$TRANSACTION

SELECT KTUXEUSN, KTUXESLT

FROM X$KTUXE

WHERE / * KTUXECFL = 'DEAD' AND*/

KTUXESTA = 'ACTIVE'

SELECT * FROM V_$FAST_START_TRANSACTIONS

SELECT USED_UBLK, T.USED_UREC FROM V$TRANSACTION T

-- the field UNDOBLOCKSDONE,UNDOBLOCKSTOTAL in the query view V$FAST_START_TRANSACTIONS estimates the progress of SMON recovery

At this point, the study of "what are the common sql in Oracle DBA" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.