Oracle PL/SQL/System Tables Views/USER TABLES
Содержание
- 1 alter table emp move tablespace users and then check the result by querying user_tables
- 2 Create sql statements from user_tables
- 3 Get table name and table space for a table by its name
- 4 Query a table in user_table by table_name
- 5 query table_name, tablespace_name from user_tables
- 6 Query user_tables for the table just created
- 7 To get a listing of all tables that you own
- 8 User_tables structure
- 9 Verify the creation of the statistics by running the following queries:
alter table emp move tablespace users and then check the result by querying user_tables
SQL>
SQL> create table EMP (
2 course_id number not null,
3 course_name varchar2(60) not null,
4 subject_id number not null,
5 duration number(2),
6 skill_lvl varchar2(12) not null
7 );
Table created.
SQL>
SQL> select tablespace_name, table_name
2 from user_tables
3 where table_name in ("EMP", "DEPT")
4 order by 1, 2;
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYSTEM EMP
SQL>
SQL>
SQL> select segment_name, tablespace_name
2 from user_segments
3 where segment_name = "EMP";
SEGMENT_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
EMP SYSTEM
SQL>
SQL> alter table emp move
2 tablespace users;
Table altered.
SQL>
SQL>
SQL> select segment_name, tablespace_name
2 from user_segments
3 where segment_name = "EMP";
SEGMENT_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
EMP USERS
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
select default_tablespace, temporary_tablespace from dba_users by username
select username, default_tablespace, temporary_tablespace
from dba_users
where default_tablespace = "SYSTEM" or temporary_tablespace = "SYSTEM";
alter user scott
default tablespace users;
select default_tablespace, temporary_tablespace
from dba_users
where username = "SCOTT";
Create sql statements from user_tables
SQL> SELECT "DROP TABLE " || table_name || " CASCADE CONSTRAINTS;" FROM user_tables;
"DROPTABLE"||TABLE_NAME||"CASCADECONSTRAINTS;"
--------------------------------------------------------------
DROP TABLE SYSTEM_STATS CASCADE CONSTRAINTS;
DROP TABLE INVENTORY_TBL CASCADE CONSTRAINTS;
DROP TABLE DEPT$AUDIT CASCADE CONSTRAINTS;
DROP TABLE PASSWORD_AUDIT CASCADE CONSTRAINTS;
DROP TABLE EMP_DELTAS CASCADE CONSTRAINTS;
DROP TABLE CUSTLOG CASCADE CONSTRAINTS;
DROP TABLE P CASCADE CONSTRAINTS;
DROP TABLE DEMO CASCADE CONSTRAINTS;
DROP TABLE CUST_NO_KEY_TABLE CASCADE CONSTRAINTS;
DROP TABLE IT CASCADE CONSTRAINTS;
DROP TABLE P1 CASCADE CONSTRAINTS;
DROP TABLE MYSTATS CASCADE CONSTRAINTS;
DROP TABLE WORKING_EMPS CASCADE CONSTRAINTS;
DROP TABLE WORKING_EMPLOYEES CASCADE CONSTRAINTS;
DROP TABLE EMPTEMPTABLE CASCADE CONSTRAINTS;
DROP TABLE TEMP_TABLE CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEETEMPTABLE CASCADE CONSTRAINTS;
DROP TABLE TMP CASCADE CONSTRAINTS;
DROP TABLE PARMS CASCADE CONSTRAINTS;
DROP TABLE IX CASCADE CONSTRAINTS;
DROP TABLE CUST_WITH_VARRAY_TEMP_TABLE CASCADE CONSTRAINTS;
DROP TABLE WORKING_CUSTOMERS CASCADE CONSTRAINTS;
22 rows selected.
Get table name and table space for a table by its name
SQL>
SQL> create table foo (a int );
Table created.
SQL>
SQL>
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name = "FOO";
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FOO SYSTEM
SQL>
SQL> drop table foo;
Table dropped.
SQL>
SQL> create table foo (a int ) tablespace users;
Table created.
SQL>
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name = "FOO";
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FOO USERS
SQL>
SQL> drop table foo;
Table dropped.
SQL>
SQL>
Query a table in user_table by table_name
SQL>
SQL> create table t(
2 a int
3 );
Table created.
SQL>
SQL>
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name = "T";
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T SYSTEM
SQL>
SQL> drop table t;
Table dropped.
SQL>
query table_name, tablespace_name from user_tables
SQL>
SQL> select table_name, tablespace_name
2 from user_tables where rownum < 50
3 order by table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ARGUMENT$ SYSTEM
ATTRCOL$ SYSTEM
BOOTSTRAP$ SYSTEM
CCOL$ SYSTEM
CDEF$ SYSTEM
CLU$ SYSTEM
COL$ SYSTEM
COLTYPE$ SYSTEM
CON$ SYSTEM
DIR$ SYSTEM
ERROR$ SYSTEM
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FET$ SYSTEM
FILE$ SYSTEM
ICOL$ SYSTEM
ICOLDEP$ SYSTEM
IDL_CHAR$ SYSTEM
IDL_SB4$ SYSTEM
IDL_UB1$ SYSTEM
IDL_UB2$ SYSTEM
IND$ SYSTEM
JAVAOBJ$ SYSTEM
LIBRARY$ SYSTEM
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LOB$ SYSTEM
NTAB$ SYSTEM
OBJ$ SYSTEM
OBJAUTH$ SYSTEM
OPQTYPE$ SYSTEM
PROCEDURE$ SYSTEM
PROCEDUREINFO$ SYSTEM
PROXY_DATA$ SYSTEM
PROXY_ROLE_DATA$ SYSTEM
REFCON$ SYSTEM
SEG$ SYSTEM
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SEQ$ SYSTEM
SOURCE$ SYSTEM
SUBCOLTYPE$ SYSTEM
SUPEROBJ$ SYSTEM
SYN$ SYSTEM
TAB$ SYSTEM
TS$ SYSTEM
TSQ$ SYSTEM
TYPED_VIEW$ SYSTEM
TYPE_MISC$ SYSTEM
UET$ SYSTEM
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
UGROUP$ SYSTEM
UNDO$ SYSTEM
USER$ SYSTEM
VIEW$ SYSTEM
VIEWTRCOL$ SYSTEM
49 rows selected.
SQL>
Query user_tables for the table just created
SQL>
SQL> create table foo (a int)
2 tablespace users;
Table created.
SQL>
SQL> select table_name, tablespace_name
2 from user_tables
3 where table_name = "FOO";
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FOO USERS
SQL>
SQL> select username, default_tablespace, temporary_tablespace
2 from dba_users
3 where default_tablespace = "SYSTEM" or temporary_tablespace = "SYSTEM";
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SYSTEM SYSTEM TEMP
SYS SYSTEM TEMP
DIP SYSTEM TEMP
OUTLN SYSTEM TEMP
MDSYS SYSTEM TEMP
TSMSYS SYSTEM TEMP
6 rows selected.
To get a listing of all tables that you own
SQL>
SQL> -- To get a listing of all tables that you own
SQL>
SQL> SELECT table_name FROM USER_TABLES;
TABLE_NAME
------------------------------
CON$
UNDO$
CDEF$
CCOL$
PROXY_ROLE_DATA$
FILE$
FET$
TS$
PROXY_DATA$
SEG$
UET$
TABLE_NAME
------------------------------
TSQ$
USER$
BOOTSTRAP$
OBJ$
ICOLDEP$
LIBRARY$
OPQTYPE$
REFCON$
NTAB$
TYPE_MISC$
VIEWTRCOL$
TABLE_NAME
------------------------------
ATTRCOL$
SUBCOLTYPE$
COLTYPE$
LOB$
TAB$
CLU$
COL$
IND$
ICOL$
OBJAUTH$
UGROUP$
TABLE_NAME
------------------------------
SYN$
VIEW$
TYPED_VIEW$
SUPEROBJ$
SEQ$
PROCEDURE$
PROCEDUREINFO$
ARGUMENT$
SOURCE$
IDL_UB1$
IDL_CHAR$
TABLE_NAME
------------------------------
IDL_UB2$
IDL_SB4$
DIR$
JAVAOBJ$
ERROR$
SETTINGS$
TRIGGER$
TRIGGERCOL$
TRIGGERJAVAF$
TRIGGERJAVAS$
TRIGGERJAVAC$
TABLE_NAME
------------------------------
TRIGGERJAVAM$
SYSAUTH$
OBJPRIV$
DEFROLE$
PROFILE$
PROFNAME$
DEPENDENCY$
ACCESS$
LINK$
TRUSTED_LIST$
PROPS$
TABLE_NAME
------------------------------
COM$
RESOURCE_COST$
INCEXP
INCVID
INCFIL
_default_auditing_options_
AUDIT$
PENDING_TRANS$
PENDING_SESSIONS$
PENDING_SUB_SESSIONS$
SUMPARTLOG$
TABLE_NAME
------------------------------
SUMDELTA$
SNAP_LOGDEP$
SNAP_LOADERTIME$
SLOG$
MLOG$
ID_GENS$
OID$
RESULT$
PARAMETER$
METHOD$
ATTRIBUTE$
TABLE_NAME
------------------------------
COLLECTION$
TYPE$
TYPEHIERARCHY$
VIEWCON$
SNAP$
SNAP_REFTIME$
MLOG_REFCOL$
SNAP_REFOP$
SNAP_COLMAP$
SNAP_OBJCOL$
REG_SNAP$
TABLE_NAME
------------------------------
SNAP_SITE$
JOB$
RGCHILD$
RGROUP$
DUC$
HISTGRM$
HIST_HEAD$
DUAL
PARTOBJ$
PARTCOL$
TABPART$
TABLE_NAME
------------------------------
INDPART$
SUBPARTCOL$
TABSUBPART$
INDSUBPART$
TABCOMPART$
INDCOMPART$
PARTLOB$
LOBFRAG$
LOBCOMPPART$
DEFSUBPART$
DEFSUBPARTLOB$
TABLE_NAME
------------------------------
SYSTEM_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
STMT_AUDIT_OPTION_MAP
RESOURCE_MAP
USER_ASTATUS_MAP
EXPACT$
NOEXP$
PROCEDUREJAVA$
PROCEDUREC$
PROCEDUREPLSQL$
KOPM$
TABLE_NAME
------------------------------
VTABLE$
USER_HISTORY$
MIGRATE$
OPERATOR$
OPBINDING$
OPANCILLARY$
OPARG$
INDTYPES$
INDOP$
INDARRAYTYPE$
SECOBJ$
TABLE_NAME
------------------------------
ASSOCIATION$
USTATS$
JAVASNM$
SUM$
SUMDETAIL$
SUMINLINE$
SUMKEY$
SUMAGG$
SUMJOIN$
SUMDEP$
SUMPRED$
TABLE_NAME
------------------------------
SUMQB$
DIM$
DIMLEVEL$
DIMLEVELKEY$
DIMJOINKEY$
DIMATTR$
HIER$
HIERLEVEL$
RLS$
RLS_SC$
RLS_GRP$
TABLE_NAME
------------------------------
RLS_CTX$
CONTEXT$
RULESET$
INDPART_PARAM$
SQL$
SQL$TEXT
SQLPROF$
SQLPROF$DESC
SQLPROF$ATTR
EXPPKGOBJ$
EXPPKGACT$
TABLE_NAME
------------------------------
EXPDEPOBJ$
EXPDEPACT$
MON_MODS$
MON_MODS_ALL$
METAVIEW$
METAFILTER$
METAXSL$
METAXSLPARAM$
METASTYLESHEET
METASCRIPT$
METASCRIPTFILTER$
TABLE_NAME
------------------------------
METANAMETRANS$
METAPATHMAP$
EXTERNAL_TAB$
EXTERNAL_LOCATION$
SQL_VERSION$
COL_USAGE$
CDC_SYSTEM$
CDC_CHANGE_SOURCES$
CDC_CHANGE_SETS$
CDC_CHANGE_TABLES$
CDC_SUBSCRIBERS$
TABLE_NAME
------------------------------
CDC_SUBSCRIBED_TABLES$
CDC_SUBSCRIBED_COLUMNS$
CDC_CHANGE_COLUMNS$
CDC_PROPAGATIONS$
CDC_PROPAGATED_SETS$
JIJOIN$
JIREFRESHSQL$
LOG$
APPROLE$
FGA$
FGACOL$
TABLE_NAME
------------------------------
AUD$
FGA_LOG$
OBJECT_USAGE
AUX_STATS$
SMON_SCN_TIME
AW$
PS$
AW_OBJ$
AW_PROP$
MAP_FILE$
MAP_FILE_EXTENT$
TABLE_NAME
------------------------------
MAP_SUBELEMENT$
MAP_ELEMENT$
MAP_EXTELEMENT$
MAP_COMPLIST$
STREAMS$_CAPTURE_PROCESS
STREAMS$_APPLY_PROCESS
STREAMS$_PROPAGATION_PROCESS
STREAMS$_PROCESS_PARAMS
STREAMS$_EXTRA_ATTRS
STREAMS$_PREPARE_OBJECT
STREAMS$_PREPARE_DDL
TABLE_NAME
------------------------------
STREAMS$_APPLY_MILESTONE
STREAMS$_APPLY_PROGRESS
STREAMS$_KEY_COLUMNS
STREAMS$_DEF_PROC
STREAMS$_RULES
APPLY$_SOURCE_OBJ
APPLY$_SOURCE_SCHEMA
APPLY$_VIRTUAL_OBJ_CONS
APPLY$_CONSTRAINT_COLUMNS
APPLY$_DEST_OBJ
APPLY$_DEST_OBJ_CMAP
TABLE_NAME
------------------------------
APPLY$_DEST_OBJ_OPS
APPLY$_ERROR
APPLY$_ERROR_TXN
APPLY$_ERROR_HANDLER
APPLY$_CONF_HDLR_COLUMNS
STREAMS$_DEST_OBJS
STREAMS$_DEST_OBJ_COLS
STREAMS$_PRIVILEGED_USER
STREAMS$_MESSAGE_RULES
WARNING_SETTINGS$
STREAMS$_MESSAGE_CONSUMERS
TABLE_NAME
------------------------------
STREAMS$_APPLY_SPILL_TXN
STREAMS$_APPLY_SPILL_TXN_LIST
FGR$_FILE_GROUPS
FGR$_FILE_GROUP_VERSIONS
FGR$_FILE_GROUP_EXPORT_INFO
FGR$_FILE_GROUP_FILES
FGR$_TABLESPACE_INFO
FGR$_TABLE_INFO
RECYCLEBIN$
FIXED_OBJ$
TAB_STATS$
TABLE_NAME
------------------------------
IND_STATS$
PROXY_INFO$
PROXY_ROLE_INFO$
REDEF$
REDEF_OBJECT$
REDEF_DEP_ERROR$
NCOMP_DLL$
IND_ONLINE$
SERVICE$
EXPIMP_TTS_CT$
TSM_SRC$
TABLE_NAME
------------------------------
TSM_DST$
DIR$MIGRATE_OPERATIONS
DIR$SERVICE_OPERATIONS
DIR$ESCALATE_OPERATIONS
DIR$QUIESCE_OPERATIONS
DIR$INSTANCE_ACTIONS
DIR$RESONATE_OPERATIONS
DIR$ALERT_HISTORY
DIR$REASON_STRINGS
DIR$DATABASE_ATTRIBUTES
DIR$VICTIM_POLICY
TABLE_NAME
------------------------------
DIR$NODE_ATTRIBUTES
DIR$SERVICE_ATTRIBUTES
CACHE_STATS_1$
CACHE_STATS_0$
STATS_TARGET$
RESOURCE_PLAN$
RESOURCE_CONSUMER_GROUP$
RESOURCE_PLAN_DIRECTIVE$
RESOURCE_GROUP_MAPPING$
RESOURCE_MAPPING_PRIORITY$
ENC$
TABLE_NAME
------------------------------
RECO_SCRIPT$
RECO_SCRIPT_PARAMS$
RECO_SCRIPT_BLOCK$
RECO_SCRIPT_ERROR$
REGISTRY$
REGISTRY$SCHEMAS
REGISTRY$LOG
REGISTRY$HISTORY
AUDIT_ACTIONS
WRI$_ADV_DEFINITIONS
WRI$_ADV_TASKS
TABLE_NAME
------------------------------
WRI$_ADV_DEF_PARAMETERS
WRI$_ADV_PARAMETERS
WRI$_ADV_OBJECTS
WRI$_ADV_FINDINGS
WRI$_ADV_RECOMMENDATIONS
WRI$_ADV_ACTIONS
WRI$_ADV_RATIONALE
WRI$_ADV_REC_ACTIONS
WRI$_ADV_DIRECTIVES
WRI$_ADV_JOURNAL
WRI$_ADV_MESSAGE_GROUPS
TABLE_NAME
------------------------------
WRI$_ADV_USAGE
WRI$_ADV_SQLW_SUM
WRI$_ADV_SQLW_STMTS
WRI$_ADV_SQLW_TABLES
WRI$_ADV_SQLW_TABVOL
WRI$_ADV_SQLW_COLVOL
WRI$_ADV_SQLA_MAP
WRI$_ADV_SQLA_STMTS
WRI$_ADV_SQLA_TMP
WRI$_ADV_SQLA_FAKE_REG
WRI$_SEGADV_OBJLIST
TABLE_NAME
------------------------------
WRI$_SEGADV_CNTRLTAB
WRI$_OPTSTAT_TAB_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_OPR
OPTSTAT_HIST_CONTROL$
DBMS_LOCK_ALLOCATED
DBMS_ALERT_INFO
TRANSFORMATIONS$
TABLE_NAME
------------------------------
ATTRIBUTE_TRANSFORMATIONS$
RULE_SET_IEUAC$
RULE_SET$
RULE$
RULE_MAP$
RULE_EC$
REC_TAB$
REC_VAR$
RULE_SET_EE$
RULE_SET_TE$
RULE_SET_VE$
TABLE_NAME
------------------------------
RULE_SET_RE$
RULE_SET_ROR$
RULE_SET_FOB$
RULE_SET_NL$
SYS_IOT_OVER_4468
RULE_SET_RDEP$
SYS_IOT_OVER_4474
SYS_IOT_OVER_4478
REG$
LOC$
AQ$_REPLAY_INFO
TABLE_NAME
------------------------------
AQ$_QUEUE_TABLE_AFFINITIES
AQ$_SCHEDULES
AQ$_MESSAGE_TYPES
AQ$_PROPAGATION_STATUS
AQ$_PENDING_MESSAGES
AQ$_QUEUE_STATISTICS
AQ$_PUBLISHER
AQ_EVENT_TABLE
AQ_SRVNTFN_TABLE
SCHEDULER$_PROGRAM
SCHEDULER$_CLASS
TABLE_NAME
------------------------------
SCHEDULER$_JOBQTAB
AQ$_SCHEDULER$_JOBQTAB_S
SYS_IOT_OVER_5062
SCHEDULER$_JOB
SCHEDULER$_JOB_ARGUMENT
SCHEDULER$_WINDOW
SCHEDULER$_PROGRAM_ARGUMENT
SCHEDULER$_SRCQ_INFO
SCHEDULER$_SRCQ_MAP
SCHEDULER$_EVTQ_SUB
SCHEDULER$_EVENT_LOG
TABLE_NAME
------------------------------
SCHEDULER$_JOB_RUN_DETAILS
SCHEDULER$_WINDOW_DETAILS
SCHEDULER$_WINDOW_GROUP
SCHEDULER$_WINGRP_MEMBER
SCHEDULER$_SCHEDULE
SCHEDULER$_CHAIN
SCHEDULER$_STEP
SCHEDULER$_STEP_STATE
SCHEDULER$_GLOBAL_ATTRIBUTE
SCHEDULER$_EVENT_QTAB
AQ$_SCHEDULER$_EVENT_QTAB_S
TABLE_NAME
------------------------------
SYS_IOT_OVER_5148
SCHEDULER$_OLDOIDS
LOGMNRG_SEED$
LOGMNRG_DICTIONARY$
LOGMNRG_OBJ$
LOGMNRG_TAB$
LOGMNRG_COL$
LOGMNRG_ATTRCOL$
LOGMNRG_TS$
LOGMNRG_IND$
LOGMNRG_USER$
TABLE_NAME
------------------------------
LOGMNRG_TABPART$
LOGMNRG_TABSUBPART$
LOGMNRG_TABCOMPART$
LOGMNRG_TYPE$
LOGMNRG_COLTYPE$
LOGMNRG_ATTRIBUTE$
LOGMNRG_LOB$
LOGMNRG_CDEF$
LOGMNRG_CCOL$
LOGMNRG_ICOL$
LOGMNRG_LOBFRAG$
TABLE_NAME
------------------------------
LOGMNRG_INDPART$
LOGMNRG_INDSUBPART$
LOGMNRG_INDCOMPART$
LOGMNR_BUILDLOG
LOGMNR_INTERESTING_COLS
KU_NOEXP_TAB
KUPC$DATAPUMP_QUETAB
AQ$_KUPC$DATAPUMP_QUETAB_S
SYS_IOT_OVER_7141
STREAMS$_APPLY_SPILL_MESSAGES
STREAMS$_INTERNAL_TRANSFORM
TABLE_NAME
------------------------------
HS$_FDS_CLASS
HS$_FDS_INST
HS$_BASE_CAPS
HS$_CLASS_CAPS
HS$_INST_CAPS
HS$_BASE_DD
HS$_CLASS_DD
HS$_INST_DD
HS$_CLASS_INIT
HS$_INST_INIT
HS$_FDS_CLASS_DATE
TABLE_NAME
------------------------------
DBMS_UPG_LOG$
DBMS_UPG_SYSAUTH_C0$
DBMS_UPG_SYSAUTH_CS$
DBMS_UPG_SYSAUTH_CT$
DBMS_UPG_OBJAUTH_C0$
DBMS_UPG_OBJAUTH_CS$
DBMS_UPG_OBJAUTH_CT$
DBMS_UPG_RLS_C0$
DBMS_UPG_RLS_CS$
DBMS_UPG_RLS_CT$
DBMS_UPG_CHANGE$
TABLE_NAME
------------------------------
DBMS_UPG_STATUS$
DBMS_UPG_OBJECT$
DBMS_UPG_ACTION_QUEUE
DBMS_UPG_CON_MAPPING
DBMS_UPG_DEBUG
AQ$_MEM_MC
AQ$_AQ$_MEM_MC_S
SYS_IOT_OVER_8638
UTL_RECOMP_ERRORS
INVALIDATION_REGISTRY$
WRI$_DBU_FEATURE_USAGE
TABLE_NAME
------------------------------
WRI$_DBU_FEATURE_METADATA
WRI$_DBU_HIGH_WATER_MARK
WRI$_DBU_HWM_METADATA
WRI$_DBU_USAGE_SAMPLE
WRI$_DBU_CPU_USAGE
WRI$_DBU_CPU_USAGE_SAMPLE
WRI$_ALERT_OUTSTANDING
WRI$_ALERT_HISTORY
ALERT_QT
AQ$_ALERT_QT_S
SYS_IOT_OVER_8748
TABLE_NAME
------------------------------
WRI$_ALERT_THRESHOLD
WRI$_ALERT_THRESHOLD_LOG
WRI$_TRACING_ENABLED
WRI$_AGGREGATION_ENABLED
WRH$_FILESTATXS_BL
WRH$_TEMPSTATXS
WRH$_DATAFILE
WRH$_TEMPFILE
WRH$_COMP_IOSTAT
WRH$_SQLSTAT_BL
WRH$_SQLTEXT
TABLE_NAME
------------------------------
WRH$_SQL_SUMMARY
WRH$_SQL_PLAN
WRH$_SQL_BIND_METADATA
WRH$_OPTIMIZER_ENV
WRH$_SYSTEM_EVENT_BL
WRH$_EVENT_NAME
WRH$_LATCH_NAME
WRH$_BG_EVENT_SUMMARY
WRH$_WAITSTAT_BL
WRH$_ENQUEUE_STAT
WRH$_LATCH_BL
TABLE_NAME
------------------------------
WRH$_LATCH_CHILDREN_BL
WRH$_LATCH_PARENT_BL
WRH$_LATCH_MISSES_SUMMARY_BL
WRH$_LIBRARYCACHE
WRH$_DB_CACHE_ADVICE_BL
WRH$_BUFFER_POOL_STATISTICS
WRH$_ROWCACHE_SUMMARY_BL
WRH$_SGA
WRH$_SGASTAT_BL
WRH$_PGASTAT
WRH$_PROCESS_MEMORY_SUMMARY
TABLE_NAME
------------------------------
WRH$_RESOURCE_LIMIT
WRH$_SHARED_POOL_ADVICE
WRH$_STREAMS_POOL_ADVICE
WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_PGA_TARGET_ADVICE
WRH$_SGA_TARGET_ADVICE
WRH$_INSTANCE_RECOVERY
WRH$_JAVA_POOL_ADVICE
WRH$_THREAD
WRH$_SYSSTAT_BL
WRH$_SYS_TIME_MODEL_BL
TABLE_NAME
------------------------------
WRH$_OSSTAT_BL
WRH$_PARAMETER_BL
WRH$_STAT_NAME
WRH$_OSSTAT_NAME
WRH$_PARAMETER_NAME
WRH$_UNDOSTAT
WRH$_SEG_STAT_BL
WRH$_SEG_STAT_OBJ
WRH$_METRIC_NAME
WRH$_SYSMETRIC_HISTORY
WRH$_SYSMETRIC_SUMMARY
TABLE_NAME
------------------------------
WRH$_SESSMETRIC_HISTORY
WRH$_FILEMETRIC_HISTORY
WRH$_WAITCLASSMETRIC_HISTORY
WRH$_DLM_MISC_BL
WRH$_CR_BLOCK_SERVER
WRH$_CURRENT_BLOCK_SERVER
WRH$_INST_CACHE_TRANSFER_BL
WRH$_ACTIVE_SESSION_HISTORY_BL
WRH$_TABLESPACE_STAT_BL
WRH$_LOG
WRH$_MTTR_TARGET_ADVICE
TABLE_NAME
------------------------------
WRH$_TABLESPACE_SPACE_USAGE
WRH$_SERVICE_NAME
WRH$_SERVICE_STAT_BL
WRH$_SERVICE_WAIT_CLASS_BL
WRH$_SESS_TIME_STATS
WRH$_STREAMS_CAPTURE
WRH$_STREAMS_APPLY_SUM
WRH$_BUFFERED_QUEUES
WRH$_BUFFERED_SUBSCRIBERS
WRH$_RULE_SET
WRI$_SCH_CONTROL
TABLE_NAME
------------------------------
WRI$_SCH_VOTES
WRM$_DATABASE_INSTANCE
WRM$_SNAPSHOT
WRM$_SNAP_ERROR
WRM$_BASELINE
WRM$_WR_CONTROL
WRI$_ADV_SQLT_BINDS
WRI$_ADV_SQLT_STATISTICS
WRI$_ADV_SQLT_PLANS
WRI$_ADV_SQLT_RTN_PLAN
WRI$_SQLSET_DEFINITIONS
TABLE_NAME
------------------------------
WRI$_SQLSET_REFERENCES
WRI$_SQLSET_STATEMENTS
WRI$_SQLSET_PLANS
WRI$_SQLSET_STATISTICS
WRI$_SQLSET_MASK
WRI$_SQLSET_PLAN_LINES
WRI$_SQLSET_BINDS
WRI$_SQLSET_WORKSPACE
SYS_IOT_OVER_9641
SYS$SERVICE_METRICS_TAB
AQ$_SYS$SERVICE_METRICS_TAB_S
TABLE_NAME
------------------------------
SYS_IOT_OVER_10125
XDB_INSTALLATION_TAB
EPG$_AUTH
AQ$_SYS$SERVICE_METRICS_TAB_I
AQ$_SYS$SERVICE_METRICS_TAB_G
AQ$_SYS$SERVICE_METRICS_TAB_H
AQ$_SYS$SERVICE_METRICS_TAB_T
RECENT_RESOURCE_INCARNATIONS$
WRI$_SQLSET_PLANS_TOCAP
WRH$_SERVICE_WAIT_CLASS
WRH$_SERVICE_STAT
TABLE_NAME
------------------------------
WRH$_TABLESPACE_STAT
WRH$_ACTIVE_SESSION_HISTORY
WRH$_INST_CACHE_TRANSFER
WRH$_DLM_MISC
WRH$_SEG_STAT
WRH$_PARAMETER
WRH$_OSSTAT
WRH$_SYS_TIME_MODEL
WRH$_SYSSTAT
WRH$_SGASTAT
WRH$_ROWCACHE_SUMMARY
TABLE_NAME
------------------------------
WRH$_DB_CACHE_ADVICE
WRH$_LATCH_MISSES_SUMMARY
WRH$_LATCH_PARENT
WRH$_LATCH_CHILDREN
WRH$_LATCH
WRH$_WAITSTAT
WRH$_SYSTEM_EVENT
WRH$_SQLSTAT
WRH$_FILESTATXS
AQ$_ALERT_QT_I
AQ$_ALERT_QT_G
TABLE_NAME
------------------------------
AQ$_ALERT_QT_H
AQ$_ALERT_QT_T
PLAN_TABLE$
AQ$_AQ$_MEM_MC_I
AQ$_AQ$_MEM_MC_G
AQ$_AQ$_MEM_MC_H
AQ$_AQ$_MEM_MC_T
DBMS_APPS_UPG_WORKING
DBMS_UPG_INVALIDATE
DBMS_UPG_CAT_CT$
DBMS_UPG_CAT_CS$
TABLE_NAME
------------------------------
DBMS_UPG_CAT_C0$
STREAMS$_APPLY_SPILL_MSGS_PART
AQ$_KUPC$DATAPUMP_QUETAB_I
AQ$_KUPC$DATAPUMP_QUETAB_G
AQ$_KUPC$DATAPUMP_QUETAB_H
AQ$_KUPC$DATAPUMP_QUETAB_T
IMPDP_STATS
KU$NOEXP_TAB
LOGMNRT_INDCOMPART$
LOGMNRT_INDSUBPART$
LOGMNRT_INDPART$
TABLE_NAME
------------------------------
LOGMNRT_LOBFRAG$
LOGMNRT_ICOL$
LOGMNRT_CCOL$
LOGMNRT_CDEF$
LOGMNRT_LOB$
LOGMNRT_ATTRIBUTE$
LOGMNRT_COLTYPE$
LOGMNRT_TYPE$
LOGMNRT_TABCOMPART$
LOGMNRT_TABSUBPART$
LOGMNRT_TABPART$
TABLE_NAME
------------------------------
LOGMNRT_USER$
LOGMNRT_IND$
LOGMNRT_TS$
LOGMNRT_ATTRCOL$
LOGMNRT_COL$
LOGMNRT_TAB$
LOGMNRT_OBJ$
LOGMNRT_DICTIONARY$
LOGMNRT_SEED$
AQ$_SCHEDULER$_EVENT_QTAB_I
AQ$_SCHEDULER$_EVENT_QTAB_G
TABLE_NAME
------------------------------
AQ$_SCHEDULER$_EVENT_QTAB_H
AQ$_SCHEDULER$_EVENT_QTAB_T
AQ$_SCHEDULER$_JOBQTAB_I
AQ$_SCHEDULER$_JOBQTAB_G
AQ$_SCHEDULER$_JOBQTAB_H
AQ$_SCHEDULER$_JOBQTAB_T
RULE_SET_ROP$
RULE_SET_IOT$
RULE_SET_PR$
ODCI_WARNINGS$
ODCI_SECOBJ$
TABLE_NAME
------------------------------
WRI$_ADV_ASA_RECO_DATA
PSTUBTBL
CLUSTER_INSTANCES
CLUSTER_NODES
CLUSTER_DATABASES
MAP_OBJECT
ATEMPTAB$
UTL_RECOMP_COMPILED
UTL_RECOMP_SORTED
669 rows selected.
SQL>
SQL>
User_tables structure
SQL>
SQL> describe user_tables;
Name Null? 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)
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)
DROPPED VARCHAR2(3)
SQL>
SQL> select table_name, tablespace_name
2 from user_tables
3 where rownum < 10
4 order by table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CCOL$ SYSTEM
CDEF$ SYSTEM
CON$ SYSTEM
FET$ SYSTEM
FILE$ SYSTEM
PROXY_DATA$ SYSTEM
PROXY_ROLE_DATA$ SYSTEM
TS$ SYSTEM
UNDO$ SYSTEM
9 rows selected.
SQL>
SQL>
SQL>
Verify the creation of the statistics by running the following queries:
SQL>
SQL> SELECT table_name FROM user_tables WHERE last_analyzed IS NULL;
TABLE_NAME
------------------------------
WORKING_EMPLOYEES
TEMP_TABLE
EMPLOYEETEMPTABLE
TMP
PARMS
WORKING_CUSTOMERS
CUST_WITH_VARRAY_TEMP_TABLE
7 rows selected.
SQL>
SQL> SELECT index_name FROM user_indexes WHERE last_analyzed IS NULL;
INDEX_NAME
------------------------------
SYS_C004799
SYS_C008156
HD_CLUSTER_INDEX
3 rows selected.
SQL>