数据字典-白红宇

数据字典

SYS@newtest>alter pluggable database clonepdb_plug open;

插接式数据库已变更。

SYS@newtest>show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

2 PDB$SEED                       READ ONLY  NO     3 PDBTEST                        MOUNTED     4 CLONEPDB_PLUG                  READ WRITE NO

SYS@newtest>grant select on x$ksppi to scott;

grant select on x$ksppi to scott

第 1 行出现错误:
ORA-02030: 只能从固定的表/视图查询
C:\Users\Administrator>oerr ora 02030
02030, 00000, "can only select from fixed tables/views"
// Cause: An attempt is being made to perform an operation other than

// a retrieval from a fixed table/view.

// Action: You may only select rows from fixed tables/views.
SYS@newtest>set autotrace trace explain
SYS@newtest>select from v$parameter;

Execution Plan

Plan hash value: 1165067939

)| Time |

| 0 | SELECT STATEMENT | | 13 | 1625 | 1 (100

)| 00:00:01 |

| 1 | NESTED LOOPS | | 13 | 1625 | 1 (100

)| 00:00:01 |

|* 2 | FIXED TABLE FULL | X$KSPPI | 242 | 22506 | 1 (100

)| 00:00:01 |

|* 3 | FIXED TABLE FIXED INDEX| X$KSPPCV (ind:1) | 1 | 32 | 0 (0

)| 00:00:01 |

Predicate Information (identified by operation id):

2 - filter(TRANSLATE("KSPPINM",'_','$') NOT LIKE '$$%' AND

"X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)

=0)

3 - filter("X"."INDX"="Y"."INDX" AND (TRANSLATE("KSPPINM",'_','$') NOT LIKE '

$%'

OR "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0))

数据字典

SYS@newtest>select * from v$version where rownum<2;

BANNER

CON_ID

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS@newtest>select * from v$version where rownum<2;

BANNER

CON_ID

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS@newtest>select kvittag,kvitval,kvitdsc from x$kvit;

KVITTAG KVITVAL

KVITDSC

ksbcpurawthrcnt 1

number of raw CPU threads in the system used by Oracle

ksbcpueffthrcnt 1

number of effective CPU threads in the system used by Oracle

ksbcpucore 1

number of physical CPU cores in the system used by Oracle

KVITTAG KVITVAL

KVITDSC

ksbcpusocket 1

number of physical CPU sockets in the system used by Oracle

ksbcpu_hwm 1

high water mark of number of CPUs used by Oracle

ksbcpucore_hwm 1

high water mark of number of CPU cores on system

KVITTAG KVITVAL

KVITDSC

ksbcpusocket_hwm 1

high water mark of number of CPU sockets on system

ksbcpu_actual 1

number of available CPUs in the system

ksbcpu_dr 1

CPU dynamic reconfiguration supported

KVITTAG KVITVAL

KVITDSC

kcbncbh 235080

number of buffers in cdb

kcbnbh 235080

number of buffers

kcbldq 25

large dirty queue if kcbclw reaches this

KVITTAG KVITVAL

KVITDSC

kcbfsp 40

Max percentage of LRU list foreground can scan for free

kcbcln 2

Initial percentage of LRU list to keep clean

kcbnbf 3000

number buffer objects

KVITTAG KVITVAL

KVITDSC

kcbwst 0

Flag that indicates recovery or db suspension

kcteln 0

Error Log Number for thread open

kcvgcw 0

SGA: opcode for checkpoint cross-instance call

KVITTAG KVITVAL

KVITDSC

kcvgcw 0

SGA:opcode for pq checkpoint cross-instance call

19 rows selected.

12C

rem skotsovo 05/05/95 - update according to release 1

rem jwijaya 04/28/95 - fix comments

rem varora 04/28/95 - rename col#,usercol#,cols,usercols

rem tcheng 03/21/95 - add col# to adtcol$ and ntab$

rem varora 01/27/95 - add table for nested table support

rem skotsovo 01/25/95 - bring normalized type tables up to date

rem skotsovo 01/23/95 - move exceptions from method to method_body

rem jwijaya 01/04/95 - add system privileges for type

rem jwijaya 12/29/94 - making type$ work (temporarily allow 'version'

rem 'checks' columns nullable and mark 'checks'

rem and 'default$' not-supported (N/S))

rem skrishna 12/06/94 - create extent table of pre-defined types

rem varora 12/01/94 - change toid in adtcol$ to type number

rem anori 11/17/94 - ADT support tables and columns

rem

rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

rem Whenever new column is created to store internal, user or kernel column

rem number, be sure to update the structure adtDT in atb.c so that those

rem columns will be updated properly during drop column.

rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

rem

dcore.bsq

dsqlddl.bsq

dmanage.bsq

dplsql.bsq

dtxnspc.bsq

dfmap.bsq

denv.bsq

drac.bsq

dsec.bsq

doptim.bsq

dobj.bsq

djava.bsq

dpart.bsq

drep.bsq

daw.bsq

dsummgt.bsq

dtools.bsq

dexttab.bsq

ddm.bsq

dlmnr.bsq

ddst.bsq

dfba.bsq

dpstdy.bsq

drupg.bsq

dtlog.bsq

dmisc.bsq

dhcs.bsq

dcore.bsq文件中

REM NOTE

REM Logminer/Streams uses contents of this table.

REM Please do not reuse any flags without verifying the impact of your

REM changes on inter-op.

create table obj$ / object table /

( obj# number not null, / object number /

dataobj# number, / data layer object number /

owner# number not null, / owner user number /

name varchar2("M_IDEN") not null, / object name /

namespace number not null, / namespace of object (see KQD.H): /

/ 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, /

/ 8 = LOB, 9 = DIRECTORY, /

/ 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, /

/ 13 = JAVA SOURCE, 14 = JAVA RESOURCE /

/ 58 = (Data Mining) MODEL /

subname varchar2("M_IDEN"), / subordinate to the name /

type# number not null, / object type (see KQD.H): /

/ 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, /

/ 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, /

/ 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, /

/ 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, /

/ 23 = DIRECTORY , 24 = QUEUE, /

/ 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, /

/ 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, /

/ 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, /

/ 35 = INDEX SUBPARTITION /

/ 82 = (Data Mining) MODEL /

/ 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE /

/ 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS /

ctime date not null, / object creation time /

mtime date not null, / DDL modification time /

stime date not null, / specification timestamp (version) /

status number not null, / status of object (see KQD.H): /

/ 1 = VALID/AUTHORIZED WITHOUT ERRORS, /

/ 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, /

/ 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, /

/ 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED /

remoteowner varchar2("M_IDEN"), / remote owner name (remote object) /

linkname varchar2("M_XDBI"), / link name (remote object) /

flags number, / 0x01 = extent map checking required /

/ 0x02 = temporary object /

/ 0x04 = system generated object /

/ 0x08 = unbound (invoker's rights) /

/ 0x10 = secondary object /

/ 0x20 = in-memory temp table /

/ 0x80 = dropped table (RecycleBin) /

/ 0x100 = synonym VPD policies /

/ 0x200 = synonym VPD groups /

/ 0x400 = synonym VPD context /

/ 0x4000 = nested table partition /

oid$ raw(16), / OID for typed table, typed view, and type /

spare1 number, / sql version flag: see kpul.h /

spare2 number, / object version number /

spare3 number, / base user# /

spare4 varchar2(1000),

spare5 varchar2(1000),

spare6 date,

signature raw(16), / object signature hash value /

spare7 number, / future use /

spare8 number,

spare9 number,

dflcollid number, / unit-level default collation id /

creappid number, / App ID of Application that created object /

creverid number, / Version of Application that created object /

crepatchid number, / Patch ID of Application that created object /

modappid number, / App ID of Application that last modified object /

modverid number, / Version of Application that last modified object /

modpatchid number, / Patch ID of Application that last modified object /

spare10 number,

spare11 number,

spare12 varchar2(1000),

spare13 varchar2(1000),

spare14 timestamp

)

storage (initial 10k next 100k maxextents unlimited pctincrease 0)

SYS@newtest>desc dba_objects

Name Null? Type

OWNER VARCHAR2(128)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(128)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(23)

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(128)

SHARING VARCHAR2(18)

EDITIONABLE VARCHAR2(1)

ORACLE_MAINTAINED VARCHAR2(1)

APPLICATION VARCHAR2(1)

DEFAULT_COLLATION VARCHAR2(100)

DUPLICATED VARCHAR2(1)

SHARDED VARCHAR2(1)

CREATED_APPID NUMBER

CREATED_VSNID NUMBER

MODIFIED_APPID NUMBER

MODIFIED_VSNID NUMBER

obj# number not null, / object number /

dataobj# number, / data layer object number /

SYS@newtest>create table test as select * from dba_users;

Table created.

SYS@newtest>select object_id,data_object_ID from dba_objects

2 where owner='SYS' and object_name='TEST';

OBJECT_ID DATA_OBJECT_ID

81840          81840

SYS@newtest>TRUNCATE TABLE TEST;

Table truncated.

SYS@newtest>select object_id,data_object_ID from dba_objects

2 where owner='SYS' and object_name='TEST';

OBJECT_ID DATA_OBJECT_ID

81840          81841

数据字典

SYS@newtest>set long 12000

SYS@newtest>set pagesize 99

SYS@newtest>select dbms_metadata.get_ddl('TABLE','TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST')

CREATE TABLE "SYS"."TEST"

( "USERNAME" VARCHAR2(128) NOT NULL ENABLE,

"USER_ID" NUMBER NOT NULL ENABLE,

"PASSWORD" VARCHAR2(4000),

"ACCOUNT_STATUS" VARCHAR2(32) NOT NULL ENABLE,

"LOCK_DATE" DATE,

"EXPIRY_DATE" DATE,

"DEFAULT_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,

"TEMPORARY_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,

"LOCAL_TEMP_TABLESPACE" VARCHAR2(30),

"CREATED" DATE NOT NULL ENABLE,

"PROFILE" VARCHAR2(128) NOT NULL ENABLE,

"INITIAL_RSRC_CONSUMER_GROUP" VARCHAR2(128),

"EXTERNAL_NAME" VARCHAR2(4000),

"PASSWORD_VERSIONS" VARCHAR2(17),

"EDITIONS_ENABLED" VARCHAR2(1),

"AUTHENTICATION_TYPE" VARCHAR2(8),

"PROXY_ONLY_CONNECT" VARCHAR2(1),

"COMMON" VARCHAR2(3),

"LAST_LOGIN" TIMESTAMP (9) WITH TIME ZONE,

"ORACLE_MAINTAINED" VARCHAR2(1),

"INHERITED" VARCHAR2(3),

"DEFAULT_COLLATION" VARCHAR2(100),

"IMPLICIT" VARCHAR2(3),

"ALL_SHARD" VARCHAR2(3)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM"

SYS@newtest>conn scott/tiger@clonepdb_plug

Connected.

SCOTT@clonepdb_plug>col table_name format A20;

SCOTT@clonepdb_plug>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

DEPT USERS

EMP USERS

BONUS USERS

SALGRADE USERS

TEST USERS

TEST1 USERS

TEST2 USERS

7 rows selected.

SCOTT@clonepdb_plug>col table_name format A40;

SCOTT@clonepdb_plug>select table_name,tablespace_name from all_tables;

TABLE_NAME TABLESPACE_NAME

DUAL SYSTEM

SYSTEM_PRIVILEGE_MAP SYSTEM

TABLE_PRIVILEGE_MAP SYSTEM

USER_PRIVILEGE_MAP SYSTEM

STMT_AUDIT_OPTION_MAP SYSTEM

AUDIT_ACTIONS SYSTEM

WRR$_REPLAY_CALL_FILTER SYSAUX

KU$_PLSQL_SRC_TBL SYSTEM

HS_BULKLOAD_VIEW_OBJ SYSTEM

HS$_PARALLEL_METADATA SYSTEM

HS_PARTITION_COL_NAME SYSTEM

HS_PARTITION_COL_TYPE SYSTEM

XDB$IMPORT_TT_INFO SYSAUX

XDB$IMPORT_QN_INFO SYSAUX

XDB$IMPORT_NM_INFO SYSAUX

XDB$IMPORT_PT_INFO SYSAUX

XDB_INDEX_DDL_CACHE SYSAUX

HELP SYSTEM

DR$OBJECT_ATTRIBUTE SYSAUX

DR$POLICY_TAB SYSAUX

DR$THS SYSAUX

DR$THS_PHRASE SYSAUX

DR$NUMBER_SEQUENCE SYSAUX

SDO_INDEX_HISTOGRAM_TABLE SYSAUX

OGIS_SPATIAL_REFERENCE_SYSTEMS SYSAUX

OGIS_GEOMETRY_COLUMNS SYSAUX

AW$EXPRESS SYSAUX

AW$AWMD SYSAUX

AW$AWCREATE SYSAUX

AW$AWCREATE10G SYSAUX

AW$AWXML SYSAUX

AW$AWREPORT SYSAUX

SDO_CS_SRS SYSAUX

NTV2_XML_DATA SYSAUX

SRSNAMESPACE_TABLE SYSAUX

SDO_UNITS_OF_MEASURE SYSAUX

SDO_PRIME_MERIDIANS SYSAUX

SDO_ELLIPSOIDS SYSAUX

SDO_DATUMS SYSAUX

SDO_COORD_SYS SYSAUX

SDO_COORD_AXIS_NAMES SYSAUX

SDO_COORD_AXES SYSAUX

SDO_COORD_REF_SYS SYSAUX

SDO_COORD_OP_METHODS SYSAUX

SDO_COORD_OPS SYSAUX

SDO_PREFERRED_OPS_SYSTEM SYSAUX

SDO_PREFERRED_OPS_USER SYSAUX

SDO_COORD_OP_PATHS SYSAUX

SDO_COORD_OP_PARAMS SYSAUX

SDO_COORD_OP_PARAM_USE SYSAUX

SDO_COORD_OP_PARAM_VALS SYSAUX

SDO_SRIDS_BY_URN SYSAUX

SDO_SRIDS_BY_URN_PATTERN SYSAUX

SDO_CRS_GEOGRAPHIC_PLUS_HEIGHT SYSAUX

SDO_PROJECTIONS_OLD_SNAPSHOT SYSAUX

SDO_ELLIPSOIDS_OLD_SNAPSHOT SYSAUX

SDO_DATUMS_OLD_SNAPSHOT SYSAUX

SDO_FEATURE_USAGE SYSAUX

SDO_WS_CONFERENCE SYSAUX

SDO_WS_CONFERENCE_RESULTS SYSAUX

SDO_WS_CONFERENCE_PARTICIPANTS SYSAUX

SDO_XML_SCHEMAS SYSAUX

SDO_GEOR_XMLSCHEMA_TABLE SYSAUX

SDO_GEOR_PLUGIN_REGISTRY SYSAUX

SDO_TIN_PC_SEQ SYSAUX

SDO_TIN_PC_SYSDATA_TABLE SYSAUX

DEPT USERS

EMP USERS

BONUS USERS

SALGRADE USERS

TEST2 USERS

MODELGTTRAW$

PSTUBTBL

WRI$_ADV_ASA_RECO_DATA

WRI$_HEATMAP_TOPN_DEP1

WRI$_HEATMAP_TOPN_DEP2

PLAN_TABLE$

OL$

OL$HINTS

OL$NODES

KU$NOEXP_TAB

KU$_LIST_FILTER_TEMP

KU$_LIST_FILTER_TEMP_2

ODCI_SECOBJ$

ODCI_WARNINGS$

ODCI_PMO_ROWIDS$

XS$VALIDATION_TABLE

KU$XKTFBUE

IMPDP_STATS

DATA_PUMP_XPL_TABLE$

KU$_DATAPUMP_MASTER_12_2

KU$_DATAPUMP_MASTER_12_0

KU$_DATAPUMP_MASTER_11_2

KU$_DATAPUMP_MASTER_11_1_0_7

KU$_DATAPUMP_MASTER_11_1

KU$_DATAPUMP_MASTER_10_1

TABLE_NAME TABLESPACE_NAME

SPD_SCRATCH_TAB

XDB$XIDX_IMP_T

SAM_SPARSITY_ADVICE

SDO_CS_CONTEXT_INFORMATION

SDO_ST_TOLERANCE

SDO_TXN_JOURNAL

SDO_TXN_IDX_EXP_UPD_RGN

SDO_TOPO_TRANSACT_DATA

SDO_TOPO_RELATION_DATA

SDO_TOPO_DATA$

SDO_WFS_LOCAL_TXNS

SDO_GR_MOSAIC_0

SDO_GR_MOSAIC_1

SDO_GR_MOSAIC_2

SDO_GR_MOSAIC_3

SDO_GR_MOSAIC_CB

SDO_GR_PARALLEL

SDO_GR_RDT_1

RDF_PARAMETER

TEST USERS

TEST1 USERS

117 rows selected.

SYS@clonepdb_plug>col owner format A20;

SYS@clonepdb_plug>select owner,count(*) from dba_tables group by owner order by 2;

OWNER COUNT(*)

AUDSYS 1

OLAPSYS 2

DBSFWUSER 3

OUTLN 3

APPQOSSYS 5

ORDSYS 5

OJVMSYS 6

SCOTT 7

DBSNMP 20

LBACSYS 22

XDB 32

OWNER COUNT(*)

WMSYS 38

GSMADMIN_INTERNAL 39

DVSYS 40

CTXSYS 53

ORDDATA 90

SYSTEM 128

MDSYS 135

SYS 1484

19 rows selected.

create or replace view sys.user_tables

(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, clustering, activity_tracking, dml_timestamp, has_identity, container_data, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate, default_collation, duplicated, sharded, external, cellmemory, containers_default, container_map, extended_data_link, extended_data_link_map, inmemory_service, inmemory_service_name, container_map_object)

;

comment on table SYS.USER_TABLES is 'Description of the user''s own relational tables';

comment on column SYS.USER_TABLES.TABLE_NAME is 'Name of the table';

comment on column SYS.USER_TABLES.TABLESPACE_NAME is 'Name of the tablespace containing the table';

comment on column SYS.USER_TABLES.CLUSTER_NAME is 'Name of the cluster, if any, to which the table belongs';

comment on column SYS.USER_TABLES.IOT_NAME is 'Name of the index-only table, if any, to which the overflow or mapping table entry belongs';

comment on column SYS.USER_TABLES.STATUS is 'Status of the table will be UNUSABLE if a previous DROP TABLE operation failed,

VALID otherwise';

comment on column SYS.USER_TABLES.PCT_FREE is 'Minimum percentage of free space in a block';

comment on column SYS.USER_TABLES.PCT_USED is 'Minimum percentage of used space in a block';

comment on column SYS.USER_TABLES.INI_TRANS is 'Initial number of transactions';

comment on column SYS.USER_TABLES.MAX_TRANS is 'Maximum number of transactions';

comment on column SYS.USER_TABLES.INITIAL_EXTENT is 'Size of the initial extent in bytes';

comment on column SYS.USER_TABLES.NEXT_EXTENT is 'Size of secondary extents in bytes';

comment on column SYS.USER_TABLES.MIN_EXTENTS is 'Minimum number of extents allowed in the segment';

comment on column SYS.USER_TABLES.MAX_EXTENTS is 'Maximum number of extents allowed in the segment';

comment on column SYS.USER_TABLES.PCT_INCREASE is 'Percentage increase in extent size';

comment on column SYS.USER_TABLES.FREELISTS is 'Number of process freelists allocated in this segment';

comment on column SYS.USER_TABLES.FREELIST_GROUPS is 'Number of freelist groups allocated in this segment';

comment on column SYS.USER_TABLES.LOGGING is 'Logging attribute';

comment on column SYS.USER_TABLES.BACKED_UP is 'Has table been backed up since last modification?';

comment on column SYS.USER_TABLES.NUM_ROWS is 'The number of rows in the table';

comment on column SYS.USER_TABLES.BLOCKS is 'The number of used blocks in the table';

comment on column SYS.USER_TABLES.EMPTY_BLOCKS is 'The number of empty (never used) blocks in the table';

comment on column SYS.USER_TABLES.AVG_SPACE is 'The average available free space in the table';

comment on column SYS.USER_TABLES.CHAIN_CNT is 'The number of chained rows in the table';

comment on column SYS.USER_TABLES.AVG_ROW_LEN is 'The average row length, including row overhead';

comment on column SYS.USER_TABLES.AVG_SPACE_FREELIST_BLOCKS is 'The average freespace of all blocks on a freelist';

comment on column SYS.USER_TABLES.NUM_FREELIST_BLOCKS is 'The number of blocks on the freelist';

comment on column SYS.USER_TABLES.DEGREE is 'The number of threads per instance for scanning the table';

comment on column SYS.USER_TABLES.INSTANCES is 'The number of instances across which the table is to be scanned';

comment on column SYS.USER_TABLES.CACHE is 'Whether the table is to be cached in the buffer cache';

comment on column SYS.USER_TABLES.TABLE_LOCK is 'Whether table locking is enabled or disabled';

comment on column SYS.USER_TABLES.SAMPLE_SIZE is 'The sample size used in analyzing this table';

comment on column SYS.USER_TABLES.LAST_ANALYZED is 'The date of the most recent time this table was analyzed';

comment on column SYS.USER_TABLES.PARTITIONED is 'Is this table partitioned? YES or NO';

comment on column SYS.USER_TABLES.IOT_TYPE is 'If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL';

comment on column SYS.USER_TABLES.TEMPORARY is 'Can the current session only see data that it place in this object itself?';

comment on column SYS.USER_TABLES.SECONDARY is 'Is this table object created as part of icreate for domain indexes?';

comment on column SYS.USER_TABLES.NESTED is 'Is the table a nested table?';

comment on column SYS.USER_TABLES.BUFFER_POOL is 'The default buffer pool to be used for table blocks';

comment on column SYS.USER_TABLES.FLASH_CACHE is 'The default flash cache hint to be used for table blocks';

comment on column SYS.USER_TABLES.CELL_FLASH_CACHE is 'The default cell flash cache hint to be used for table blocks';

comment on column SYS.USER_TABLES.ROW_MOVEMENT is 'Whether partitioned row movement is enabled or disabled';

comment on column SYS.USER_TABLES.GLOBAL_STATS is 'Are the statistics calculated without merging underlying partitions?';

comment on column SYS.USER_TABLES.USER_STATS is 'Were the statistics entered directly by the user?';

comment on column SYS.USER_TABLES.DURATION is 'If temporary table, then duration is sys$session or sys$transaction else NULL';

comment on column SYS.USER_TABLES.SKIP_CORRUPT is 'Whether skip corrupt blocks is enabled or disabled';

comment on column SYS.USER_TABLES.MONITORING is 'Should we keep track of the amount of modification?';

comment on column SYS.USER_TABLES.CLUSTER_OWNER is 'Owner of the cluster, if any, to which the table belongs';

comment on column SYS.USER_TABLES.DEPENDENCIES is 'Should we keep track of row level dependencies?';

comment on column SYS.USER_TABLES.COMPRESSION is 'Whether table compression is enabled or not';

comment on column SYS.USER_TABLES.COMPRESS_FOR is 'Compress what kind of operations';

comment on column SYS.USER_TABLES.DROPPED is 'Whether table is dropped and is in Recycle Bin';

comment on column SYS.USER_TABLES.READ_ONLY is 'Whether table is read only or not';

comment on column SYS.USER_TABLES.SEGMENT_CREATED is 'Whether the table segment is created or not';

comment on column SYS.USER_TABLES.RESULT_CACHE is 'The result cache mode annotation for the table';

comment on column SYS.USER_TABLES.CLUSTERING is 'Whether table has clustering clause or not';

comment on column SYS.USER_TABLES.ACTIVITY_TRACKING is 'ILM activity tracking mode';

comment on column SYS.USER_TABLES.DML_TIMESTAMP is 'ILM row modification or creation timestamp tracking mode';

comment on column SYS.USER_TABLES.HAS_IDENTITY is 'Whether the table has an identity column';

comment on column SYS.USER_TABLES.CONTAINER_DATA is 'An indicator of whether the table contains Container-specific data';

comment on column SYS.USER_TABLES.INMEMORY is 'Whether in-memory is enabled or not';

comment on column SYS.USER_TABLES.INMEMORY_PRIORITY is 'User defined priority in which in-memory column store object is loaded';

comment on column SYS.USER_TABLES.INMEMORY_DISTRIBUTE is 'How the in-memory columnar store object is distributed';

comment on column SYS.USER_TABLES.INMEMORY_COMPRESSION is 'Compression level for the in-memory column store option';

comment on column SYS.USER_TABLES.INMEMORY_DUPLICATE is 'How the in-memory column store object is duplicated';

comment on column SYS.USER_TABLES.DEFAULT_COLLATION is 'Default collation for the table';

comment on column SYS.USER_TABLES.EXTERNAL is 'Whether the table is an external table or not';

comment on column SYS.USER_TABLES.CELLMEMORY is 'Cell columnar cache';

comment on column SYS.USER_TABLES.CONTAINERS_DEFAULT is 'Whether the table is enabled for CONTAINERS() by default';

comment on column SYS.USER_TABLES.CONTAINER_MAP is 'Whether the table is enabled for use with container_map database property';

comment on column SYS.USER_TABLES.EXTENDED_DATA_LINK is 'Whether the table is enabled for fetching extended data link from Root';

comment on column SYS.USER_TABLES.EXTENDED_DATA_LINK_MAP is 'Whether the table is enabled for use with extended data link map';

comment on column SYS.USER_TABLES.INMEMORY_SERVICE is 'How the in-memory columnar store object is distributed for service';

comment on column SYS.USER_TABLES.INMEMORY_SERVICE_NAME is 'Service on which the in-memory columnar store object is distributed';

comment on column SYS.USER_TABLES.CONTAINER_MAP_OBJECT is 'Whether the table is used as the value of container_map database property';

SYS@clonepdb_plug>col owner format A10

SYS@clonepdb_plug>col object_name format A20

SYS@clonepdb_plug>select owner,object_name,object_type

2 from dba_objects where object_name in('DICT','DICTIONARY');

OWNER OBJECT_NAME OBJECT_TYPE

SYS DICTIONARY VIEW

PUBLIC DICTIONARY SYNONYM

PUBLIC DICT SYNONYM

SYS@clonepdb_plug>desc dict

Name Null? Type

TABLE_NAME VARCHAR2(128)

COMMENTS VARCHAR2(4000)

SYS@clonepdb_plug>select table_name from dict where table_name like '%TEST%';

TABLE_NAME

CDB_XDS_LATEST_ACL_REFSTAT

DBA_XDS_LATEST_ACL_REFSTAT

USER_XDS_LATEST_ACL_REFSTAT

ALL_XDS_LATEST_ACL_REFSTAT

12c

SYS@clonepdb_plug>select column_name,comments from dict_columns

2 where table_name='DICT';

no rows selected

SYS@clonepdb_plug>col table_name format A30

SYS@clonepdb_plug>select
2 from (select table_name,count()

3 from dict_columns

4 group by table_name

5 order by 2 desc)

6 where rownum<6;

TABLE_NAME COUNT(*)

DBA_HIST_ACTIVE_SESS_HISTORY 112

DBA_HIST_SQLSTAT 80

DBA_HIST_CELL_DISK_SUMMARY 79

ALL_TABLES 77

DBA_TABLES 77

SYS@clonepdb_plug>select table_name from dict where table_name like'DBA%COLUMNS';

TABLE_NAME

DBA_CONS_COLUMNS

DBA_CUBE_VIEW_COLUMNS

DBA_LOG_GROUP_COLUMNS

DBA_SUBPART_KEY_COLUMNS

DBA_TAB_COLUMNS

DBA_UPDATABLE_COLUMNS

DBA_CUBE_DIM_VIEW_COLUMNS

DBA_IND_COLUMNS

DBA_AUDIT_POLICY_COLUMNS

DBA_JOIN_IND_COLUMNS

DBA_APPLY_CONFLICT_COLUMNS

TABLE_NAME

DBA_CLU_COLUMNS

DBA_CUBE_HIER_VIEW_COLUMNS

DBA_HIER_COLUMNS

DBA_PUBLISHED_COLUMNS

DBA_CONS_OBJ_COLUMNS

DBA_ENCRYPTED_COLUMNS

DBA_ANALYTIC_VIEW_COLUMNS

DBA_JSON_COLUMNS

DBA_APPLY_DML_CONF_COLUMNS

DBA_APPLY_TABLE_COLUMNS

DBA_STREAMS_COLUMNS

TABLE_NAME

DBA_STREAMS_KEEP_COLUMNS

DBA_GG_AUTO_CDR_COLUMNS

DBA_SUBSCRIBED_COLUMNS

DBA_OLDIMAGE_COLUMNS

DBA_PART_KEY_COLUMNS

DBA_APPLY_KEY_COLUMNS

DBA_COMPARISON_COLUMNS

SCOTT@clonepdb_plug>col OBJECT_NAME format A20

SCOTT@clonepdb_plug>select object_name,object_type from obj;

OBJECT_NAME OBJECT_TYPE

DEPT TABLE

PK_DEPT INDEX

EMP TABLE

PK_EMP INDEX

BONUS TABLE

SALGRADE TABLE

TEST TABLE

V_TEST VIEW

TEST1 TABLE

TEST2 TABLE

V_TEST1 VIEW

11 rows selected.

SCOTT@clonepdb_plug>desc user_source

Name Null? Type

NAME VARCHAR2(128)

TYPE VARCHAR2(12)

LINE NUMBER

TEXT VARCHAR2(4000)

ORIGIN_CON_ID NUMBER

HR@pdbtest>select text from user_source where name='P_DELEMPLOYEES';

TEXT

PROCEDURE P_DelEmployees

(v_empno IN employees.employee_id%TYPE)

No_result EXCEPTION;

BEGIN

DELETE FROM employees WHERE employee_id = v_empno;

IF SQL%NOTFOUND THEN

RAISE no_result;

END IF;

DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!');

EXCEPTION

TEXT

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END P_DelEmployees;

17 rows selected.

数据字典

SYS@newtest>select view_definition from v$fixed_view_definition

2 where view_name='V$FIXED_TABLE';

VIEW_DEFINITION

select NAME , OBJECT_ID , TYPE , TABLE_NUM, CON_ID from GV$FIXED_TABLE where in

st_id = USERENV('Instance')

SYS@newtest>select view_definition from v$fixed_view_definition

2 where view_name='GV$FIXED_TABLE';

VIEW_DEFINITION

select inst_id,kqftanam, kqftaobj, 'TABLE', indx, con_id from x$kqfta union all

select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537, con_id from x$kqfvi union all

select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537, con_id from x$kqfdt

SYS@newtest>select view_definition from v$fixed_view_definition a where a.view_n

ame='V$PARAMETER';

VIEW_DEFINITION

select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, DEFAULT_VALUE, ISDEFAULT , IS

SES_MODIFIABLE , ISSYS_MODIFIABLE , ISPDB_MODIFIABLE , ISINSTANCE_MODIFIABLE, IS

MODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH

, CON_ID from GV$PARAMETER where inst_id = USERENV('Instance')

SYS@newtest>select view_definition from v$fixed_view_definition a where a.view_n

ame='GV$PARAMETER';

VIEW_DEFINITION

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdfl, kspps

tdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg/65

536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE

','FALSE'), decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE'), decode(bitand(k

sppiflg,4),4,'FALSE', decode(bitand(ksppiflg

/65536,3), 0, 'FALSE', 'TRUE')), decode(bitand(ksppstvf,7),1,'MODIFIED',4,'S

YSTEM_MOD','FALSE'), decode(bitand(ksppstvf,2),2,'TRUE','FALSE'), decode(bitan

d(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppilrmflg/268435456,

1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash, y.con_id from x$ksp

pi x, x$ksppcv y where (x.indx = y.indx) and bitand(ksppiflg,268435456) = 0 and

((translate(ksppinm,'','$') not like '$$%') and ((translate(ksppinm,'','$

VIEW_DEFINITION

') not like '$%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0))

)

数据字典

SYS@clonepdb_plug>grant select on v$parameter to scott;

grant select on v$parameter to scott

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

本文转自whshurk 51CTO博客，原文链接：http://blog.51cto.com/shurk/2059065，如需转载请自行联系原作者