Oracle maintenance Common SQL statement Summary

How to remotely determine the Oracle Database Installation Platform select * fromv $ version; view the usage of the tablespace selectsum (bytes) (1024*1024) asfree_space, tablespace_namefromdba_free_spacegroupbytablespace_name; SELECTA. TABLESPACE_NAME,. BYTESTOTAL, B. BYTESUSED,

How to remotely determine the Oracle Database Installation Platform select * from v $ version; view the table space usage select sum (bytes)/(1024*1024) as free_space, tablespace_name from dba_free_space group by tablespace_name; select. TABLESPACE_NAME,. bytes total, B. bytes used,

How to remotely determine the Oracle Database Installation Platform
Select * from v $ version;
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;
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, 1, 6) = 'oracle ';
9. view the database creation date and archiving method
Select Created, Log_Mode, Log_Mode From V $ Database;
10. How to remotely determine the Oracle Database Installation Platform
Select * from v $ version;
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;
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. 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
/
16. View the parameter information of a 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
17. Find out which processes are used by the object
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
18. Resource-consuming process (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,
S. 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
19. 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, 'clusive ', 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
Check the link of the Computer Based on the sid.
Column osuser format a15
Column username format a10
Column machine format a30
Select osuser, machine, username, sid, serial # from v $ session where sid = '20140901 ';
Query the corresponding SQL statement based on the sid
Select SID, SQL _TEXT from v $ open_cursor where SID = '000000 ';

20. View wait Status
SELECT v $ waitstat. class, v $ waitstat. 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
21. View sga status
Select name, bytes from sys. V _ $ SGASTAT ORDER BY NAME ASC
22. View catched object
SELECT owner, name, db_link, namespace,
Type, sharable_mem, loads, executions,
Locks, pins, kept FROM v $ db_object_cache
23. 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
24. 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
25. 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 'constraint', count (*) from sys. con $
26. 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, s. 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)
27. 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: 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),
(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
28. query the fragmentation degree of the 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
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;
29. Query which database instances are running
Select inst_name from v $ active_instances;
30. Search for oracle performance bottleneck SQL
Select SQL _text, spid, v $ session. program, process from
V $ sqlarea, v $ session, v $ process
Where v $ sqlarea. address = v $ session. SQL _address
And v $ sqlarea. hash_value = v $ session. SQL _hash_value
And v $ session. paddr = v $ process. addr
And v $ process. spid in (operating system PID );
Select sid, event, p1, p1text from v $ session_wait;
31. Find the most resource-consuming SQL statement
Select * from v $ process where spid = '000000 ';
Select SQL _hash_value, machine, username, program from v $ session where PAddr = '63b7a584 ';
Select * from v $ sqltext where hash_value = '201312 ';
Select * from v $ SQL where hash_value = '201312 ';
Select * from v $ sqlarea where hash_value = '201312 ';
SELECT. username,. machine,. program,. sid,. serial #,. status, c. piece, c. SQL _text FROM v $ session a, v $ process B, v $ sqltext c WHERE B. spid = '000000' AND B. addr =. paddr AND. SQL _address = c. address (+) order by c. to find the most resource-consuming SQL statement, piece can first use top and other tools to find the process with the best resources (remember the process number). For example, the operating system process number is 2796, then, according to the process number (v $ process. spid) Find the process address (v $ process. addr), and then find the corresponding sid (v $ session. sid), and then find the corresponding hash alue (v $ ses Sion. SQL _hash_value). Then, based on the hash alue, find the corresponding SQL statement (SQL _text) in the v $ sqltext, $ SQL, v $ sqlarea, and other views ).
Select * from v $ process where spid = '000000 ';
Select SQL _hash_value, machine, username, program from v $ session where PAddr = '63b7a584 ';
Select * from v $ sqltext where hash_value = '201312 ';
Select * from v $ SQL where hash_value = '201312 ';
Select * from v $ sqlarea where hash_value = '201312 ';
SELECT. username,. machine,. program,. sid,. serial #,. status, c. piece, c. SQL _text FROM v $ session a, v $ process B, v $ sqltext c WHERE B. spid = '000000' AND B. addr =. paddr AND. SQL _address = c. address (+) order by c. piece