_银子

提供更专注、更专心、更专业的服务
  首页  :: 联系 :: 订阅 订阅  :: 管理

Oracle表空间用户数据导出

Posted on 2012-04-06 12:12  _银子  阅读(11275)  评论(0)  编辑  收藏  举报

oracle表空间简介   
Oracle表空间是数据管理的基本方法，所有用户的对象要存放在表空间中，也就是用户有空间的使用权，才能创建用户对象。否则是不充许创建对象，因为就是想创建对象，如表，索引等，也没有地方存放，Oracle会提示:没有存储配额。因此，在创建对象之前，首先要分配存储空间。分配存储，就要创建表空间：

Oracle可以创建的表空间有三种类型:
(1)temporary: 临时表空间，用于临时数据的存放;创建临时表空间的语法如下:create temporary tablespace test_temp ......
(2)undo: 还原表空间。用于存入重做日志文件。创建还原表空间的语法如下:create undo tablespace test_undo ......
(3)用户（数据）表空间: 最重要，也是用于存放用户数据表空间可以直接写成: create tablespace test_data .......
temporaty 和 undo 表空间是Oracle 管理的特殊的表空间。只用于存放系统相关数据。

查看表空间

Sql代码 复制代码 收藏代码
  1. select tablespace_name,file_id,bytes,file_name from dba_data_files  
select tablespace_name,file_id,bytes,file_name from dba_data_files



创建（数据）表空间
（创建表空间stu_data，datafile指定数据表空间的存储文件的位置，用来存放数据的文件）

Sql代码 复制代码 收藏代码
  1. --第一种   
  2. create tablespace stu_data      
  3. logging   
  4. datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'       
  5. size 32m       
  6. autoextend on       
  7. next 32m    
  8. maxsize 2048m       
  9. extent management local;   
--第一种
create tablespace stu_data   
logging
datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'    
size 32m    
autoextend on    
next 32m 
maxsize 2048m    
extent management local;



创建表空间参数讲解
1、logging
有 nologging和 logging两个选项，nologging:创建表空间时，不创建重做日志。logging和nologging正好相反， 就是在创建表空间时生成重做日志。用nologging时，好处在于创建时不用生成日志，这样表空间的创建较快，但是没能日志，数据丢失后，不能恢复，但是一般我们在创建表空间时，是没有数据的，按通常的做法，是建完表空间，并导入数据后，是要对数据做备份的，所以通常不需要表空间的创建日志，因此，在创建表空间时，选择nologging，以加快表空间的创建速度。

2、datafile 用于指定数据文件的具体位置和大小。
如: datafile 'D:\Oracle\ORADATA\ORA92\LUNTAN.ora' SIZE 5M 说明文件的存放位置是'D:\Oracle\ORADATA\ORA92\LUNTAN.ora' ，文件的大小为5M。如果有多个文件，可以用逗号隔开:
datafile 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M,  'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M
但是每个文件都需要指明大小。单位以指定的单位为准,如 5M 或 500K。对具体的文件，可以根据不同的需要，存放大不同的介质上，如磁盘阵列，以减少IO竟争。指定文件名时，必须为绝对地址，不能使用相对地址。

3、extent management local：存储区管理方法
在Oracle 8i以前，可以有两种选择，一种是在字典中管理(dictionary)，另一种是本地管理(local)，从9I开始，只能是本地管理方式。因为local管理方式有很多优点。在字典中管理(dictionary): 将数据文件中的每一个存储单元做为一条记录，所以在做DM操作时，就会产生大量的对这个管理表的Delete和Update操作。做大量数据管理时，将会产生很多的DM操作，严得的影响性能，同时，长时间对表数据的操作，会产生很多的磁盘碎片，这就是为什么要做磁盘整理的原因。本地管理(local): 用二进制的方式管理磁盘，有很高的效率，同进能最大限度的使用磁盘。 同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。

4、segment space management：磁盘扩展管理方法:
  ◆segment space management: 使用该选项时区大小由系统自动确定。由于 Oracle 可确定各区的最佳大小，所以区大小是可变的。
  ◆uniform segment space management:指定区大小，也可使用默认值 (1 MB)。

5、段空间的管理方式:
  ◆auto: 只能使用在本地管理的表空间中。 使用LOCAL管理Oracle表空间时，数据块中的空闲空间增加或减少后，其新状态都会在位图中反映出来。位图使 Oracle 管理空闲空间的行为更加自动化，并为管理空闲空间提供了更好的性，但对含有LOB字段的表不能自动管理。
  ◆nanual: 目前已不用，主要是为向后兼容。

Sql代码 复制代码 收藏代码
  1. --第二种   
  2. create tablespace stu_data    
  3. datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'       
  4. size 800M     
  5. autoextend on     
  6. next 50M     
  7. maxsize unlimited     
  8. --maxsize unlimited 是大小不受限制   
--第二种
create tablespace stu_data 
datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'    
size 800M  
autoextend on  
next 50M  
maxsize unlimited  
--maxsize unlimited 是大小不受限制



Sql代码 复制代码 收藏代码
  1. --第三种   
  2. create tablespace stu_data    
  3. datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'       
  4. size 800M     
  5. autoextend on     
  6. next 50M     
  7. maxsize 1000M   
  8. extent management local uniform;     
  9. --unform表示区的大小相同，默认为1M     
--第三种
create tablespace stu_data 
datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'    
size 800M  
autoextend on  
next 50M  
maxsize 1000M
extent management local uniform;  
--unform表示区的大小相同，默认为1M



Sql代码 复制代码 收藏代码
  1. --第四种   
  2. create tablespace stu_data    
  3. datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'       
  4. size 800M     
  5. autoextend on     
  6. next 50M    
  7. maxsize 1000M    
  8. extent management local uniform size 500K;     
  9. --unform size 500K表示区的大小相同，为500K   
--第四种
create tablespace stu_data 
datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'    
size 800M  
autoextend on  
next 50M 
maxsize 1000M 
extent management local uniform size 500K;  
--unform size 500K表示区的大小相同，为500K



Sql代码 复制代码 收藏代码
  1. --第五种   
  2. create tablespace stu_data    
  3. datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'       
  4. size 800M     
  5. autoextend on     
  6. next 50M   
  7. maxsize 1000M     
  8. extent management local autoallocate;     
  9. --autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区   
--第五种
create tablespace stu_data 
datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'    
size 800M  
autoextend on  
next 50M
maxsize 1000M  
extent management local autoallocate;  
--autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区



Sql代码 复制代码 收藏代码
  1. --第六种   
  2. create tablespace stu_data    
  3. datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'       
  4. size 800M     
  5. autoextend on     
  6. next 50M     
  7. maxsize 1000M     
  8. temporary;     
  9. --temporary创建字典管理临时表空间   
--第六种
create tablespace stu_data 
datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'    
size 800M  
autoextend on  
next 50M  
maxsize 1000M  
temporary;  
--temporary创建字典管理临时表空间



为表空间增加数据文件

Sql代码 复制代码 收藏代码
  1. alter tablespace stu_data add     
  2. datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'       
  3. size 800M     
  4. autoextend on    
  5. next 50M     
  6. maxsize 1000M;  
alter tablespace stu_data add  
datafile 'D:\oracle\product\10.2.0\oradata\orcl\stu_data.dbf'    
size 800M  
autoextend on 
next 50M  
maxsize 1000M;



创建临时表空间
（创建临时表空间stu_temp，tempfile指定临时表空间的存储文件的位置，是临时文件）

Sql代码 复制代码 收藏代码
  1. --创建方式和创建数据表空间相同，只是把datafile改为tempfile   
  2. create temporary tablespace stu_temp       
  3. tempfile 'D:\oracle\product\10.2.0\oradata\orcl\stu_temp01.dbf'       
  4. size 32m       
  5. autoextend on       
  6. next 32m maxsize 2048m       
  7. extent management local;  
--创建方式和创建数据表空间相同，只是把datafile改为tempfile
create temporary tablespace stu_temp    
tempfile 'D:\oracle\product\10.2.0\oradata\orcl\stu_temp01.dbf'    
size 32m    
autoextend on    
next 32m maxsize 2048m    
extent management local;



创建用户 并指定表空间
（创建用户stu_test，密码为abcdef，指定其默认表空间为stu_data，临时表空间为stu_temp）

Sql代码 复制代码 收藏代码
  1. create user stu_test identified by abcdef       
  2. default tablespace stu_data      
  3. temporary tablespace stu_temp;   
create user stu_test identified by abcdef    
default tablespace stu_data   
temporary tablespace stu_temp;



给用户授权

Sql代码 复制代码 收藏代码
  1. grant connect,resource to stu_test;  
grant connect,resource to stu_test;



用户常用权限：connect resource dba exp_full_database imp_full_database

改变用户的默认表空间

Sql代码 复制代码 收藏代码
  1. alter user stu_test default tablespace stu_data;    
alter user stu_test default tablespace stu_data;




删除用户以及用户所有的对象

Sql代码 复制代码 收藏代码
  1. drop user stu_test cascade;   
  2. --cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数  
drop user stu_test cascade;
--cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数




删除表空间
前提：删除表空间之前要确认该表空间没有被其他用户使用之后再做删除

Sql代码 复制代码 收藏代码
  1. drop tablespace stu_data including contents and datafiles cascade onstraints;   
  2. --including contents 删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉，所以习惯性的加此参数   
  3. --including datafiles 删除表空间中的数据文件    
  4. --cascade constraints 同时删除tablespace中表的外键参照  
drop tablespace stu_data including contents and datafiles cascade onstraints;
--including contents 删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉，所以习惯性的加此参数
--including datafiles 删除表空间中的数据文件 
--cascade constraints 同时删除tablespace中表的外键参照




如果删除表空间之前删除了表空间文件，解决办法:

如果在清除表空间之前，先删除了表空间对应的数据文件，会造成数据库无法正常启动和关闭。
可使用如下方法恢复（此方法已经在oracle9i中验证通过）：
下面的过程中，filename是已经被删除的数据文件，如果有多个，则需要多次执行；tablespace_name是相应的表空间的名称。

Sql代码 复制代码 收藏代码
  1. $ sqlplus /nolog   
  2. SQL> conn / as sysdba;   
  3. --如果数据库已经启动，则需要先执行下面这行：   
  4. SQL> shutdown abort   
  5. SQL> startup mount    
  6. SQL> alter database datafile 'filename' offline drop;    
  7. SQL> alter database open;    
  8. SQL> drop tablespace tablespace_name including contents;  
$ sqlplus /nolog
SQL> conn / as sysdba;
--如果数据库已经启动，则需要先执行下面这行：
SQL> shutdown abort
SQL> startup mount 
SQL> alter database datafile 'filename' offline drop; 
SQL> alter database open; 
SQL> drop tablespace tablespace_name including contents;




oracle数据库的导入导出命令：

Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件， imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用

导出数据库文件（开始--运行--cmd）
1、用户名为stu_test，密码为abcdef，数据库为orcl，file=.....为导出后存放位置

Sql代码 复制代码 收藏代码
  1. exp stu_test/abcdef@orcl file=d:/a.dmp  
exp stu_test/abcdef@orcl file=d:/a.dmp


2、将数据库中system用户与sys用户的表导出

Sql代码 复制代码 收藏代码
  1. exp system/system@orcl file=d:/a.dmp owner=(system,sys)  
exp system/system@orcl file=d:/a.dmp owner=(system,sys)


3、将数据库中的表t1、t2导出

Sql代码 复制代码 收藏代码
  1. exp zyna/zyna@orcl file=d:/a.dmp tables=(t1,t2)  
exp zyna/zyna@orcl file=d:/a.dmp tables=(t1,t2)

4、将数据库中的表table1中的字段filed1以"00"打头的数据导出

Sql代码 复制代码 收藏代码
  1. exp system/system@orcl file=d:/a.dmp tables=(table1) query=" where filed1 like '00%'"  
exp system/system@orcl file=d:/a.dmp tables=(table1) query=" where filed1 like '00%'"



导入数据库文件(开始--运行--cmd )
1、用户名是stu_test，密码是abcdef，数据库是orclfile=....是要导入的数据库文件的存放位置

Sql代码 复制代码 收藏代码
  1. imp stu_test/abcdef@orcl file=d:/a.dmp full=y  
imp stu_test/abcdef@orcl file=d:/a.dmp full=y

 

Sql代码 复制代码 收藏代码
  1. imp stu_test/abcdef@orcl file=d:/a.dmp full=y ignore=y   
  2. --上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。   
  3. --在后面加上 ignore=y 就可以了。  
imp stu_test/abcdef@orcl file=d:/a.dmp full=y ignore=y
--上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
--在后面加上 ignore=y 就可以了。


2、将d:/a.dmp中的表table1 导入

Sql代码 复制代码 收藏代码
  1. imp system/system@orcl file=d:/a.dmp tables=(table1)  
imp system/system@orcl file=d:/a.dmp tables=(table1)


注意：用户要有导入导出权限