Common oracle statements

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;