資料遷移：Oracle表空間的基本操作

Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the 

SYSTEM

 tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The 

SYSTEM

  tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the 

SYSTEM

 tablespace or take it offline.

The 

SYSAUX

 tablespace, which acts as an auxiliary tablespace to the 

SYSTEM

 tablespace, is also always created when you create a database. It contains the schemasused by various Oracle products and features, so that those products do not require their own tablespaces. As for the 

SYSTEM

 tablespace, management of the

SYSAUX

 tablespace requires a higher level of security and you cannot rename or drop it. The management of the 

SYSAUX

 tablespace is discussed separately in"Managing the SYSAUX Tablespace"

To create a new tablespace, use the SQL statement  

CREATE TABLESPACE

 or 

CREATE TEMPORARY TABLESPACE

. You must have the 

CREATE TABLESPACE

  system privilege to create a tablespace. Later, you can use the 

ALTER TABLESPACE

 or 

ALTER DATABASE

 statements to alter the tablespace. You must have the 

ALTER TABLESPACE

 or

ALTER DATABASE

 system privilege, correspondingly. ---

U se the 

CREATE UNDO TABLESPACE

 statement to create a special type of tablespace called an  undo tablespace.

• 建立表空間,具體參考oracle 官方文檔

CREATE TABLESPACE index_new DATAFILE '+data' SIZE 256M  autoextend on next 1M maxsize 4G  extent management local;        

• 檢視表空間

select t.name,d.name from v$tablespace t,v$datafile d where t.ts#=d.ts#；
           

• 生成建立表空間的腳本

SQL> spool C:\get_ddl_tablespace.sql
SQL> SELECT 'Select dbms_metadata.get_ddl('
  2    ||chr(39)
  3    || 'TABLESPACE'
  4    ||chr(39)
  5    ||','
  6    ||chr(39)
  7    || TABLESPACE_NAME
  8    ||chr(39)
  9    || ')  from dual; '
 10  FROM DBA_TABLESPACES
 11  where tablespace_name not in ('SYSTEM','TEMP','UNDOTBS1','SYSAUX','USERS','
UNDOTBS2')
 12  ;
           

        查詢得到的腳本修改後在新環境執行。

"
  CREATE TABLESPACE "USERS_NEW" DATAFILE 
  '+DATA/purple/datafile/users.277.809429767' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
 "
 "
  CREATE TABLESPACE "USERS_NEW1" DATAFILE 
  '+DATA/purple/datafile/users_new.285.809448141' SIZE 104857600
  AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
 "
 
 "
  CREATE TABLESPACE "USERS_NEW2" DATAFILE 
  '+DATA/purple/datafile/users_new2.286.809448145' SIZE 104857600
  AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
 "
           

PS：各個版本資料庫建立庫執行個體後表空間、使用者、元件

版本	元件/select comp_name,version,status from DBA_REGISTRY/v$option	表空間	使用者/select username from DBA_USERS
8I	Partitioning TRUE Objects TRUE Parallel Server FALSE Advanced replication TRUE Bit-mapped indexes TRUE Connection multiplexing TRUE Connection pooling TRUE Database queuing TRUE Incremental backup and recovery TRUE Instead-of triggers TRUE Parallel backup and recovery TRUE Parallel execution TRUE Parallel load TRUE Point-in-time tablespace recovery TRUE Fine-grained access control TRUE N-Tier authentication/authorization TRUE Function-based indexes TRUE Plan Stability TRUE Online Index Build TRUE Coalesce Index TRUE Managed Standby TRUE Materialized view rewrite TRUE Materialized view warehouse refresh TRUE Database resource manager TRUE Spatial TRUE Visual Information Retrieval TRUE Export transportable tablespaces TRUE Transparent Application Failover TRUE Fast-Start Fault Recovery TRUE Sample Scan TRUE Duplexed backups TRUE Java FALSE OLAP Window Functions TRUE	SYSTEM RBS USERS TEMP TOOLS INDX DRSYS	SYS SYSTEM OUTLN DBSNMP ORDPLUGINS AURORA$JIS$UTILITY$ OSE$HTTP$ADMIN AURORA$ORB$UNAUTHENTICATED ORDSYS MDSYS CTXSYS
9I
10G	10.2.0.4.0 Spatial VALID 10.2.0.4.0 Oracle interMedia VALID 10.2.0.4.0 OLAP Catalog VALID 10.2.0.4.0 Oracle Enterprise Manager VALID 10.2.0.4.0 Oracle XML Database VALID 10.2.0.4.0 Oracle Text VALID 10.2.0.4.0 Oracle Expression Filter VALID 10.2.0.4.0 Oracle Rules Manager VALID 10.2.0.4.3 Oracle Workspace Manager VALID 10.2.0.4.0 Oracle Data Mining VALID 10.2.0.4.0 Oracle Database Catalog Views VALID 10.2.0.4.0 Oracle Database Packages and Types VALID 10.2.0.4.0 JServer JAVA Virtual Machine VALID 10.2.0.4.0 Oracle XDK VALID 10.2.0.4.0 Oracle Database Java Packages VALID 10.2.0.4.0 OLAP Analytic Workspace VALID 10.2.0.4.0 Oracle OLAP API VALID 10.2.0.4.0 Oracle Real Application Clusters VALID	SYSTEM UNDOTBS1 SYSAUX USERS TEMP UNDOTBS2	SYS SYSTEM OUTLN MGMT_VIEW MDSYS ORDSYS CTXSYS ANONYMOUS EXFSYS DMSYS DBSNMP WMSYS SYSMAN XDB ORDPLUGINS SI_INFORMTN_SCHEMA OLAPSYS MDDATA DIP SCOTT ORACLE_OCM TSMSYS
11G	OWB 11.2.0.1.0 VALID Oracle Application Express 3.2.1.00.10 VALID Oracle Enterprise Manager 11.2.0.1.0 VALID OLAP Catalog 11.2.0.1.0 VALID Spatial 11.2.0.1.0 VALID Oracle Multimedia 11.2.0.1.0 VALID Oracle XML Database 11.2.0.1.0 VALID Oracle Text 11.2.0.1.0 VALID Oracle Expression Filter 11.2.0.1.0 VALID Oracle Rules Manager 11.2.0.1.0 VALID Oracle Workspace Manager 11.2.0.1.0 VALID Oracle Database Catalog Views 11.2.0.1.0 VALID Oracle Database Packages and Types 11.2.0.1.0 VALID JServer JAVA Virtual Machine 11.2.0.1.0 VALID Oracle XDK 11.2.0.1.0 VALID Oracle Database Java Packages 11.2.0.1.0 VALID OLAP Analytic Workspace 11.2.0.1.0 VALID Oracle OLAP API 11.2.0.1.0 VALID	SYSTEM SYSAUX UNDOTBS1 USERS TEMP	SYS SYSTEM OUTLN MGMT_VIEW FLOWS_FILES MDSYS ORDSYS EXFSYS DBSNMP WMSYS APPQOSSYS APEX_030200 OWBSYS_AUDIT ORDDATA CTXSYS ANONYMOUS SYSMAN XDB ORDPLUGINS OWBSYS SI_INFORMTN_SCHEMA OLAPSYS SCOTT ORACLE_OCM XS$NULL MDDATA DIP APEX_PUBLIC_USER SPATIAL_CSW_ADMIN_USR SPATIAL_WFS_ADMIN_USR