一次数据导入过程

一。查出原系统有多少表空间：

select a.tablespace_name,total,free,round(free/total*100,2) free_precent,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by tablespace_name;

TABLESPACE_NAME                     TOTAL       FREE FREE_PRECENT       USED
------------------------------ ---------- ---------- ------------ ----------
AUDTOOL                            5120   959.9375        18.75 4160.0625
DBA01_2001                            500   499.9375        99.99     0.0625
DBA01_2002                            500   499.9375        99.99     0.0625
DBA01_2003                            500   499.9375        99.99     0.0625
DBA01_2004                            500   499.9375        99.99     0.0625
DBA01_2005                           1024 1023.9375        99.99     0.0625
DBA01_2006                           1024 1023.9375        99.99     0.0625
DBA01_2007                           1024 1023.9375        99.99     0.0625
DBA01_2008                           1024 1023.9375        99.99     0.0625
DBA01_2009                           1024 1023.9375        99.99     0.0625
DBA01_2010                           1024 1023.9375        99.99     0.0625
DBA01_2011                           1024 1023.9375        99.99     0.0625
INDX                                98816 19484.3125        19.72 79331.6875
SYSAUX                         27080.9375 1126.6875         4.16   25954.25
SYSTEM                               2788 1626.3125        58.33 1161.6875
UNDOTBS2                             3524       3299        93.62        225
USERS                               92160 54093.875         58.7 38066.125

二。查出各表空间数据文件大小

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE FILE_NAME                          SIZE_M
---------- ------------------------------ ----------
USERS      /u02/oradata/orcl/users01.dbf       30720
USERS      /u02/oradata/orcl/users02.dbf       30720
USERS      /u02/oradata/orcl/users03.dbf       30720

SQL>SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2001';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2001 /u02/oradata/orcl/users_2001.dbf 500

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2002';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2002 /u02/oradata/orcl/users_2002.dbf 500

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2003';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2003 /u02/oradata/orcl/users_2003.dbf 500

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2004';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2004 /u02/oradata/orcl/users_2004.dbf 500

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2005';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2005 /u02/oradata/orcl/users_2005.dbf 1024

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2006';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2006 /u02/oradata/orcl/users_2006.dbf 1024

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2007';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2007 /u02/oradata/orcl/users_2007.dbf 1024

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2008';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2008 /u02/oradata/orcl/users_2008.dbf 1024

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2009';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2009 /u02/oradata/orcl/users_2009.dbf 1024

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2010';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2010 /u02/oradata/orcl/users_2010.dbf 1024

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'DBA01_2011';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
DBA01_2011 /u02/oradata/orcl/users_2011.dbf 1024

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'INDX';

TABLESPACE FILE_NAME                                    SIZE_M
---------- ---------------------------------------- ----------
INDX       /u02/oradata/orcl/indx01.dbf                  10240
INDX       /u02/oradata/orcl/indx02.dbf                  20480
INDX       /u02/oradata/orcl/indx03.dbf                  23808
INDX       /u02/oradata/orcl/indx04.dbf                  23808
INDX       /u02/oradata/orcl/indx05.dbf                  20480

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE FILE_NAME                                    SIZE_M
---------- ---------------------------------------- ----------
USERS      /u02/oradata/orcl/users01.dbf                 30720
USERS      /u02/oradata/orcl/users02.dbf                 30720
USERS      /u02/oradata/orcl/users03.dbf                 30720

SQL> SELECT TABLESPACE_NAME ,FILE_NAME, BYTES/1024/1024 SIZE_M from dba_data_files WHERE TABLESPACE_NAME = 'SYSAUX';

TABLESPACE FILE_NAME SIZE_M
---------- ---------------------------------------- ----------
SYSAUX /u02/oradata/orcl/sysaux01.dbf 27080.9375

三.根据以上文件分别建立表空间
1.先扩大本身的user01.dbf的空间

SQL>alter database datafile '/opt/oracle/oradata/orcl/users01.dbf' resize 30G;
2.再增加数据文件

SQL>alter tablespace users add datafile '/opt/oracle/oradata/orcl/users02.dbf' size 30G;

SQL>alter tablespace users add datafile '/opt/oracle/oradata/orcl/users03.dbf' size 30G;

3.创建索引表空间
SQL>create tablespace INDX datafile '/opt/oracle/oradata/orcl/indx01.dbf' size 30G;
SQL>alter tablespace INDX add datafile '/opt/oracle/oradata/orcl/indx02.dbf' size 30G;

SQL>alter tablespace INDX add datafile '/opt/oracle/oradata/orcl/indx03.dbf' size 30G;
其它表空间

create tablespace DBA01_2001 datafile '/opt/oracle/oradata/orcl/users_2001.dbf' size 500M;
create tablespace DBA01_2002 datafile '/opt/oracle/oradata/orcl/users_2002.dbf' size 500M;
create tablespace DBA01_2003 datafile '/opt/oracle/oradata/orcl/users_2003.dbf' size 500M;
create tablespace DBA01_2004 datafile '/opt/oracle/oradata/orcl/users_2004.dbf' size 500M;
create tablespace DBA01_2005 datafile '/opt/oracle/oradata/orcl/users_2005.dbf' size 500M;
create tablespace DBA01_2006 datafile '/opt/oracle/oradata/orcl/users_2006.dbf' size 500M;
create tablespace DBA01_2007 datafile '/opt/oracle/oradata/orcl/users_2007.dbf' size 500M;
create tablespace DBA01_2008 datafile '/opt/oracle/oradata/orcl/users_2008.dbf' size 500M;
create tablespace DBA01_2009 datafile '/opt/oracle/oradata/orcl/users_2009.dbf' size 500M;
create tablespace DBA01_2010 datafile '/opt/oracle/oradata/orcl/users_2010.dbf' size 500M;
create tablespace DBA01_2011 datafile '/opt/oracle/oradata/orcl/users_2011.dbf' size 500M;

建立目录：以SYS管理登录
sql> create directory expdir as '/opt/oracle/oradata/orcl';
一。授权用户
sql> grant EXP_FULL_DATABASE to orauser
sql> grant IMP_FULL_DATABASE to orauser

注意：
针对大数据库导入时，遇到了由于db_recovery_file_dest_size=4G (太小)，导致不能写日志，导入过程停在那里了。
通过
SQL> alter system set db_recovery_file_dest_size =50G scope=both来设置。--调大

在linux命令窗口以 oracle用户登录
导入

# impdp orauser/password directory=expdir dumpfile=data.dmp logfile=exp.log full=y

#单张表。如果表已经存在则要先删除
  impdp 用户名/密码 TABLES= DIRECTORY=expdir  DUMPFILE=data.dmp

导出：
# expdp orauser/password directory=expdir compression=ALL dumpfile=data.dmp full=y logfile=exp.log

posted on 2012-10-24 15:58 gdufo 阅读(506) 评论(0) 编辑收藏所属分类: Database (oracle, sqlser,MYSQL)

gdufo

一次数据导入过程

导航

统计

常用链接

留言簿(6)

随笔分类

随笔档案

文章分类

文章档案

收藏夹

Hibernate

友情链接

搜索

最新评论

阅读排行榜

评论排行榜