11. View the parameter information of a data table: Select partition_name, high_value, partition, tablespace_name, pct_free, upper, lower, max_trans, upper, next_extent, min_extent, max_extent, upper, freelists, lower, logging, buffer, 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 =: Townerorder by partition_position 12. view the uncommitted transaction select * from V $ locked_object; select * from V $ transaction; 13. Find out which processes the object uses selectp. spid, S. sid, S. serial # serial_num, S. username user_name,. type object_type, S. osuser OS _user_name,. owner,. 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. View select rownum, sys. dba_rollback_segs.segment_name name, V $ rollstat. extentsextents, V $ rollstat. rssize size_in_bytes, V $ rollstat. 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 andv $ 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, statussession_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 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 16. View the 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', 'U ', '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 wait (wait) status select v $ waitstat. class, V $ waitstat. count count, sum (V $ sysstat. value) sum_valuefrom 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 select name, bytes from SYS. V _ $ sgastat order by name ASC 19. View catched objectselect owner, name, db_link, namespace, type, sharable_mem, loads, executions, locks, pins, kept from V $ db_object_cache 20. View v $ export SQL _text, sharable_mem, delimiter, runtime_mem, sorts, version_count, loaded_versions, open_versions, users_opening, executions, users_executing, loads, statistics, metrics, partitions, disk_reads, metrics, 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 fromsys. 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. 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 wh Ere O. type #> = 1 and U. user # = O. owner # And U. name <> 'public' group by U. name/* order by \ * sys. link $ \ * Union select 'constraint', count (*) from sys. con $ * \ */23. Related information about connection 1) check which users are connected to 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. osuser2) According to v. sid view the resource usage of the corresponding connection. Select n. name, V. value, N. class, N. statistic # from V $ statname N, V $ sesstat vwhere v. SID = 71 and V. statistic # = n. statistic # order by N. class, N. statistic #3) view the sqlselect/* + push_subq */command_type, SQL _text, sharable_mem, memory, runtime_mem, sorts, version_count, loaded_versions, open_versions, users_opening, executions, users_executing, loads, tables, invalidations, parse_cils, disk_reads, buffer_gets, rows_processed, sysdate start_time, sysdate finish_time, '>' | Address SQL _address, 'N' statusfrom v $ sqlareawhere address = (select SQL _address from V $ session where Sid = 71)