1. view the table space name 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. view the name and size of the tablespace physical file.
Select tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) total_space
From dba_data_files
Order by tablespace_name;
3. Check the rollback segment name and size.
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. View Control Files
Select name from v $ controlfile;
5. view log files
Select member from v $ logfile;
6. View table space usage
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 "% USED", (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 objects
Select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;
8. view the database version
Select version FROM Product_component_version
Where SUBSTR (PRODUCT,) = ''oracle '';
9. view the database creation date and archiving method
Select Created, Log_Mode, Log_Mode From V $ Database;
10. Capture SQL statements that have been 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 parameter information of the data table
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 =: towner
Order by partition_position
12. View uncommitted transactions
Select * from v $ locked_object;
Select * from v $ transaction;
13. Find out which processes the object is used
Select
P. spid,
S. sid,
S. serial # serial_num,
S. username user_name,
A. type object_type,
S. osuser OS _user_name,
A. 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,
S. 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. View rollback segments
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. resource-consuming processes (top sessions)
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,
S. program, st. value criteria_value from v $ sesstat st, v $ session s, v $ processp
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 status
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 O. object_id = ls. id1 and O. Owner
<> ''Sys ''order by O. Owner, O. object_name
17. view the waiting status
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. View sga status
Select name, bytes from sys. V _ $ SGASTAT ORDER BY NAME ASC
19. View catched object
SELECT owner, name, db_link, namespace,
Type, sharable_mem, loads, executions,
Locks, pins, kept FROM v $ db_object_cache
20. View 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_cils, DISK_READS,
BUFFER_GETS, ROWS_PROCESSED from v $ SQLAREA
21. view the number of object categories
Select decode (o. type #, 1, ''index'', 2, ''table'', 3, ''cluster'', 4, ''view'', 5,
''Synonym'', 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, ''synonym'', 6, ''sequence '', ''other'') Union select
''Column'', count (*) from SYS. Col $ Union select ''db link '', count (*) from
22. view object types by user
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
SYS. Link $ Union select ''straint'', count (*) from SYS. Con $
23. Information about connection
1) check which user connections are available
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. osuser
2) view the resource usage of the corresponding connection according to v. Sid.
Select N. Name,
V. value,
N. Class,
N. statistic #
From v $ statname n,
V $ sesstat v
Where v. sid = 71 and
V. statistic # = n. statistic #
Order by n. class, n. statistic #
3) view the SQL statement that the connection is running based on the sid.
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_cils,
Disk_reads,
Buffer_gets,
Rows_processed,
Sysdate start_time,
Sysdate finish_time,
''>'' Address SQL _address,
''N' status
From v $ sqlarea
Where address = (select SQL _address from v $ session where sid = 71)
24. query table space usage select a. tablespace_name "tablespace name ",
100-round (nvl (B. bytes_free, 0)/a. bytes_alloc) *) "usage (% )",
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 extension segment (M )",
To_char (sysdate, ''yyyy-mm-dd hh24: mi: s'') "Sampling Time"
From (select f. tablespace_name,
Sum (f. bytes) bytes_alloc,
Sum (decode (f. autoextensible, ''y'', f. maxbytes, ''no'', f. bytes) maxbytes
From dba_data_files f
Group by tablespace_name),
(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_name
25. query the degree of table space fragmentation
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
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;