Oracle maintenance common SQL statement

1, the name for the table space and size select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

2, check the name of the table space physical file and size Select TableSpace_name, File_ID, File_name, Round (Bytes / (1024 * 1024), 0) Total_Space from DBA_DATA_FILES ORDER BY TABLESPACE_NAME

3, see the name and size of the rollback select segment_name, tablespace_name, r.status, (initial_extent / 1024) InitialExtent, (next_extent / 1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v $ rollstat v Where r.segment_id = V.usn ( ) Order by segment_name;

4, check the control file SELECT NAME FROM V $ ControlFile;

5, check the log file Select MEMBER from V $ logfile;

6, check the usage of table space SELECT SUM (Bytes) / (1024 * 1024) As Free_Space, TableSpace_name from dba_free_space group by tablespace_name;

SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES Free, (B.BYTES * 100) /A.BYTES "% use", (C.BYTES * 100) /A.BYTES "% free "From sys.sm $ ts_avail a, sys.sm $ ts_used b, sys.sm $ ts_free c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name;

7. View Database Library Object Select Owner, Object_Type, Status, Count (*) Count # from all_Objects group by Owner, Object_Type, Status

8. View the version of the database Select Version from product_component_version where substr (product, 1, 6) = 'Oracle'

9. View the database creation date and archive SELECT CREATED, LOG_MODE, LOG_MODE FROM V $ DATABASE

10. Capture a long time SQL Column UserName Format A12 Column Opname Format A16 Column ProGress Format A8

select username, sid, opname, round (sofar * 100 / totalwork, 0) || '%' as progress, time_remaining, sql_text from v $ session_longops, v $ sql where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value /

11, view data sheet parameter information SELECT partition_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS, freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks, empty_blocks, avg_space , chain_cnt, avg_row_len, sample_size, last_analyzed fROM dba_tab_partitions --WHERE table_name =: tname AND table_owner =: affairs select towner ORDER BY partition_position12, see not submitted * from v $ locked_object; select * from v $ transaction;

13, find Object which process uses Select P.SPID, S.SID, S.Serial # serial_num, s.username user_name, a.type object_type, s.OWNER, A. Object Object_name, Decode (Sign (48 - Command), 1, To_Char (Command), 'Action Code #' || To_char (Command)) Action, P.Program Oracle_Process, S.Terminal Terminal, S.Program Program, S.Status session_status from v $ session S, V $ Access A, V $ process p Where s.paddr = p.addr and s.type = 'user' and a.sid = s.SID and A. Object = 'Subscriber_attr' Order by S.Username, S .osuser

14, rollback view select rownum, sys.dba_rollback_segs.segment_name Name, v $ rollstat.extents Extents, v $ rollstat.rssize Size_in_Bytes, v $ rollstat.xacts XActs, v $ rollstat.gets Gets, v $ rollstat.waits Waits , v $ rollstat.writes Writes, sys.dba_rollback_segs.status status from v $ rollstat, sys.dba_rollback_segs, v $ rollname where v $ rollname.name ( ) = sys.dba_rollback_segs.segment_name and v $ rollstat.usn ( ) = V $ rollname.usn order by rownum

15. Top session Select S.Schemaname Schema_name, Decode (Sign (48 - Command), 1, To_Char (Command), 'Action Code #' || to_CHAR (Command)) Action, Status Session_Status, S . osuser os_user_name, s.sid, p.spid, s.serial # serial_num, nVL (S.Username, '[Oracle Process]') User_Name, S.Terminal Terminal, S.Program Program, St.Value Criteria_Value from V $ SESSSTAT ST, V $ Session S, V $ process p where st.sid = s.SID and st.statistic # = to_number ('38 ') and (' all '=' all 'or s.status =' all ') And p.addr = s.paddr Order by St.Value Desc, P.SPID ASC, S.UserName ASUSER ASC16, view lock (LOCK) SELECT / * Rule * / LS.OSUSER OS_USER_NAME, LS. Username User_name, decode (ls.type, 'rw', 'Row Wait Enqueue Lock ",' TM ',' DML Enqueue Lock ',' TX ',' Transaction Enqueue Lock ',' UL ',' User Supplied Lock ' LOCK_TYPE, O.Object_name Object, Decode (Ls.Lmode, 1, Null, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive' , NULL) LOCK_MODE, O.OWNER, LS.SID, LS.SERIAL # Serial_Num, Ls.ID1 , ls.id2 from sys.dba_objects o, (Select S. OSUser, S.Username, L. Type, L.Lmode, S.SID, S.Serial #, L.ID1, L.ID2 from V $ Session S, V $ lock l where s.sid = L.SID) ls where object_id = ls.id1 and o.owner <> 'sys' order by o.owner, o.object_name

17. See Wait (Wait) SELECT V $ Waitstat.class, V $ Waitstat.count Count, Sum (V $ sysstat.value) SUM_VALUE FROM V $ WAITSTAT, V $ sysstat where v $ sysstat.name in ('DB Block Gets', 'Consistent Gets', Group by V $ Waitstat.class, V $ Waitstat.count

18, see the sga case SELECT NAME, BYTES FROM SYS.V_ $ SGASTAT ORDER BY NAME ASC19, see catched object SELECT owner, name, db_link, namespace, type, sharable_mem, loads, executions, locks, pins, kept FROM v $ db_object_cache

20, see the V $ SQLAREA SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED FROM V $ SQLAREA

21. View Object Decode (O.Type #, 1, 'INDEX', 2, 'TABLE', 3, 'Cluster', 4, 'View', 5, 'Synynym', 6, 'SEQUENCE', 'Other') Object_type, count (*) Quantity from sys.obj $ o where o.type #> 1 group by decode (o.type #, 1, 'index', 2, 'table', 3, 'cluster " , 4, 'View', 6, 'Sequence', 'Other') Union Select 'Column', Count (*) from Sys.col $ Union Select 'DB Link', Count (*) from

22. According to users View Object Type Select U.Name Schema, Sum (Decode (O.Type #, 1, 1, Null) Indexes, SUM (Decode (O.Type #, 2, 1, Null) Tables, SUM (Decode (O.Type #, 3, 1, Null) Clusters, Sum (Decode (O.Type #, 4, 1, NULL) Views, SUM (Decode (O.Type #, 5, 1, NULL) Synyms, Sum (Decode (O.Type #, 6, 1, NULL) SEQUENCES, SUM (Decode (O.Type #, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1)) Others from sys.obj $ o, sys.user $ u Where o.type #> = 1 and u.user # = o.owner # and u.name

23. Related information about Connection 1) View which users connect Select S. OS_USER_NAME, DECODE (Sign (48 - Command), 1, To_Char (Command), 'Action Code #' || to_char (command)) Action, p.program oracle_process, status session_status, s.terminal terminal, s.program program, s.username user_name, s.fixed_table_sequence activity_meter, '' query, 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial # serial_num From v $ session s, v $ process p where spaddr = p.addr and s.type = 'user' order by s.username, s.suser 2) According to V.SID View Resource Occupation of the corresponding connection, SELECT N.Name, V.Value, N.class, n.statistic # from V $ STATNAME N, V ​​$ SESSSTAT V where v.sid = 71 and v.statistic # = n.statistic # Order by n.class, n. statistic # 3) See the sid the corresponding connection running sql select / * PUSH_SUBQ * / command_type, sql_text, sharable_mem, persistent_mem, runtime_mem, sorts, version_count, loaded_versions, open_versions, users_opening, executions, users_executing, loads, first_load_time, invalidations, Parse_calls, disk_reads, buffer_gets, rows_processed, sysdate start_time, sysdate fini SH_TIME, '>' || address sql_address, 'n' status from v $ sqlarea where address = (SELECT SQL_ADDRESS FROM V $ session where sid = 71)

24. Query Table Space Use SELECT A.TABLESPACE_NAME "Table Space Name", 100-Round ((NVL (B.Bytes_Free, 0) /A.BYTES_ALLOC) * 100, 2) "Occupation (%)", ROUND A.BYTES_ALLOC / 1024/1024, 2) "Capacity (M)", ROUND (NVL (B.Bytes_Free, 0) / 1024/1024, 2) "Idle (M)", Round ((A.BYTES_ALLOC-NVL ( B.BYTES_FREE, 0)) / 1024/1024, 2) "Sampling of" using (m) ", Largest" maximum expansion (m) ", to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') time "from (select f.tablespace_name, sum (f.bytes) bytes_alloc, sum (decode (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name, sum (f.bytes) bytes_free from dba_free_space f group by tablespace_name) b, (select round (max (ff.length) * 16 / 1024,2) Largest, ts.name tablespace_name from sys. FET $ ff, sys.file $ tf, sys.ts $ ts where ts.ts # = ff.ts # and ff.file # = tf.relfile # and ts.ts # = tf.ts # group by ts.name , tf.blocks) c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name25, degree of fragmentation lookup table space select tablespace_name, count (tablespace_name) from dba_free_space group by table Space_name haVing count (TABLESPACE_NAME)> 10;

Alter TableSpace Name Coalesce; Alter Table Name Deallocate Unused;

create or replace view ts_blocks_v as select tablespace_name, block_id, bytes, blocks, 'free space' segment_name from dba_free_space union all select tablespace_name, block_id, bytes, blocks, segment_name from dba_extents;

SELECT * from TS_BLOCKS_V;

SELECT TABLESPACE_NAME, SUM (BYTES), Max (Bytes), Count (Block_ID) from DBA_Free_Space Group by TableSpace_name;