数据迁移：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 theSYSAUX 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