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 NOSYS@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
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| 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_IDOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
0
SYS@newtest>select * from v$version where rownum<2;
BANNER
CON_IDOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
0
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 81840SYS@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)
as
;
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)
AS
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
