Oracle data dictionary

Tables and columns

DBA_TABLES, ALL_TABLES, and USER_TABLES show general information about database tables.

DBA_TAB_COLUMNS, ALL_TAB_COLUMNS, and USER_TAB_COLUMNS display information about the columns of each database table.

Note: DBA_OBJECTS, ALL_OBJECTS, and USER_OBJECTS display information about schema objects, including tables.

User_Sys_Privs and User_Role_Privs can view permissions

Select SYS_CONTEXT ('USERENV','IP_ADDRESS') from dual-View the system ip address

Select SYS_CONTEXT ('USERENV','HOST') from dual-View the host name

-1. Query database name:

Select name,dbid from vs. database, or show parameter db_name.

-2. Query instance name:

Select instance_name from vastly instantly or show parameter instance_name

Select * from user_tablespaces-tablespace details

SELECT * from user_ts_quotas

Select username,default_tablespace from user_users;- to view the user's default tablespace

Select * from dba_tables where tablespace_name=' tablespace names'- Note that tablespace names are case sensitive.

Select * from user_tables;-contains information about the names, number of columns, and other details of all tables

Select * from all_tables;-query the information of all accessible tables

Select * from user_tab_columns-check the details of the user table

Select * from user_Tab_Privs-View the current user's permissions on all tables

Select * from all_Tab_Privs-View all user permissions on all tables

Select * from user_segments-a bit like all_tables (monitoring the growth of the table)

Select * from User_Catalog-View the tables, views and other objects under the current user (query which tables are available)

Select * from All_Catalog-View tables, views, and other objects under all users (query which tables are available)

Select * from User_Objects-View the tables, views, and other objects under the current user (table details)

Select * from user_users-query current user

Select * from All_Users-query all accessible users

Select * from User_Sys_Privs-View the permissions that the current user has

Select * from User_Role_Privs-View the roles the user has

All_source this view should store definitions of Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY

Select * from dba_source-basic information about all stored procedures for DBA users

Select * from all_source-basic information about all stored procedures for all users

Select * from user_source-basic information about all stored procedures for the current user

-- through the table name search process--

Select distinct name from USER_SOURCE where type = 'PROCEDURE' and text like'% TABLE_A%'

-query the location of the data files in the tablespace

Select t1.name from v$tablespace T2 where t1.ts# = t2.ts#

Or

Select * FROM DBA_DATA_FILES

+ DATADG/zdxyxdb/datafile/tbs_meta.652.897498109

-

-Dictionary table-

-users and permissions

SELECT * from User_Users-default tablespace

SELECT * from All_Users

SELECT * from Dba_Users

SELECT * from User_Sys_Privs

SELECT * from User_Role_Privs

SELECT * from DBA_ROLES-all roles

SELECT * from dba_Sys_Privs

SELECT * from dba_Role_Privs

-Database objects. (check all tables, views, synonyms and sequences)

SELECT * from user_Catalog

SELECT * from cat-- synonym for user_Catalog

SELECT * from all_Catalog

SELECT * from dba_catalog

-sequence

SELECT * from all_Catalog WHERE table_type = 'SEQUENCE' AND owner='SO' AND table_name =' SEQ_ATOM_ACTION_ID'

SELECT so.SEQ_ATOM_ACTION_ID.nextval FROM dual

Select * from dba_sequences where sequence_owner='KONGZY'

SELECT * from USER_SEQUENCES

SELECT * from ALL_SEQUENCES WHERE sequence_owner='KONGZY'

-synonyms

Select * from all_synonyms WHERE owner='PUBLIC' AND table_name = 'USER_TABLES'

SELECT * from user_synonyms

-Table correlation

Select * from user_tablespaces-tablespace details

SELECT * from user_ts_quotas-space limit

Select * from user_users;-View the user's default tablespace

-Table

Select * from dba_tables

Select * from user_tables

Select * from all_tables

-column

SELECT * from user_tab_columns

Select * from all_tab_columns

Select * from dba_tab_columns

-permissions

Select * from user_Tab_Privs

Select * from all_Tab_Privs

Select * from dba_Tab_Privs