1.1 dba_ start
dba_users Database user information
dba_segments Table segment information
dba_extents Data area information
dba_objects Database object information
dba_tablespaces Database tablespace information
dba_data_files Data file setting information
dba_temp_files Temporary data file information
dba_rollback_segs Rollback segment information
dba_ts_quotas User tablespace quota information
dba_free_space Database free space information
dba_profiles Database user resource restriction information
dba_sys_privs User's system permission information
dba_tab_privs Object permission information that the user has
dba_col_privs Column object permission information that the user has
dba_role_privs User's role information
dba_audit_trail Audit trail record information
dba_stmt_audit_opts audit setting information
dba_audit_object Object audit result information
dba_audit_session Session audit result information
dba_indexes Index information for user mode
The following is desc DBA_ Results for users. Others are similar.
SQL> desc dba_users; name Is it empty? type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(30) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(30) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) EXTERNAL_NAME VARCHAR2(4000) PASSWORD_VERSIONS VARCHAR2(8) EDITIONS_ENABLED VARCHAR2(1) AUTHENTICATION_TYPE VARCHAR2(8)
one point two user_ start
user_objects User object information
user_source All resource object information of database users
user_segments User segment information
user_tables User's table object information
user_tab_columns User's tabular information
user_constraints User's object constraint information
user_sys_privs System permission information of the current user
user_tab_privs Object permission information of the current user
user_col_privs Table column permission information of the current user
user_role_privs Role permission information of the current user
user_indexes User's index information
user_ind_columns Table column information corresponding to the user's index
user_cons_columns Table column information corresponding to user constraints
user_clusters All cluster information of the user
user_clu_columns Content information contained in the user's cluster
user_cluster_hash_expressions Hash cluster information
SQL> desc user_tables; name Is it empty? type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) FLASH_CACHE VARCHAR2(7) CELL_FLASH_CACHE VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) COMPRESS_FOR VARCHAR2(12) DROPPED VARCHAR2(3) READ_ONLY VARCHAR2(3) SEGMENT_CREATED VARCHAR2(3) RESULT_CACHE VARCHAR2(7)
one point three Start with v $
v$database database information
v$datafile Data file information
v$controlfile Control file information
v$logfile Redo log information
v$instance Database instance information
v$log Log group information
v$loghist Log history information
v$sga Database SGA information
v$parameter Initialization parameter information
v$process Database server process information
v$bgprocess Database daemon information
v$controlfile_record_section Information of each part recorded in the control document
v$thread Thread information
v$datafile_header The information recorded in the header of the data file
v$archived_log Archived log information
v$archive_dest Setting information of archive log
v$logmnr_contents DML DDL result information of archive log analysis
v$logmnr_dictionary Dictionary file information for log analysis
v$logmnr_logs Log list information for log analysis
v$tablespace Tablespace information
v$tempfile Temporary file information
v$filestat I/O statistics for data files
v$undostat Undo data information
v$rollname Online rollback segment information
v$session Session information
v$transaction Transaction information
v$rollstat Rollback segment statistics
v$pwfile_users Privileged user information
v$sqlarea Resources and related information accessed by the currently queried sql statement
v$sql Basically the same information as v$sqlarea
v$sysstat Database system status information
SQL> desc V$database; name Is it empty? type ----------------------------------------- -------- ---------------------------- DBID NUMBER NAME VARCHAR2(9) CREATED DATE RESETLOGS_CHANGE# NUMBER RESETLOGS_TIME DATE PRIOR_RESETLOGS_CHANGE# NUMBER PRIOR_RESETLOGS_TIME DATE LOG_MODE VARCHAR2(12) CHECKPOINT_CHANGE# NUMBER ARCHIVE_CHANGE# NUMBER CONTROLFILE_TYPE VARCHAR2(7) CONTROLFILE_CREATED DATE CONTROLFILE_SEQUENCE# NUMBER CONTROLFILE_CHANGE# NUMBER CONTROLFILE_TIME DATE OPEN_RESETLOGS VARCHAR2(11) VERSION_TIME DATE OPEN_MODE VARCHAR2(20) PROTECTION_MODE VARCHAR2(20) PROTECTION_LEVEL VARCHAR2(20) REMOTE_ARCHIVE VARCHAR2(8) ACTIVATION# NUMBER SWITCHOVER# NUMBER DATABASE_ROLE VARCHAR2(16) ARCHIVELOG_CHANGE# NUMBER ARCHIVELOG_COMPRESSION VARCHAR2(8) SWITCHOVER_STATUS VARCHAR2(20) DATAGUARD_BROKER VARCHAR2(8) GUARD_STATUS VARCHAR2(7) SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8) SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3) SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3) FORCE_LOGGING VARCHAR2(3) PLATFORM_ID NUMBER PLATFORM_NAME VARCHAR2(101) RECOVERY_TARGET_INCARNATION# NUMBER LAST_OPEN_INCARNATION# NUMBER CURRENT_SCN NUMBER FLASHBACK_ON VARCHAR2(18) SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3) SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3) DB_UNIQUE_NAME VARCHAR2(30) STANDBY_BECAME_PRIMARY_SCN NUMBER FS_FAILOVER_STATUS VARCHAR2(22) FS_FAILOVER_CURRENT_TARGET VARCHAR2(30) FS_FAILOVER_THRESHOLD NUMBER FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7) FS_FAILOVER_OBSERVER_HOST VARCHAR2(512) CONTROLFILE_CONVERTED VARCHAR2(3) PRIMARY_DB_UNIQUE_NAME VARCHAR2(30) SUPPLEMENTAL_LOG_DATA_PL VARCHAR2(3) MIN_REQUIRED_CAPTURE_CHANGE# NUMBER
SQL> desc V$instance; name Is it empty? type ----------------------------------------- -------- ---------------------------- INSTANCE_NUMBER NUMBER INSTANCE_NAME VARCHAR2(16) HOST_NAME VARCHAR2(64) VERSION VARCHAR2(17) STARTUP_TIME DATE STATUS VARCHAR2(12) PARALLEL VARCHAR2(3) THREAD# NUMBER ARCHIVER VARCHAR2(7) LOG_SWITCH_WAIT VARCHAR2(15) LOGINS VARCHAR2(10) SHUTDOWN_PENDING VARCHAR2(3) DATABASE_STATUS VARCHAR2(17) INSTANCE_ROLE VARCHAR2(18) ACTIVE_STATE VARCHAR2(9) BLOCKED VARCHAR2(3)
Get the current instance name:
SQL> select instance_name from V$instance; INSTANCE_NAME ---------------- orcl
1.4 all_ start
all_users Database information for all users
all_objects Information about all objects in the database
all_def_audit_opts All default audit settings information
all_tables All table object information
all_indexes Information of all database object indexes
SQL> desc all_users; name Is it empty? type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER CREATED NOT NULL DATE
Query all users:
SQL> select * from all_users; USERNAME USER_ID CREATED ------------------------------ ---------- -------------- SDE 91 09-10 month-21 BI 90 17-11 month-20 PM 89 17-11 month-20 SH 88 17-11 month-20 IX 87 17-11 month-20 OE 86 17-11 month-20 HR 85 17-11 month-20 SCOTT 84 30-3 month -10 OWBSYS_AUDIT 83 30-3 month -10 OWBSYS 79 30-3 month -10 APEX_030200 78 30-3 month -10 USERNAME USER_ID CREATED ------------------------------ ---------- -------------- APEX_PUBLIC_USER 76 30-3 month -10 FLOWS_FILES 75 30-3 month -10 MGMT_VIEW 74 30-3 month -10 SYSMAN 72 30-3 month -10 SPATIAL_CSW_ADMIN_USR 70 30-3 month -10 SPATIAL_WFS_ADMIN_USR 67 30-3 month -10 MDDATA 65 30-3 month -10 MDSYS 57 30-3 month -10 SI_INFORMTN_SCHEMA 56 30-3 month -10 ORDPLUGINS 55 30-3 month -10 ORDDATA 54 30-3 month -10 USERNAME USER_ID CREATED ------------------------------ ---------- -------------- ORDSYS 53 30-3 month -10 OLAPSYS 61 30-3 month -10 ANONYMOUS 46 30-3 month -10 XDB 45 30-3 month -10 CTXSYS 43 30-3 month -10 EXFSYS 42 30-3 month -10 XS$NULL 2147483638 30-3 month -10 WMSYS 32 30-3 month -10 APPQOSSYS 31 30-3 month -10 DBSNMP 30 30-3 month -10 ORACLE_OCM 21 30-3 month -10 USERNAME USER_ID CREATED ------------------------------ ---------- -------------- DIP 14 30-3 month -10 OUTLN 9 30-3 month -10 SYSTEM 5 30-3 month -10 SYS 0 30-3 month -10 37 rows selected.
1.5 session_ start
session_roles Role information for the session
session_privs Session permission information
1.6 index_ start
index_stats Index setting and storage information
one point seven Pseudo table
dual System pseudo list information