今天在研究了一下關(guān)于ORACLE的導(dǎo)入導(dǎo)出的功能，周五快要下班的時(shí)候給同事新建一個(gè)表空間，將同一個(gè)數(shù)據(jù)庫(kù)中的某個(gè)用戶下的表導(dǎo)入新的表空間上，建好表空間和用戶后，直接用 exp ,imp 來(lái)導(dǎo)入數(shù)據(jù)到新的表空間，查是發(fā)現(xiàn)后來(lái)導(dǎo)入的數(shù)據(jù)還在原來(lái)的表空間上，（周五那天的我是用工具編輯DMP文件，修改里的表空間名后導(dǎo)入的），后來(lái)想起來(lái)了eygle的書上寫了關(guān)于這個(gè)的問(wèn)題，于是今天就來(lái)試驗(yàn)了一下。
第一種：修改用戶的一些權(quán)限。
首先使用sytem帳戶登陸
--創(chuàng)建新表空間的用戶
SQL> create user pangzi identified by pangzi default tablespace pangzi temporary tablespace temp;
用戶已創(chuàng)建。
--授給新用戶一般的權(quán)限
SQL> grant export full database to pangzi;
授權(quán)成功。
SQL> grant import full database to pangzi;
授權(quán)成功。
SQL> grant connect,resource to pangzi;
授權(quán)成功。
SQL> grant create procedure to pangzi;
授權(quán)成功。
SQL> grant create job to pangzi;
授權(quán)成功。
SQL> grant create view to pangzi;
授權(quán)成功。
SQL> grant create synonym to pangzi;
授權(quán)成功。
--從這里開始將是增加的，為了使導(dǎo)入的數(shù)據(jù)，不放在原來(lái)的表空間上
SQL> grant dba to pangzi;
--收回用戶的umlimited tablespace權(quán)限
SQL> revoke unlimited tablespace from pangzi;
--設(shè)置新創(chuàng)建的用戶可使用syb表空間的大小0（原數(shù)據(jù)所在的表空間為syb）
SQL> alter user pangzi quota 0 on syb;
--設(shè)置新創(chuàng)建的用戶可使用pangzi表空間的大小不限制
SQL> alter user pangzi quota unlimited on pangzi;
--收回DBA權(quán)限
SQL> revoke dba from pangzi;
執(zhí)行導(dǎo)入數(shù)據(jù)
C:\Users\dyspangzi>imp pangzi/pangzi@testdev file=syb.dmp full=y
Import: Release 10.2.0.3.0 - Production on 星期六 4月 21 17:48:54 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
經(jīng)由常規(guī)路徑由 EXPORT:V10.02.01 創(chuàng)建的導(dǎo)出文件
警告: 這些對(duì)象由 SYB 導(dǎo)出, 而不是當(dāng)前用戶
已經(jīng)完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的導(dǎo)入
. 正在將 SYB 的對(duì)象導(dǎo)入到 PANGZI
. . 正在導(dǎo)入表 "ALL_SALES"導(dǎo)入了 360 行
. . 正在導(dǎo)入表 "COUPONS"導(dǎo)入了 6 行
. . 正在導(dǎo)入表 "CUSTOMERS"導(dǎo)入了 5 行
. .中間部分內(nèi)容省略，都是導(dǎo)入的信息
. . 正在導(dǎo)入表 "REG_EXPS"導(dǎo)入了 1 行
. . 正在導(dǎo)入表 "SALARY_GRADES"導(dǎo)入了 4 行
即將啟用約束條件...
成功終止導(dǎo)入, 沒(méi)有出現(xiàn)警告。
導(dǎo)入成功，我們看導(dǎo)的表已經(jīng)在新的表空間中了
SQL> show user
USER 為 "PANGZI"
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DYSPANGZI PANGZI
CUSTOMERS PANGZI
PRODUCT_TYPES PANGZI
PRODUCTS PANGZI
PURCHASES PANGZI
EMPLOYEES PANGZI
SALARY_GRADES PANGZI
PURCHASES_WITH_TIMESTAMP PANGZI
PURCHASES_TIMESTAMP_WITH_TZ PANGZI
PURCHASES_WITH_LOCAL_TZ PANGZI
COUPONS PANGZI
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PROMOTIONS PANGZI
ORDER_STATUS PANGZI
PRODUCT_CHANGES PANGZI
MORE_PRODUCTS PANGZI
MORE_EMPLOYEES PANGZI
DIVISIONS PANGZI
JOBS PANGZI
EMPLOYEES2 PANGZI
ALL_SALES PANGZI
PRODUCT_PRICE_AUDIT PANGZI
REG_EXPS PANGZI
已選擇22行。
第二種：使用EXPDP和IMPDP來(lái)導(dǎo)入和導(dǎo)出
使用expdp來(lái)導(dǎo)出，首先要為一個(gè)參數(shù)來(lái)指定一個(gè)路徑——directory，expdp是在服務(wù)器端工作，導(dǎo)出的文件需要放在本地目錄，這個(gè)參數(shù)就是保存導(dǎo)出文件的位置。這個(gè)可以自己創(chuàng)建，也可以是默認(rèn)的，我自己創(chuàng)建了一個(gè)，名字是expdir
SQL> CREATE OR REPLACE DIRECTORY expdir AS '/var/backup';
目錄已創(chuàng)建。
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------- ------------------------------- -----------------------------------------
SYS ADMIN_DIR /ade/aime_10.2_lnx_push/oracle/md/admin
SYS DATA_PUMP_DIR /usr/app/oracle/product/10.2.0/db_1/rdbms/log/
SYS WORK_DIR /ade/aime_10.2_lnx_push/oracle/work
SYS EXPDIR /var/backup
--給用戶授予讀寫權(quán)限
SQL> grant read,write on directory expdir to syb;
開始導(dǎo)出數(shù)據(jù)
[oracle@dyspangzi ~]$ expdp dumpfile=syb.dmp directory=expdir;
Export: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:06:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYB"."SYS_EXPORT_SCHEMA_01": dumpfile=syb.dmp directory=expdir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.375 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "SYB"."DYSPANGZI" 48.50 KB 659 rows
. . exported "SYB"."ALL_SALES" 13.68 KB 360 rows
中間導(dǎo)出信息省略
. . exported "SYB"."REG_EXPS" 5.437 KB 1 rows
. . exported "SYB"."SALARY_GRADES" 5.710 KB 4 rows
. . exported "SYB"."PRODUCT_PRICE_AUDIT" 0 KB 0 rows
Master table "SYB"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYB.SYS_EXPORT_SCHEMA_01 is:
/var/backup/syb.dmp
Job "SYB"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:07:11
導(dǎo)出成功后查看一下剛才創(chuàng)建目錄里邊的內(nèi)容
[root@dyspangzi var]# cd backup
[root@dyspangzi backup]# ls -l
總計(jì) 720
-rw-r--r-- 1 oracle oinstall 3472 04-21 18:07 export.log
-rw-r----- 1 oracle oinstall 729088 04-21 18:07 syb.dmp
多了兩個(gè)文件，一個(gè)是日志文件一個(gè)是導(dǎo)出的數(shù)據(jù)文件，在導(dǎo)入的時(shí)候需要這兩個(gè)文件。下面開始導(dǎo)入。
===================================================================================
出現(xiàn)了第一個(gè)錯(cuò)誤
[oracle@dyspangzi ~]$ impdp pangzi/pangzi dumpfile=syb.dmp directory=expdir remap_tablespace=syb:pangzi
Import: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:45:35
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name EXPDIR is invalid
出現(xiàn)了錯(cuò)誤，后來(lái)發(fā)現(xiàn)這個(gè)是因?yàn)樾掠脩魀angzi沒(méi)有對(duì)目錄的讀寫權(quán)限造成的，于是加上權(quán)限
SQL> grant read,write on directory expdir to pangzi;
授權(quán)成功。
==================================================================================
出現(xiàn)了第二個(gè)錯(cuò)誤
[oracle@dyspangzi ~]$ impdp pangzi/pangzi dumpfile=syb.dmp directory=expdir remap_tablespace=syb:pangzi
Import: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:47:38
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "PANGZI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PANGZI"."SYS_IMPORT_FULL_01": pangzi/******** dumpfile=syb.dmp directory=expdir remap_tablespace=syb:pangzi
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "PANGZI"."SYS_IMPORT_FULL_01" successfully completed at 18:47:41
這個(gè)是由于原來(lái)的用戶和現(xiàn)在的不一樣了，加上這個(gè)參數(shù)就好了 remap_schema=syb:pangzi
=====================================================================================================
下面是沒(méi)有任何錯(cuò)誤的導(dǎo)入了
[oracle@dyspangzi ~]$ impdp pangzi/pangzi dumpfile=syb.dmp directory=expdir remap_schema=syb:pangzi remap_tablespace=syb:pangzi
Import: Release 10.2.0.1.0 - Production on Saturday, 21 April, 2012 18:49:24
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "PANGZI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PANGZI"."SYS_IMPORT_FULL_01": pangzi/******** dumpfile=syb.dmp directory=expdir remap_schema=syb:pangzi remap_tablespace=syb:pangzi
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "PANGZI"."DYSPANGZI" 48.50 KB 659 rows
. . imported "PANGZI"."ALL_SALES" 13.68 KB 360 rows
中間部分導(dǎo)入信息省略
. . imported "PANGZI"."PURCHASES_WITH_TIMESTAMP" 5.609 KB 1 rows
. . imported "PANGZI"."REG_EXPS" 5.437 KB 1 rows
. . imported "PANGZI"."SALARY_GRADES" 5.710 KB 4 rows
. . imported "PANGZI"."PRODUCT_PRICE_AUDIT" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "PANGZI"."SYS_IMPORT_FULL_01" successfully completed at 18:49:37
成功導(dǎo)入！！！！
查看默認(rèn)的表空間
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DYSPANGZI PANGZI
CUSTOMERS PANGZI
PRODUCT_TYPES PANGZI
PRODUCTS PANGZI
PURCHASES PANGZI
EMPLOYEES PANGZI
SALARY_GRADES PANGZI
PURCHASES_WITH_TIMESTAMP PANGZI
PURCHASES_TIMESTAMP_WITH_TZ PANGZI
PURCHASES_WITH_LOCAL_TZ PANGZI
COUPONS PANGZI
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PROMOTIONS PANGZI
ORDER_STATUS PANGZI
PRODUCT_CHANGES PANGZI
MORE_PRODUCTS PANGZI
MORE_EMPLOYEES PANGZI
DIVISIONS PANGZI
JOBS PANGZI
EMPLOYEES2 PANGZI
ALL_SALES PANGZI
PRODUCT_PRICE_AUDIT PANGZI
REG_EXPS PANGZI
已選擇22行。
此表空間中沒(méi)有索引，所以第一種方法也沒(méi)有報(bào)錯(cuò)，如果出現(xiàn)有索引的第一種方法還得增加幾步，等我明天找到周五那個(gè)數(shù)據(jù)再來(lái)再著做試驗(yàn)。
-----------------------------------------------------------
今天特意去公司找來(lái)了那天導(dǎo)的數(shù)據(jù)庫(kù)，回來(lái)做實(shí)驗(yàn)。采用第一種方法不可行，雖然說(shuō)可以使用IMP 加indexs參數(shù)來(lái)導(dǎo)出一些索引，但是導(dǎo)入的時(shí)候總有莫名其妙的錯(cuò)誤，估計(jì)還是我沒(méi)弄好，所以為了省事還是直接用第二種吧，方便，無(wú)錯(cuò)。
1.在Oracle中可以用下面兩種：
01:
create table newtable as select * from oldtable;//用于復(fù)制前未創(chuàng)建新表newtable不存在的情況
02:
insert into newtable select * from oldtable;//已經(jīng)創(chuàng)建了新表newtable 的情況
注意：第一種方式只是復(fù)制了表結(jié)構(gòu)，但是主鍵什么的并沒(méi)有復(fù)制進(jìn)去，所以用的時(shí)候要小心在意。
2.如果想簡(jiǎn)單快速的復(fù)制表結(jié)構(gòu)，而不需要oldtable里面的數(shù)據(jù),可以用下面的語(yǔ)句：
create table newtable as select * from oldtable where 1=2;(把數(shù)據(jù)過(guò)濾掉)
3.如過(guò)newtable 和oldtable的表結(jié)構(gòu)不同，可以使用下面的方式:
create table newtable as select s.c1,s.c2 from oldtable s;
4.如果想重新命名newtable的列名：
在oracle中:
create table newtable(id,name1) as select s.c1,s.c2 from oldtable s;
或者
create table newtable as select s.c1 ,s.c2 from oldtable s;
在mysql中恐怕只能用第二種方式了。
5.如果是只需要把一部分的oldtable中的數(shù)據(jù)添加到newtable中。可以這樣：
create table newtable as (select * from oldtable where ...);//加where過(guò)濾條件
6.最常見的情況是id列新表中要用，并且和舊表中的不同，使用下面的語(yǔ)句就可以了（我們可以重新建一個(gè)sequence）
create table yang(id,name) as select hibernate_sequence.nextval,t.ename from emp t;
7.要注意，導(dǎo)出表的時(shí)候不能用select...into語(yǔ)句。