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
<source lang="sql">
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";
</source>
Create sql statements from user_tables
<source lang="sql">
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.
</source>
Get table name and table space for a table by its name
<source lang="sql">
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>
</source>
Query a table in user_table by table_name
<source lang="sql">
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>
</source>
query table_name, tablespace_name from user_tables
<source lang="sql">
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>
</source>
Query user_tables for the table just created
<source lang="sql">
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.
</source>
To get a listing of all tables that you own
<source lang="sql">
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>
</source>
User_tables structure
<source lang="sql">
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>
</source>
Verify the creation of the statistics by running the following queries:
<source lang="sql">
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>
</source>