Common Oracle DBA Statement 2

Source: Internet
Author: User
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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.