91极品女神在线,欧美日韩国产成人在线,中文字幕久久精品一区二区

一次數(shù)據(jù)導(dǎo)入過程

一。查出原系統(tǒng)有多少表空間：

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

二。查出各表空間數(shù)據(jù)文件大小

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

三.根據(jù)以上文件分別建立表空間
1.先擴(kuò)大本身的user01.dbf的空間

SQL>alter database datafile '/opt/oracle/oradata/orcl/users01.dbf' resize 30G;
2.再增加數(shù)據(jù)文件

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.創(chuàng)建索引表空間
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';
一。授權(quán)用戶
sql> grant EXP_FULL_DATABASE to orauser
sql> grant IMP_FULL_DATABASE to orauser

注意：
針對大數(shù)據(jù)庫導(dǎo)入時(shí)，遇到了由于db_recovery_file_dest_size=4G (太小)，導(dǎo)致不能寫日志，導(dǎo)入過程停在那里了。
通過
SQL> alter system set db_recovery_file_dest_size =50G scope=both來設(shè)置。--調(diào)大

在linux命令窗口以 oracle用戶登錄
導(dǎo)入

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

#單張表。如果表已經(jīng)存在則要先刪除
  impdp 用戶名/密碼 TABLES= DIRECTORY=expdir  DUMPFILE=data.dmp

導(dǎo)出：
# expdp orauser/password directory=expdir compression=ALL dumpfile=data.dmp full=y logfile=exp.log

posted on 2012-10-24 15:58 gdufo 閱讀(518) 評論(0) 編輯收藏所屬分類: Database (oracle, sqlser,MYSQL)

gdufo

一次數(shù)據(jù)導(dǎo)入過程

導(dǎo)航

統(tǒng)計(jì)

常用鏈接

留言簿(6)

隨筆分類

隨筆檔案

文章分類

文章檔案

收藏夾

Hibernate

友情鏈接

搜索

最新評論

閱讀排行榜

評論排行榜