Oracle DBA Common Statement 2____oracle

11.  View parameter information for a datasheet 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, Las
 
T_analyzed from dba_tab_partitions--where table_name =: tname and Table_owner =: Towner ORDER by Partition_position
12. View a transaction that has not yet been submitted select * from V$locked_object;
 
SELECT * from V$transaction; 13. Find which processes the object is used for select P.spid, S.sid, s.serial# serial_num, S.username user_name, A.type object_type, S.osuser Os_user_na Me, A.owner, A.object object_name, decode (sign (48-command), 1, to_char (command), ' Action Code # ' | | to_char (command)) a ction, P.program oracle_process, s.terminal terminal, s.program program, S.status session_status from V$session, V$ACC ESS 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 program, St.value crit Eria_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.us Ername ASC, S.osuser ASC 16. View lock Condition 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, ' Exclusiv E ', null) Lock_mode, O.owner, Ls.sid, ls.serial# serial_num, Ls.id1, ls.id2 from Sys.dba_objects O, (sele CT S.osuser, S.username, L.type, L.lmode, S.sid, s.serial#, L.id1, L.id2 from V$session, v$l Ock l where s.sid = l.sid) ls where o.object_id = ls.id1 and O.owner <> ' SYS ' ORDER by O.owner, O.object_na Me 17. View wait 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 blocks gets ', ' consistent gets ') group by V$waitstat.class, V$waitstat.count 18. View the SGA condition 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.C ol$/* Union Select ' DB LINK ', COUNT (*) from*/22.    View by user Object Kind 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 &LT;&G T ' Public ' GROUP by U.name/*order by \*sys.link$ \*union Select ' CONSTRAINT ' and COUNT (*) from sys.con$*\*\*/23. Related information about connection 1) see which users are connected to select S.osuser os_user_name, decode (sign (48-command), 1, to_char (command), ' Action Cod E # ' | | 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 wher E s.paddr=p.addr and S.type = ' USER ' ORDER by S.username, S.osuser 2) Select V.sid, N.name, N, according to V.value view the resource occupancy of the corresponding connection . class, n.statistic# from V$statname N, v$sesstat v where v.sid = the and v.statistic# = n.statistic# ORDER by N.class , n.statistic# 3) to view the corresponding connection running SQL Select/*+ PUSH_SUBQ/Command_type, Sql_text, Sharable_mem, Persistent_mem, Ru, based on SID Ntime_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, Sysdat e finish_time, ' > ' | | Address sql_address, ' N ' status from V$sqlarea where address = (select sql_address from v$session where sid = 71)