Descs of several table views commonly used in Oracle

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)
 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)
 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_HOST                          VARCHAR2(512)
 CONTROLFILE_CONVERTED                              VARCHAR2(3)
 PRIMARY_DB_UNIQUE_NAME                             VARCHAR2(30)
 SUPPLEMENTAL_LOG_DATA_PL                           VARCHAR2(3)
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;


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

Tags: Database Oracle SQL

Posted on Sat, 09 Oct 2021 03:07:08 -0400 by lettie