Common Oracle views

1. Common ORACLE data dictionary table 1. view the current user's default tablespace SQLselectusername, default_tablespacefromuser_users; 2. view the current user's role SQLselect * fromuser_role_privs; 3. view the current user's system permissions and table-level permissions SQLselect * fromuser_sys_privs; SQLselect * fromuse

1. Common Oracle data dictionary table 1. view the current user's default tablespace SQLselectusername, default_tablespacefromuser_users; 2. view the current user's role SQLselect * fromuser_role_privs; 3. view the current user's system permissions and table-level permissions SQLselect * fromuser_sys_privs; SQLselect * fromuse

1 Oracle common data dictionary table

1. view the default tablespace of the current user

SQL> select username, default_tablespace from user_users;

2. view the role of the current user

SQL> select * from user_role_privs;

3. view the system and table-level permissions of the current user

SQL> select * from user_sys_privs;

SQL> select * from user_tab_privs;

4. view all tables under a user

SQL> select * from user_tables;

5. view the column attributes of all tables under the user

SQL> select * from USER_TAB_COLUMNS where table_name =: table_Name;

6. Display User Information (tablespace)

Select default_tablespace, temporary_tablespace

From dba_users

Where username = 'game ';

7. display the permissions of the current session

SQL> select * from session_privs;

8. displays the system permissions of a specified user.

SQL> select * from dba_sys_privs where grantee = 'game ';

9. Show privileged users

Select * from v $ pwfile_users;

10. Display User Information (tablespace)

Select default_tablespace, temporary_tablespace

From dba_users where username = 'game ';

11. display the user's PROFILE

Select profile from dba_users where username = 'game ';

2 tables

1. view all tables under a user

SQL> select * from user_tables;

2. View tables whose names contain log characters

SQL> select object_name, object_id from user_objects

Where instr (object_name, 'log')> 0;

3. view the creation time of a table

SQL> select object_name, created from user_objects where object_name = upper ('& table_name ');

4. view the size of a table

SQL> select sum (bytes)/(1024*1024) as "size (M)" from user_segments

Where segment_name = upper ('& table_name ');

5. view the table in the Oracle memory Partition

SQL> select table_name, cache from user_tables where instr (cache, 'y')> 0;

3 Index

1. view the number and category of Indexes

SQL> select index_name, index_type, table_name from user_indexes order by table_name;

2. view the indexed fields

SQL> select * from user_ind_columns where index_name = upper ('& index_name ');

3. view the index size

SQL> select sum (bytes)/(1024*1024) as "size (M)" from user_segments

Where segment_name = upper ('& index_name ');

4. Serial number

1. Check the serial number. last_number is the current value.

SQL> select * from user_sequences;

5 Views

1. view name

SQL> select view_name from user_views;

2. view the select statement for creating a view

SQL> set view_name, text_length from user_views;

SQL> set long 2000; Description: You can set the size of set long based on the text_length value of the view.

SQL> select text from user_views where view_name = upper ('& view_name ');

6 Synonyms

1. view the synonym name

SQL> select * from user_synonyms;

7 Constraints

1. view the constraints of a table

SQL> select constraint_name, constraint_type, search_condition, r_constraint_name

From user_constraints where table_name = upper ('& table_name ');

SQL> select c. constraint_name, c. constraint_type, cc. column_name

From user_constraints c, user_cons_columns cc

Where c. owner = upper ('& table_owner') and c. table_name = upper ('& table_name ')

And c. owner = cc. owner and c. constraint_name = cc. constraint_name

Order by cc. position;

8. storage functions and processes

1. view the status of functions and processes

SQL> select object_name, status from user_objects where object_type = 'function ';

SQL> select object_name, status from user_objects where object_type = 'Procedure ';

2. view the source code of functions and processes

SQL> select text from all_source where owner = user and name = upper ('& plsql_name ');

9 common data dictionaries:

Dba_data_files: Used to query information about database files.

Dba_db_links: includes all database links in the database, that is, databaselinks.

Dba_extents: information about all partitions in the database

Dba_free_space: Free partitions in all tablespaces

Dba_indexs: description of all indexes in the database

Dba_ind_columns: compression of index columns on all tables and aggregation

Dba_objects: all objects in the database

Dba_rollback_segs: rollback segment description

Dba_segments: storage space of all database segments in Segments

Dba_synonyms: Query synonym Information

Dba_tables: description of all data tables in the database

Dba_tabespaces: Information about tablespaces

Dba_tab_columns: All table descriptions, views, and clustered Columns

Dba_tab_grants/privs: Permission granted to the object

Dba_ts_quotas: Quota of all user tablespaces

Dba_users: information about all users of data

Dba_views: Text of all views in the database

10 common dynamic performance views:

V $ datafile: the data file used by the database

V $ librarycache: shares the management information of SQL statements in the pool.

V $ lock: sets all object lock information by accessing database sessions.

V $ log: extract information about redo log groups from the Control File

V $ logfile information about the instance resetting the log group file name and its location

V $ parameter: value of all items in the initialization parameter file

V $ process: information about the current process

V $ rollname: rollback segment information

V $ rollstat: Online rollback segment statistics

V $ rowcache: In-memory data dictionary activity/performance information

V $ session: session information

V $ sesstat: Report the statistics of the current session in v $ session

V $ sqlarea: displays statistics of the current cursor in the shared pool. The cursor is a memory area and is opened when Oracle processes SQL statements.

V $ statname: Meaning of each statistics reported in v $ sesstat

V $ sysstat: system statistics based on the current operation session

V $ waitstat: displays the details when more than one session accesses the database. When more than one session accesses the same information, a waiting condition may occur.

After summing up, the view and data dictionary are completely different, which is not so easy to confuse. Hey !!!

11 common SQL queries: 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, 1, 6) = '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 a 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 are used by the object

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 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,

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 ('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, 'clusive ', 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 wait Status

SELECT v $ waitstat. class, v $ waitstat. 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 'constraint', 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, s. 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)

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: 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),

(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;

26. Query which database instances are running

Select inst_name from v $ active_instances;

// Obtain the IP address of the server

Select utl_inaddr.get_host_address from dual

// Obtain the Client IP Address

Select sys_context ('userenv', 'host'), sys_context ('userenv', 'IP _ address') from dual