「dba_**」ディクショナリービュー

Oracle_4-100

DBAディクショナリービュー

Oracleの基本的な構成情報を保持しているのがDBAビューですが、よく使うものについてまとました。

 

DBAディクショナリービュー一覧

dba_catalog
   すべての索引、表、クラスタ、ビュー、シノニムおよび順序の情報
dba_clusters
   クラスタの情報
dba_constraints
   表の制約定義情報
dba_data_files
   データファイルに関する情報
dba_db_links
   データベースリンクの情報
dba_directories
   ディレクトリ・オブジェクトの情報
dba_extents
   エクステントの領域情報
dba_indexes
   インデックスの情報
dba_jobs
   ジョブに関する情報
dba_objects
   スキーマオブジェクトの情報
dba_profiles
   プロファイル情報
dba_recyclebin
   DROP済みテーブルの情報
dba_roles
   ロールの情報
dba_segments
   セグメントのエクステントの情報
dba_services
   サービスの情報
dba_synonyms
   シノニムの情報
dba_tab_columns
   表、ビューおよびクラスタの列の情報
dba_tables
   テーブルの情報
dba_tablespaces
   表領域に関する情報
dba_temp_files
   一時ファイルに関する情報
dba_triggers
   トリガーの情報
dba_users
   ユーザーに関する情報
dba_views
   ビューの情報

 

dba_catalog

すべての索引、表、クラスタ、ビュー、シノニムおよび順序の情報

select OWNER,TABLE_NAME,TABLE_TYPE from dba_catalog;

SQL> desc dba_catalog
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 TABLE_NAME                         NOT NULL VARCHAR2(30)
 TABLE_TYPE                                  VARCHAR2(11)

SQL>

 

dba_clusters

 クラスタの情報

select OWNER,CLUSTER_NAME,TABLESPACE_NAME,PCT_FREE,PCT_USED,KEY_SIZE,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,AVG_BLOCKS_PER_KEY,CLUSTER_TYPE,FUNCTION,HASHKEYS,DEGREE,INSTANCES,CACHE,BUFFER_POOL,FLASH_CACHE,CELL_FLASH_CACHE,SINGLE_TABLE,DEPENDENCIES from dba_clusters;

SQL> desc dba_clusters
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 CLUSTER_NAME                       NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                    NOT NULL VARCHAR2(30)
 PCT_FREE                                    NUMBER
 PCT_USED                                    NUMBER
 KEY_SIZE                                    NUMBER
 INI_TRANS                          NOT NULL NUMBER
 MAX_TRANS                          NOT NULL NUMBER
 INITIAL_EXTENT                              NUMBER
 NEXT_EXTENT                                 NUMBER
 MIN_EXTENTS                        NOT NULL NUMBER
 MAX_EXTENTS                        NOT NULL NUMBER
 PCT_INCREASE                                NUMBER
 FREELISTS                                   NUMBER
 FREELIST_GROUPS                             NUMBER
 AVG_BLOCKS_PER_KEY                          NUMBER
 CLUSTER_TYPE                                VARCHAR2(5)
 FUNCTION                                    VARCHAR2(15)
 HASHKEYS                                    NUMBER
 DEGREE                                      VARCHAR2(10)
 INSTANCES                                   VARCHAR2(10)
 CACHE                                       VARCHAR2(5)
 BUFFER_POOL                                 VARCHAR2(7)
 FLASH_CACHE                                 VARCHAR2(7)
 CELL_FLASH_CACHE                            VARCHAR2(7)
 SINGLE_TABLE                                VARCHAR2(5)
 DEPENDENCIES                                VARCHAR2(8)

SQL>

 

dba_constraints

表の制約定義情報

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION,R_OWNER,R_CONSTRAINT_NAME,DELETE_RULE,STATUS,DEFERRABLE,DEFERRED,VALIDATED,GENERATED,BAD,RELY,LAST_CHANGE,INDEX_OWNER,INDEX_NAME,INVALID,VIEW_RELATED from dba_constraints;

SQL> desc dba_constraints
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                                       VARCHAR2(30)
 CONSTRAINT_NAME                    NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                             VARCHAR2(1)
 TABLE_NAME                         NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                            LONG
 R_OWNER                                     VARCHAR2(30)
 R_CONSTRAINT_NAME                           VARCHAR2(30)
 DELETE_RULE                                 VARCHAR2(9)
 STATUS                                      VARCHAR2(8)
 DEFERRABLE                                  VARCHAR2(14)
 DEFERRED                                    VARCHAR2(9)
 VALIDATED                                   VARCHAR2(13)
 GENERATED                                   VARCHAR2(14)
 BAD                                         VARCHAR2(3)
 RELY                                        VARCHAR2(4)
 LAST_CHANGE                                 DATE
 INDEX_OWNER                                 VARCHAR2(30)
 INDEX_NAME                                  VARCHAR2(30)
 INVALID                                     VARCHAR2(7)
 VIEW_RELATED                                VARCHAR2(14)

SQL>

 

dba_data_files

データファイルに関する情報

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES,BLOCKS,STATUS,RELATIVE_FNO,AUTOEXTENSIBLE,MAXBYTES,MAXBLOCKS,INCREMENT_BY,USER_BYTES,USER_BLOCKS,ONLINE_STATUS from dba_data_files;

SQL> desc dba_data_files
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 FILE_NAME                                   VARCHAR2(257)
 FILE_ID                                     NUMBER
 TABLESPACE_NAME                             VARCHAR2(30)
 BYTES                                       NUMBER
 BLOCKS                                      NUMBER
 STATUS                                      VARCHAR2(9)
 RELATIVE_FNO                                NUMBER
 AUTOEXTENSIBLE                              VARCHAR2(3)
 MAXBYTES                                    NUMBER
 MAXBLOCKS                                   NUMBER
 INCREMENT_BY                                NUMBER
 USER_BYTES                                  NUMBER
 USER_BLOCKS                                 NUMBER
 ONLINE_STATUS                               VARCHAR2(7)

SQL>

 

データベースリンクの情報

select OWNER,DB_LINK,USERNAME,HOST,CREATED from dba_db_links;

SQL> desc dba_db_links
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 DB_LINK                            NOT NULL VARCHAR2(128)
 USERNAME                                    VARCHAR2(30)
 HOST                                        VARCHAR2(2000)
 CREATED                            NOT NULL DATE

SQL>

 

dba_directories

ディレクトリ・オブジェクトの情報

select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

SQL> desc dba_directories
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                     NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                              VARCHAR2(4000)

SQL>

 

dba_extents

エクステントの領域情報

select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS,RELATIVE_FNO from  dba_extents;

SQL> desc dba_extents
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                                       VARCHAR2(30)
 SEGMENT_NAME                                VARCHAR2(81)
 PARTITION_NAME                              VARCHAR2(30)
 SEGMENT_TYPE                                VARCHAR2(18)
 TABLESPACE_NAME                             VARCHAR2(30)
 EXTENT_ID                                   NUMBER
 FILE_ID                                     NUMBER
 BLOCK_ID                                    NUMBER
 BYTES                                       NUMBER
 BLOCKS                                      NUMBER
 RELATIVE_FNO                                NUMBER

SQL>

 

dba_indexes

 インデックスの情報

select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS,COMPRESSION,PREFIX_LENGTH,TABLESPACE_NAME,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,PCT_THRESHOLD,INCLUDE_COLUMN,FREELISTS,FREELIST_GROUPS,PCT_FREE,LOGGING,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED,DEGREE,INSTANCES,PARTITIONED,TEMPORARY,GENERATED,SECONDARY,BUFFER_POOL,FLASH_CACHE,CELL_FLASH_CACHE,USER_STATS,DURATION,PCT_DIRECT_ACCESS,ITYP_OWNER,ITYP_NAME,PARAMETERS,GLOBAL_STATS,DOMIDX_STATUS,DOMIDX_OPSTATUS,FUNCIDX_STATUS,JOIN_INDEX,IOT_REDUNDANT_PKEY_ELIM,DROPPED,VISIBILITY,DOMIDX_MANAGEMENT,SEGMENT_CREATED from dba_indexes;

SQL> desc dba_indexes
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 INDEX_NAME                         NOT NULL VARCHAR2(30)
 INDEX_TYPE                                  VARCHAR2(27)
 TABLE_OWNER                        NOT NULL VARCHAR2(30)
 TABLE_NAME                         NOT NULL VARCHAR2(30)
 TABLE_TYPE                                  VARCHAR2(11)
 UNIQUENESS                                  VARCHAR2(9)
 COMPRESSION                                 VARCHAR2(8)
 PREFIX_LENGTH                               NUMBER
 TABLESPACE_NAME                             VARCHAR2(30)
 INI_TRANS                                   NUMBER
 MAX_TRANS                                   NUMBER
 INITIAL_EXTENT                              NUMBER
 NEXT_EXTENT                                 NUMBER
 MIN_EXTENTS                                 NUMBER
 MAX_EXTENTS                                 NUMBER
 PCT_INCREASE                                NUMBER
 PCT_THRESHOLD                               NUMBER
 INCLUDE_COLUMN                              NUMBER
 FREELISTS                                   NUMBER
 FREELIST_GROUPS                             NUMBER
 PCT_FREE                                    NUMBER
 LOGGING                                     VARCHAR2(3)
 BLEVEL                                      NUMBER
 LEAF_BLOCKS                                 NUMBER
 DISTINCT_KEYS                               NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                     NUMBER
 AVG_DATA_BLOCKS_PER_KEY                     NUMBER
 CLUSTERING_FACTOR                           NUMBER
 STATUS                                      VARCHAR2(8)
 NUM_ROWS                                    NUMBER
 SAMPLE_SIZE                                 NUMBER
 LAST_ANALYZED                               DATE
 DEGREE                                      VARCHAR2(40)
 INSTANCES                                   VARCHAR2(40)
 PARTITIONED                                 VARCHAR2(3)
 TEMPORARY                                   VARCHAR2(1)
 GENERATED                                   VARCHAR2(1)
 SECONDARY                                   VARCHAR2(1)
 BUFFER_POOL                                 VARCHAR2(7)
 FLASH_CACHE                                 VARCHAR2(7)
 CELL_FLASH_CACHE                            VARCHAR2(7)
 USER_STATS                                  VARCHAR2(3)
 DURATION                                    VARCHAR2(15)
 PCT_DIRECT_ACCESS                           NUMBER
 ITYP_OWNER                                  VARCHAR2(30)
 ITYP_NAME                                   VARCHAR2(30)
 PARAMETERS                                  VARCHAR2(1000)
 GLOBAL_STATS                                VARCHAR2(3)
 DOMIDX_STATUS                               VARCHAR2(12)
 DOMIDX_OPSTATUS                             VARCHAR2(6)
 FUNCIDX_STATUS                              VARCHAR2(8)
 JOIN_INDEX                                  VARCHAR2(3)
 IOT_REDUNDANT_PKEY_ELIM                     VARCHAR2(3)
 DROPPED                                     VARCHAR2(3)
 VISIBILITY                                  VARCHAR2(9)
 DOMIDX_MANAGEMENT                           VARCHAR2(14)
 SEGMENT_CREATED                             VARCHAR2(3)

SQL>

 

dba_jobs

ジョブに関する情報

select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN,INTERVAL,FAILURES,WHAT,NLS_ENV,MISC_ENV,INSTANCE from dba_jobs;

SQL> desc dba_jobs
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 JOB                                NOT NULL NUMBER
 LOG_USER                           NOT NULL VARCHAR2(30)
 PRIV_USER                          NOT NULL VARCHAR2(30)
 SCHEMA_USER                        NOT NULL VARCHAR2(30)
 LAST_DATE                                   DATE
 LAST_SEC                                    VARCHAR2(8)
 THIS_DATE                                   DATE
 THIS_SEC                                    VARCHAR2(8)
 NEXT_DATE                          NOT NULL DATE
 NEXT_SEC                                    VARCHAR2(8)
 TOTAL_TIME                                  NUMBER
 BROKEN                                      VARCHAR2(1)
 INTERVAL                           NOT NULL VARCHAR2(200)
 FAILURES                                    NUMBER
 WHAT                                        VARCHAR2(4000)
 NLS_ENV                                     VARCHAR2(4000)
 MISC_ENV                                    RAW(32)
 INSTANCE                                    NUMBER

SQL>

 

dba_objects

スキーマオブジェクトの情報

select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME from dba_objects;

SQL> desc dba_objects
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                 VARCHAR2(128)
 SUBOBJECT_NAME                              VARCHAR2(30)
 OBJECT_ID                                   NUMBER
 DATA_OBJECT_ID                              NUMBER
 OBJECT_TYPE                                 VARCHAR2(19)
 CREATED                                     DATE
 LAST_DDL_TIME                               DATE
 TIMESTAMP                                   VARCHAR2(19)
 STATUS                                      VARCHAR2(7)
 TEMPORARY                                   VARCHAR2(1)
 GENERATED                                   VARCHAR2(1)
 SECONDARY                                   VARCHAR2(1)
 NAMESPACE                                   NUMBER
 EDITION_NAME                                VARCHAR2(30)

SQL>

 

dba_profiles

プロファイル情報

select PROFILE,RESOURCE_NAME,RESOURCE_TYPE,LIMIT from dba_profiles;

SQL> desc dba_profiles
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 PROFILE                            NOT NULL VARCHAR2(30)
 RESOURCE_NAME                      NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                               VARCHAR2(8)
 LIMIT                                       VARCHAR2(40)

SQL>

 

dba_recyclebin

 DROP済みテーブルの情報

select OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,TS_NAME,CREATETIME,DROPTIME,DROPSCN,PARTITION_NAME,CAN_UNDROP,CAN_PURGE,RELATED,BASE_OBJECT,PURGE_OBJECT,SPACE from dba_recyclebin;

SQL> desc dba_recyclebin
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 OBJECT_NAME                        NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                               VARCHAR2(32)
 OPERATION                                   VARCHAR2(9)
 TYPE                                        VARCHAR2(25)
 TS_NAME                                     VARCHAR2(30)
 CREATETIME                                  VARCHAR2(19)
 DROPTIME                                    VARCHAR2(19)
 DROPSCN                                     NUMBER
 PARTITION_NAME                              VARCHAR2(32)
 CAN_UNDROP                                  VARCHAR2(3)
 CAN_PURGE                                   VARCHAR2(3)
 RELATED                            NOT NULL NUMBER
 BASE_OBJECT                        NOT NULL NUMBER
 PURGE_OBJECT                       NOT NULL NUMBER
 SPACE                                       NUMBER

SQL>

 

dba_roles

ロールの情報

select ROLE,PASSWORD_REQUIRED,AUTHENTICATION_TYPE from dba_roles;

SQL> desc dba_roles
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 ROLE                               NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                           VARCHAR2(8)
 AUTHENTICATION_TYPE                         VARCHAR2(11)

SQL>

 

dba_segments

セグメントのエクステントの情報

select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,MAX_SIZE,RETENTION,MINRETENTION,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,RELATIVE_FNO,BUFFER_POOL,FLASH_CACHE,CELL_FLASH_CACHE from dba_segments;

SQL> desc dba_segments
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                                       VARCHAR2(30)
 SEGMENT_NAME                                VARCHAR2(81)
 PARTITION_NAME                              VARCHAR2(30)
 SEGMENT_TYPE                                VARCHAR2(18)
 SEGMENT_SUBTYPE                             VARCHAR2(10)
 TABLESPACE_NAME                             VARCHAR2(30)
 HEADER_FILE                                 NUMBER
 HEADER_BLOCK                                NUMBER
 BYTES                                       NUMBER
 BLOCKS                                      NUMBER
 EXTENTS                                     NUMBER
 INITIAL_EXTENT                              NUMBER
 NEXT_EXTENT                                 NUMBER
 MIN_EXTENTS                                 NUMBER
 MAX_EXTENTS                                 NUMBER
 MAX_SIZE                                    NUMBER
 RETENTION                                   VARCHAR2(7)
 MINRETENTION                                NUMBER
 PCT_INCREASE                                NUMBER
 FREELISTS                                   NUMBER
 FREELIST_GROUPS                             NUMBER
 RELATIVE_FNO                                NUMBER
 BUFFER_POOL                                 VARCHAR2(7)
 FLASH_CACHE                                 VARCHAR2(7)
 CELL_FLASH_CACHE                            VARCHAR2(7)

SQL>

 

dba_services

サービスの情報

select SERVICE_ID,NAME,NAME_HASH,NETWORK_NAME,CREATION_DATE,CREATION_DATE_HASH,FAILOVER_METHOD,FAILOVER_TYPE,FAILOVER_RETRIES,FAILOVER_DELAY,MIN_CARDINALITY,MAX_CARDINALITY,GOAL,DTP,ENABLED,AQ_HA_NOTIFICATIONS,CLB_GOAL,EDITION from dba_services;

SQL> desc dba_services
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 SERVICE_ID                                  NUMBER
 NAME                                        VARCHAR2(64)
 NAME_HASH                                   NUMBER
 NETWORK_NAME                                VARCHAR2(512)
 CREATION_DATE                               DATE
 CREATION_DATE_HASH                          NUMBER
 FAILOVER_METHOD                             VARCHAR2(64)
 FAILOVER_TYPE                               VARCHAR2(64)
 FAILOVER_RETRIES                            NUMBER(10)
 FAILOVER_DELAY                              NUMBER(10)
 MIN_CARDINALITY                             NUMBER
 MAX_CARDINALITY                             NUMBER
 GOAL                                        VARCHAR2(12)
 DTP                                         VARCHAR2(1)
 ENABLED                                     VARCHAR2(3)
 AQ_HA_NOTIFICATIONS                         VARCHAR2(3)
 CLB_GOAL                                    VARCHAR2(5)
 EDITION                                     VARCHAR2(30)

SQL>

 

dba_synonyms

シノニムの情報

select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME,DB_LINK from dba_synonyms;

SQL> desc dba_synonyms
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 SYNONYM_NAME                       NOT NULL VARCHAR2(30)
 TABLE_OWNER                                 VARCHAR2(30)
 TABLE_NAME                         NOT NULL VARCHAR2(30)
 DB_LINK                                     VARCHAR2(128)

SQL>

 

dba_tab_columns

表、ビューおよびクラスタの列の情報

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID,DEFAULT_LENGTH,DATA_DEFAULT,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH,GLOBAL_STATS,USER_STATS,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED,V80_FMT_IMAGE,DATA_UPGRADED,HISTOGRAM from dba_tab_columns;

SQL> desc dba_tab_columns
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 TABLE_NAME                         NOT NULL VARCHAR2(30)
 COLUMN_NAME                        NOT NULL VARCHAR2(30)
 DATA_TYPE                                   VARCHAR2(106)
 DATA_TYPE_MOD                               VARCHAR2(3)
 DATA_TYPE_OWNER                             VARCHAR2(30)
 DATA_LENGTH                        NOT NULL NUMBER
 DATA_PRECISION                              NUMBER
 DATA_SCALE                                  NUMBER
 NULLABLE                                    VARCHAR2(1)
 COLUMN_ID                                   NUMBER
 DEFAULT_LENGTH                              NUMBER
 DATA_DEFAULT                                LONG
 NUM_DISTINCT                                NUMBER
 LOW_VALUE                                   RAW(32)
 HIGH_VALUE                                  RAW(32)
 DENSITY                                     NUMBER
 NUM_NULLS                                   NUMBER
 NUM_BUCKETS                                 NUMBER
 LAST_ANALYZED                               DATE
 SAMPLE_SIZE                                 NUMBER
 CHARACTER_SET_NAME                          VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                        NUMBER
 GLOBAL_STATS                                VARCHAR2(3)
 USER_STATS                                  VARCHAR2(3)
 AVG_COL_LEN                                 NUMBER
 CHAR_LENGTH                                 NUMBER
 CHAR_USED                                   VARCHAR2(1)
 V80_FMT_IMAGE                               VARCHAR2(3)
 DATA_UPGRADED                               VARCHAR2(3)
 HISTOGRAM                                   VARCHAR2(15)

SQL>

 

dba_tables

テーブルの情報

select OWNER,TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED,IOT_TYPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,FLASH_CACHE,CELL_FLASH_CACHE,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATION,SKIP_CORRUPT,MONITORING,CLUSTER_OWNER,DEPENDENCIES,COMPRESSION,COMPRESS_FOR,DROPPED,READ_ONLY,SEGMENT_CREATED,RESULT_CACHE from dba_tables;

SQL> desc dba_tables
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 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)

SQL>

 

dba_tablespaces

表領域に関する情報

select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,MAX_SIZE,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,FORCE_LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE,PLUGGED_IN,SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,RETENTION,BIGFILE,PREDICATE_EVALUATION,ENCRYPTED,COMPRESS_FOR from dba_tablespaces;

SQL> desc dba_tablespaces
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 TABLESPACE_NAME                    NOT NULL VARCHAR2(30)
 BLOCK_SIZE                         NOT NULL NUMBER
 INITIAL_EXTENT                              NUMBER
 NEXT_EXTENT                                 NUMBER
 MIN_EXTENTS                        NOT NULL NUMBER
 MAX_EXTENTS                                 NUMBER
 MAX_SIZE                                    NUMBER
 PCT_INCREASE                                NUMBER
 MIN_EXTLEN                                  NUMBER
 STATUS                                      VARCHAR2(9)
 CONTENTS                                    VARCHAR2(9)
 LOGGING                                     VARCHAR2(9)
 FORCE_LOGGING                               VARCHAR2(3)
 EXTENT_MANAGEMENT                           VARCHAR2(10)
 ALLOCATION_TYPE                             VARCHAR2(9)
 PLUGGED_IN                                  VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                    VARCHAR2(6)
 DEF_TAB_COMPRESSION                         VARCHAR2(8)
 RETENTION                                   VARCHAR2(11)
 BIGFILE                                     VARCHAR2(3)
 PREDICATE_EVALUATION                        VARCHAR2(7)
 ENCRYPTED                                   VARCHAR2(3)
 COMPRESS_FOR                                VARCHAR2(12)

SQL>

 

dba_temp_files

一時ファイルに関する情報

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES,BLOCKS,STATUS,RELATIVE_FNO,AUTOEXTENSIBLE,MAXBYTES,MAXBLOCKS,INCREMENT_BY,USER_BYTES,USER_BLOCKS from dba_temp_files;

SQL> desc dba_temp_files
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 FILE_NAME                                   VARCHAR2(257)
 FILE_ID                                     NUMBER
 TABLESPACE_NAME                    NOT NULL VARCHAR2(30)
 BYTES                                       NUMBER
 BLOCKS                                      NUMBER
 STATUS                                      VARCHAR2(7)
 RELATIVE_FNO                                NUMBER
 AUTOEXTENSIBLE                              VARCHAR2(3)
 MAXBYTES                                    NUMBER
 MAXBLOCKS                                   NUMBER
 INCREMENT_BY                                NUMBER
 USER_BYTES                                  NUMBER
 USER_BLOCKS                                 NUMBER

SQL>

 

dba_triggers

トリガーの情報

select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,BASE_OBJECT_TYPE,TABLE_NAME,COLUMN_NAME,REFERENCING_NAMES,WHEN_CLAUSE,STATUS,DESCRIPTION,ACTION_TYPE,TRIGGER_BODY,CROSSEDITION,BEFORE_STATEMENT,BEFORE_ROW,AFTER_ROW,AFTER_STATEMENT,INSTEAD_OF_ROW,FIRE_ONCE,APPLY_SERVER_ONLY from dba_triggers;

SQL> desc dba_triggers
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                                       VARCHAR2(30)
 TRIGGER_NAME                                VARCHAR2(30)
 TRIGGER_TYPE                                VARCHAR2(16)
 TRIGGERING_EVENT                            VARCHAR2(227)
 TABLE_OWNER                                 VARCHAR2(30)
 BASE_OBJECT_TYPE                            VARCHAR2(16)
 TABLE_NAME                                  VARCHAR2(30)
 COLUMN_NAME                                 VARCHAR2(4000)
 REFERENCING_NAMES                           VARCHAR2(128)
 WHEN_CLAUSE                                 VARCHAR2(4000)
 STATUS                                      VARCHAR2(8)
 DESCRIPTION                                 VARCHAR2(4000)
 ACTION_TYPE                                 VARCHAR2(11)
 TRIGGER_BODY                                LONG
 CROSSEDITION                                VARCHAR2(7)
 BEFORE_STATEMENT                            VARCHAR2(3)
 BEFORE_ROW                                  VARCHAR2(3)
 AFTER_ROW                                   VARCHAR2(3)
 AFTER_STATEMENT                             VARCHAR2(3)
 INSTEAD_OF_ROW                              VARCHAR2(3)
 FIRE_ONCE                                   VARCHAR2(3)
 APPLY_SERVER_ONLY                           VARCHAR2(3)

SQL>

 

dba_users

ユーザーに関する情報

select USERNAME,USER_ID,PASSWORD,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,PROFILE,INITIAL_RSRC_CONSUMER_GROUP,EXTERNAL_NAME,PASSWORD_VERSIONS,EDITIONS_ENABLED,AUTHENTICATION_TYPE from dba_users;

SQL> desc dba_users
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 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)

SQL>

 

dba_views

 ビューの情報

select OWNER,VIEW_NAME,TEXT_LENGTH,TEXT,TYPE_TEXT_LENGTH,TYPE_TEXT,OID_TEXT_LENGTH,OID_TEXT,VIEW_TYPE_OWNER,VIEW_TYPE,SUPERVIEW_NAME,EDITIONING_VIEW,READ_ONLY from dba_views;

SQL> desc dba_views
 名前                               NULL?    型
 ---------------------------------- -------- ------------------------------------
 OWNER                              NOT NULL VARCHAR2(30)
 VIEW_NAME                          NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                 NUMBER
 TEXT                                        LONG
 TYPE_TEXT_LENGTH                            NUMBER
 TYPE_TEXT                                   VARCHAR2(4000)
 OID_TEXT_LENGTH                             NUMBER
 OID_TEXT                                    VARCHAR2(4000)
 VIEW_TYPE_OWNER                             VARCHAR2(30)
 VIEW_TYPE                                   VARCHAR2(30)
 SUPERVIEW_NAME                              VARCHAR2(30)
 EDITIONING_VIEW                             VARCHAR2(1)
 READ_ONLY                                   VARCHAR2(1)

SQL>

 

 

 

 


サブコンテンツ

このページの先頭へ