Common SQL query:
1, check the name and size of the table space
SELECT T.TABLESPACE_NAME, ROUND (Bytes / (1024 * 1024)), 0) TS_SIZEFROM DBA_TABLESPACES T, DBA_DATA_FILES DWHERE T.TABLESPACE_NAME = D.TABLESPACE_NAMEGROUP BY T.TABLESPACE_NAME;
2, check the name and size of the physical file of the table space
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROT (Bytes / (1024 * 1024), 0) Total_SpaceFrom DBA_DATA_FILESORDER by TABLESPACE_NAME
3. View the name of the rollback segment and size
select segment_name, tablespace_name, r.status, (initial_extent / 1024) InitialExtent, (next_extent / 1024) NextExtent, max_extents, v.curext CurExtentFrom dba_rollback_segs r, v $ rollstat vWhere 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_spacegroup by tablesspace_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 cwhere a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name;
7, check the database library object
Select Owner, Object_Type, Status, Count (*) Count # from all_Objects group by Owner, Object_type, status
8, check the version of the database
Select version from product_component_version where substr (product, 1, 6) = 'oracle'
9. View the creation date and archive of the database
SELECT CREATED, LOG_MODE, LOG_MODE FROM V $ DATABASE
10, capture SQL running for a long time
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 the 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 =: townerORDER BY partition_position12 view the transaction has not submitted select * from v $ locked_object; select * from v $ transaction;.
13. Find object p.SPID, S.SID, S.Serial # serial_num, s.use Object_Type, S. OS_USER_NAME, A.OWNER, A. Object Object_name, Decode (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'Rder by S.Username, S. Orsuser
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 rownum15. 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 $ SESSTAT 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 ASC, S. OSUSER ASC
16. 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', 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 = Ls WHERE O.Object_ID = ls.id1 and o.owner <> 'sys' Order by O.Owner, O.Object_name17. View Wait (Wait) SELECT V $ Waitstat.class, V $ Waitstat.count Count, SUM (V $ SSSTAT.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. View SGA situation Select Name, Bytes from sys.v_ $ sgastat order by name asc
19. View Catched ObjectSelect Owner, Name, DB_LINK, NAMESPACE, TYPE, ShaBLE_MEM, LOADS, Executions, Locks, Pins, Kept from V $ db_Object_cache 20. View V $ SQLAREASELECT 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 $ SQLAREA21. 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', 5, 'Synym', 6, 'Sequence', 'Other') Union Select 'Column', Count (*) from Sys.col $ Union Select 'DB Link', COUNT (*) from
twenty two. View the 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) Synonyms , 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 <> PUBLIC 'Group By u.name Order by Sys.Link $ Union Select 'Constraint', Count (*) from sys.con $
twenty three. Related information about Connection 1) See which users connect Select S.OSUSER 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 s.paddr = p.addr and s.type = 'user' order by s.username, s.osuser2) 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 ) according to the corresponding connection sid view sqlselect running / * 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_rea ds, buffer_gets, rows_processed, sysdate start_time, sysdate finish_time, '>' || address sql_address, 'N' status from v $ sqlareawhere address = (select sql_address from v $ session where sid = 71)
twenty four. Query Table Space Usage Select a.tables, 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) "Use (M)", Largest "maximum expansion segment (m)", to_char (sysdate, 'YYYY-MM-DD HH24: MI: SS') "Sampling 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.rellet # 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 query table space
SELECT TABLESPACE_NAME, Count (TableSpace_name) from dba_free_space group by tablespace_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;
26. What database instances are running SELECT INST_NAME FROM V $ ACTIVE_INSTANCES;
Www.loveunix.com master by sdawnyj email: noamebaby@sohu.com2003-11-22
