Oracle maintains common SQL statements

oracle| statement
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 table space physical file name 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. View 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 the control file
Select name from V$controlfile;

5. View Log file
Select member from V$logfile;

6, view the use 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 "% 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 Library objects
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 the way of filing
Select Created, Log_mode, Log_mode from V$database;

10. Capturing SQL for a long run
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 table 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 =: Towner
ORDER BY Partition_position

12. View the transactions that have not yet been submitted
SELECT * from V$locked_object;
SELECT * from V$transaction;

13. Find out which processes the object is using
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 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 segment 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, 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 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 (' ") 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, check the lock situation
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, look at the waiting (wait) situation
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 the SGA situation
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_calls, 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 the type of object 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
sys.link$ Union Select ' CONSTRAINT ', COUNT (*) from sys.con$

23. Related Information about connection
1 to see which users are connected
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) According to the V.sid view of the corresponding connection resource occupancy, etc.
Select N.name,
V.value,
N.class,
n.statistic#
From V$statname N,
V$sesstat V
where V.sid = the
v.statistic# = n.statistic#
Order by N.class, n.statistic#
3 to view the SQL that the corresponding connection is running according to 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_calls,
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, the query table space use situation select A.tablespace_name "Table space name",
100-round ((NVL (b.bytes_free,0)/a.bytes_alloc) *100,2) "occupancy rate (%)",
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.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, the query table space fragmentation degree
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 spaces ' 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;