SQL> descr v$sysaux_occupants
Name Null? Type
----------------------------------------- -------- ----------------------------
OCCUPANT_NAME VARCHAR2(64)
OCCUPANT_DESC VARCHAR2(64)
SCHEMA_NAME VARCHAR2(64)
MOVE_PROCEDURE VARCHAR2(64)
MOVE_PROCEDURE_DESC VARCHAR2(64)
SPACE_USAGE_KBYTES NUMBER
SQL> select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants;
OCCUPANT_NAME SCHEMA_NAME SPACE_USAGE_KBYTES
---------------------- ----------------------------------------- ------------------
LOGMNR SYSTEM 6080
LOGSTDBY SYSTEM 896
STREAMS SYS 512
XDB XDB 0
AO SYS 768
XSOQHIST SYS 768
XSAMD OLAPSYS 0
SM/AWR SYS 111168
SM/ADVISOR SYS 16256
SM/OPTSTAT SYS 149312
SM/OTHER SYS 20864
STATSPACK PERFSTAT 0
ODM DMSYS 0
SDO MDSYS 0
WM WMSYS 7040
ORDIM ORDSYS 0
ORDIM/PLUGINS ORDPLUGINS 0
ORDIM/SQLMM SI_INFORMTN_SCHEMA 0
EM SYSMAN 81728
TEXT CTXSYS 0
ULTRASEARCH WKSYS 0
ULTRASEARCH_DEMO_USER WK_TEST 0
EXPRESSION_FILTER EXFSYS 1856
EM_MONITORING_USER DBSNMP 1600
TSM TSMSYS 256
JOB_SCHEDULER SYS 384
SQL> select occupant_name, move_procedure from v$sysaux_occupants;
OCCUPANT_NAME MOVE_PROCEDURE
----------------- ---------------------------------------------------------------------------------
LOGMNR SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY SYS.DBMS_LOGSTDBY.SET_TABLESPACE
STREAMS XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO DBMS_AW.MOVE_AWMETA
XSOQHIST DBMS_XSOQ.OlapiMoveProc
XSAMD DBMS_AMD.Move_OLAP_Catalog
SM/AWR
SM/ADVISOR SM/OPTSTAT
SM/OTHER STATSPACK
ODM MOVE_ODM
SDO MDSYS.MOVE_SDO
WM DBMS_WM.move_proc
ORDIM ORDIM/PLUGINS
ORDIM/SQLMM emd_maintenance.move_em_tblspc
TEXT DRI_MOVE_CTXSYS
ULTRASEARCH MOVE_WK
ULTRASEARCH_DEMO_USER MOVE_WK
OCCUPANT_NAME MOVE_PROCEDURE
EXPRESSION_FILTER
EM_MONITORING_USER
TSM
JOB_SCHEDULER
http://13821zrh:7777/pls/apex/f?p=109:5:1798445824213355::NO&P4_DB_LINK=CZAPPPR1
http://13821zrh:7777/pls/apex/f?p=109:5:1798445824213355:::::
SQL> descr SYS.DBMS_LOGMNR_D
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN DEFAULT
DICTIONARY_LOCATION VARCHAR2 IN DEFAULT
OPTIONS NUMBER IN DEFAULT
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN DEFAULT
DICTIONARY_LOCATION VARCHAR2 IN DEFAULT
OPTIONS NUMBER IN DEFAULT
LOW_SCN NUMBER OUT
PROCEDURE SET_TABLESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NEW_TABLESPACE VARCHAR2 IN
execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE('Create_a_posta_per_lm');
SQL> select rowid from billingprod.db_info;
ROWID
------------------
AAAhNCAAEAAAI7+AAA
AAAhNCAAEAAAI7+AAB
AAAhNCAAEAAAI7+AAC
AAAhNCAAEAAAI7+AAD
AAAhNCAAEAAAI7+AAE
AAAhNCAAEAAAI7+AAF
AAAhNCAAEAAAI7+AAG
AAAhNCAAEAAAI7+AAH
AAAhNCAAEAAAI7+AAI
AAAhNCAAEAAAI7+AAJ
AAAhNCAAEAAAI7+AAK
AAAhNC AAE AAAI7+ AAI
AAAhNC AAE AAAI7+ AAJ
AAAhNC AAE AAAI7+ AAK
Data_object_number data_file block row
SQL> create bigfile tablespace this_is_big datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARKAPEX\very_big.dbf' size 128M
2 extent management local;
Tablespace created.
SQL> alter tablespace this_is_big add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARKAPEX\very_big_2.dbf' size 128M;
alter tablespace this_is_big add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARKAPEX\very_big_2.dbf' size 128M
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
SQL> create table hr.blin_blan (c1 number) tablespace this_is_big;
Table created.
SQL> insert into hr.blin_blan values(234);
1 row created.
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select rowid from hr.blin_blan;
ROWID
------------------
AAAi2FAAAAAAAAUAAA
AAAi2F AAAAAAAAU AAA
SQL> select rowid from hr.blin_blan;
ROWID
------------------
AAAi2F AAAAAAAAU AAA
AAAi2F AAAAAAAAU AAB
AAAi2F AAAAAAAAU AAC
Elapsed: 00:00:00.32
SQL> descr v$tablespace
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
SQL> SELECT ts#, bigfile FROM v$tablespace;
TS# BIG
---------- ---
0 NO
1 NO
2 NO
4 NO
3 NO
6 NO
8 NO
9 NO
10 YES
SQL> r
1 select * from database_properties
2* order by 1
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------- ----------------------------------------
DBTIMEZONE +01:00 DB time zone
DEFAULT_PERMANENT_TABLESPACE SYSTEM Name of default permanent tablespace
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DICT.BASE 2 dictionary base tables version #
EXPORT_VIEWS_VERSION 8 Export views revision #
GLOBAL_DB_NAME CZAPPPR1.WORLD Global database name
NLS_CALENDAR GREGORIAN Calendar system
NLS_CHARACTERSET WE8ISO8859P1 Character set
NLS_COMP BINARY NLS comparison
NLS_CURRENCY # Local currency
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE ENGLISH Date language
NLS_DUAL_CURRENCY ? Dual currency symbol
NLS_ISO_CURRENCY UNITED KINGDOM ISO currency
NLS_LANGUAGE ENGLISH Language
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
NLS_SORT BINARY Linguistic definition
NLS_TERRITORY UNITED KINGDOM Territory
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF Time stamp format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR Timestamp with timezone format
NLS_TIME_FORMAT HH24.MI.SSXFF Time format
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR Time with timezone format
27 rows selected.
SQL> create temporary tablespace temp2 tablespace group tempgpr temporary file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARKAPEX\temp2_01.db
f' size 128M;
create temporary tablespace temp2 tablespace group tempgpr temporary file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARKAPEX\temp2_01.dbf' si
ze 128M
*
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
SQL> create temporary tablespace temp2 tablespace group tempgpr tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARKAPEX\temp2_01.dbf' siz
e 128M;
Tablespace created.
SQL> select table_name from dict where table_name like 'DBA%GROU%';
TABLE_NAME
------------------------------
DBA_LOG_GROUPS
DBA_LOG_GROUP_COLUMNS
DBA_TABLESPACE_GROUPS
DBA_RSRC_CONSUMER_GROUPS
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_GROUP_MAPPINGS
DBA_RGROUP
DBA_SCHEDULER_WINDOW_GROUPS
DBA_SCHEDULER_WINGROUP_MEMBERS
DBA_POLICY_GROUPS
DBA_REGISTERED_SNAPSHOT_GROUPS
DBA_REGISTERED_MVIEW_GROUPS
DBA_REPGROUP_PRIVILEGES
DBA_REPGROUP
DBA_REPPRIORITY_GROUP
DBA_REPCOLUMN_GROUP
DBA_REPGROUPED_COLUMN
DBA_TEMPLATE_REFGROUPS
DBA_FILE_GROUPS
DBA_FILE_GROUP_VERSIONS
DBA_FILE_GROUP_EXPORT_INFO
DBA_FILE_GROUP_FILES
DBA_FILE_GROUP_TABLESPACES
DBA_FILE_GROUP_TABLES
24 rows selected.
SQL> descr DBA_TABLESPACE_GROUPS
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME NOT NULL VARCHAR2(30)
SQL> select * from DBA_TABLESPACE_GROUPS;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMPGPR TEMP2
SQL> alter database default tablespace THIS_IS_BIG;
SQL> SELECT property_name, property_value FROM database_properties;
PROPERTY_NAME PROPERTY_VALUE
--------------------------------------------------------------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE THIS_IS_BIG
DEFAULT_TBS_TYPE SMALLFILE
Oracle9i also provides three types of partitioned indexes:
- Local Indexes: A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.
- Global Partitioned Indexes: A global partitioned index is an index on a partitioned or non-partitioned table which is partitioned using a different partitioning-key from the table. Global-partitioned indexes can only be partitioned using range partitioning. For example, a table could be range-partitioned by month and have twelve partitions, while an index on that table could be range-partitioned using a different partitioning key and have a different number of partitions.
- Global Non-Partitioned Indexes: A global non-partitioned index is essentially identical to an index on a non-partitioned table. The index structure is not partitioned.