Oracle PL/SQL/System Tables Views/USER TABLES

Материал из SQL эксперт
Перейти к: навигация, поиск

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>