TABLESPACE 扩展,SYNONYM,EXP/IMP,EXPDP/IMPDP,ORA-39006,ORA-39213
1.tablespace
|
----------------------------------目录-------------------------
oracle@h1 product]$ cd $ORACLE_HOME
[oracle@h1 dbhome_2]$ pwd /app/oracle/product/11.2.0/dbhome_2 [oracle@h1 dbhome_2]$ cd /app [oracle@h1 app]$ ls oracle oraInventory [oracle@h1 app]$ cd oracle [oracle@h1 oracle]$ ls admin cfgtoollogs checkpoints diag flash_recovery_area oradata product [oracle@h1 oracle]$ cd product/ [oracle@h1 product]$ ls 11.2.0 [oracle@h1 product]$ cd .. [oracle@h1 oracle]$ ls admin cfgtoollogs checkpoints diag flash_recovery_area oradata product [oracle@h1 oracle]$ cd oradata [oracle@h1 oradata]$ ls orcl [oracle@h1 oradata]$ cd orcl [oracle@h1 orcl]$ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@h1 orcl]$
[oracle@h1 orcl]$ cd /app/oracle/product/
[oracle@h1 product]$ mkdir data [oracle@h1 product]$ chmod 777 data
----------------------------------------------------------------------------------------
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS@ORCL AS SYSDBA SQL> create tablespace ts1 datafile 'E:\app\cswggod\oradata\orcl\ts1.dbf' size 50M; Tablespace created SQL> create user user1 identified by abc; create user user1 identified by abc ORA-01920: user name 'USER1' conflicts with another user or role name SQL> grant dba to user1; Grant succeeded ------------------1.修改用户默认表空间----------------------------- SQL> alter user user1 default tablespace ts1; User altered
------------------2.修改表空间大小(1)修改表空间
SQL> alter tablespace ts1 add datafile 'E:\app\cswggod\oradata\orcl\ts1_1.dbf' size 50M; Tablespace altered SQL>
------------------3.修改表空间大小(2)修改数据库
SQL> create tablespace ts2 datafile 'E:\app\cswggod\oradata\orcl\ts2.dbf' size 50M;
Tablespace created SQL> alter tablespace ts2 datafile 'E:\app\cswggod\oradata\orcl\ts2.dbf' resize 100M; alter tablespace ts2 datafile 'E:\app\cswggod\oradata\orcl\ts2.dbf' resize 100M ORA-02142: missing or invalid ALTER TABLESPACE option SQL> alter database datafile 'E:\app\cswggod\oradata\orcl\ts2.dbf' resize 100M; Database altered SQL> |
2.SYNONYM 同义词
|
-----------------------1.create synonym
SQL> create table t1(id int,name varchar(30) );
Table created
SQL> insert all
2 into t1 values(2,'java') 3 into t1 values(1,'oracle') 4 select * from dual; 2 rows inserted SQL> rename t1 to sale_product; Table renamed SQL> commit; Commit complete SQL> select * from sale_product; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle
SQL> create synonym t1 for user1.sale_product;
Synonym created SQL> select * from t1; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle SQL> insert all 2 into t1 values(3,'linux') 3 select * from dual; 1 row inserted SQL> select * from t1; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux SQL> commit; Commit complete SQL> select * from t1; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux SQL>
------------------------2.create view--------------
-------------------------*******视图一定以原物理表建立，不要像以下方法以同义词建立视图
SQL> create view v_t1 as select * from user1.sale_product;
View created SQL> select * from v_t1; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux SQL> insert into v_t1 values(4,'MCITP'); 1 row inserted SQL> select * from v_t1; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux 4 MCITP SQL> commit; Commit complete SQL> select * from v_t1; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux 4 MCITP SQL>
------------------------------3.public synonym
--------------synonym，其他用户访问要授权
SQL> conn user2/abc@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as user2@orcl SQL> select * from user1.t1; select * from user1.t1 ORA-00942: table or view does not exist//无权限访问
SQL> conn user1/abc@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as user1@orcl SQL> grant select,insert,update on t1 to user2; Grant succeeded
SQL> conn user2/abc@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as user2@orcl SQL> select * from user1.t1; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux 4 MCITP SQL> -----------------授权后可以访问
-----------------------------public synonym
-----------------------------------所有用户可以访问的synonym
SQL> conn user1/abc@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as user1@orcl SQL> create public synonym pt1 for user1.t1; Synonym created SQL> conn user2/abc@orcl Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as user2@orcl
SQL> select * from pt1;
ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux 4 MCITP SQL>
-----------------------------
SQL> show user;
User is "user2" SQL> conn user1/abc@orcl Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as user1@orcl SQL> create public synonym pt1_1 for user1.v_t1; Synonym created SQL>
-------------------
select * from all_synonyms where owner='USER1';
select * from all_synonyms where owner='PUBLIC' and synonym_name in ('PT1','PT1_1');
--------------------------------3.drop synonym
SQL> show user
User is "user1" SQL> drop synonym t1; Synonym dropped SQL>
----------------------------4.drop pubilc synonym
SQL> select * from all_synonyms where SYNONYM_name in ('PT1');
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------- PUBLIC PT1 USER1 T1 SQL>
SQL> drop public synonym pt1;
Synonym dropped SQL> select * from all_synonyms where SYNONYM_name in ('PT1'); OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------- SQL> |
3.关于session 的select
| ---=======================session1
SQL> show user
User is "user1"
SQL> insert into sale_product values(10,'SAP');
1 row inserted
SQL> select * from sale_product;
ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux 4 MCITP 10 SAP ---=======================session2,----------------session1 不提交
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS@ORCL AS SYSDBA SQL> select * from user1.sale_product; SQL> select * from user1.sale_product; ID NAME --------------------------------------- ------------------------------ 2 java 1 oracle 3 linux 4 MCITP SQL> -------------------session2 未变化--- |
4.exp、imp
|
------------------------1.test1
SQL> show user
User is "user1" SQL> create table t2 (id int); Table created SQL> insert all into t2 values(1) into t2 values(2) select * from dual; 2 rows inserted SQL> commit; Commit complete SQL> exit select * from all_tables where owner in ('USER1','USER2')  windows dos命令
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\陈>exp user1/abc@orcl tables=sale_product,t2 file='F:\cswggod_Documents
\Documents\1.dmp'
Export: Release 11.2.0.1.0 - Production on Sat Nov 3 13:17:14 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SALE_PRODUCT 4 rows exported . . exporting table T2 2 rows exported Export terminated successfully without warnings.
C:\Users\陈>
------------------------------------------------
SQL> show user
User is "user1" SQL> drop table t2; Table dropped SQL>
--------------------------------
windows dos命令，导出部分用户的部分表
C:\Users\陈>imp user1/abc file='F:\cswggod_Documents\Documents\1.dmp' fromuser=U
SER1 tables=t2 full=n
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 13:31:31 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set . importing USER1's objects into USER1 . importing USER1's objects into USER1 . . importing table "T2" 2 rows imported Import terminated successfully without warnings.
-----------------------------------------------------------
SQL> select * from t2;
ID --------------------------------------- 1 2 SQL> ---------------------------------------------------------------------
-------------------------------------2.test2: fromuser touser
F:\cswggod_Documents\Documents>exp user2/abc@orcl tables=t1 file='F:\cswggod_Doc
uments\Documents\1.dmp' ;
Export: Release 11.2.0.1.0 - Production on Sat Nov 3 13:49:11 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T1 1 rows exported Export terminated successfully without warnings.
----------------------------------------------------------------------------------------------
F:\cswggod_Documents\Documents>imp user1/abc file='F:\cswggod_Documents\Document
s\1.dmp' fromuser=USER2 touser=USER1 tables=t1
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 13:52:37 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by USER2, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing USER2's objects into USER1 . . importing table "T1" 1 rows imported Import terminated successfully without warnings. --------------------------------------3.OWNER全部内容导出
F:\cswggod_Documents\Documents>exp user1/abc@orcl file=F:\cswggod_Documents\Docu
ments\user1.dmp owner=USER1
Export: Release 11.2.0.1.0 - Production on Sat Nov 3 14:17:26 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions . exporting foreign function library names for user USER1 . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user USER1 About to export USER1's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export USER1's tables via Conventional Path ... . . exporting table SALE_PRODUCT 4 rows exported . . exporting table T1 1 rows exported . . exporting table T2 2 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. ------------------
C:\Users\陈>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 3 14:25:14 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user user1 cascade;
User dropped.
SQL>
------------------------------ SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr oduction With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\陈>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 3 14:31:58 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user user1 identified by abc;
User created.
SQL> grant dba to user1;
Grant succeeded.
SQL> exit
-------------------------- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr oduction With the Partitioning, OLAP, Data Mining and Real Application Testing options --=====================下例导入错误，导入到DBA user:cswggod中 -----------=================没有fromuser,touser 指定；默认指定为登陆用户
F:\cswggod_Documents\Documents>imp cswggod/198543 file='F:\cswggod_Documents\Do
uments\user1.dmp' full=Y
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 14:33:29 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produ tion With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by USER1, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
----------------------------------------------下例导入成功导入到DBA user:USER1中 . importing USER1's objects into CSWGGOD . . importing table "SALE_PRODUCT" 4 rows imported . . importing table "T1" 1 rows imported . . importing table "T2" 2 rows imported IMP-00041: Warning: object created with compilation warnings "CREATE FORCE VIEW "CSWGGOD"."V_T1" ("ID","NAME") AS" " " "select "ID","NAME" from user1.sale_product" Import terminated successfully with warnings.
F:\cswggod_Documents\Documents>imp cswggod/198543 file='F:\cswggod_Documents\Doc
uments\user1.dmp' FROMUSER=USER1 TOUSER=USER1
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 14:38:59 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by USER1, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing USER1's objects into USER1 . . importing table "SALE_PRODUCT" 4 rows imported . . importing table "T1" 1 rows imported . . importing table "T2" 2 rows imported Import terminated successfully without warnings. ------------------------- select * from all_objects where owner='USER1';  --------------------------------------4.dmp 文件查看
F:\cswggod_Documents\Documents>imp cswggod/198543 file='F:\cswggod_Documents\Doc
uments\user1.dmp' FROMUSER=USER1 TOUSER=USER1 show=Y
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 14:43:29 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by USER1, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing USER1's objects into USER1 "BEGIN " "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','" "CURRENT_SCHEMA'), export_db_name=>'ORCL', inst_scn=>'2116657');" "COMMIT; END;" "ALTER SESSION SET CURRENT_SCHEMA= "USER1"" "CREATE TABLE "SALE_PRODUCT" ("ID" NUMBER(*,0), "NAME" VARCHAR2(30)) PCTFRE" "E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 " "MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE " ""TS1" LOGGING NOCOMPRESS" . . skipping table "SALE_PRODUCT"
"GRANT INSERT ON "SALE_PRODUCT" TO "USER2""
"GRANT SELECT ON "SALE_PRODUCT" TO "USER2"" "GRANT UPDATE ON "SALE_PRODUCT" TO "USER2"" "ALTER SESSION SET CURRENT_SCHEMA= "USER1"" "CREATE TABLE "T1" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT" "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI" "ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS" . . skipping table "T1"
"ALTER SESSION SET CURRENT_SCHEMA= "USER1""
"CREATE TABLE "T2" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT" "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI" "ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS1" LOGGING NOCOMPRESS" . . skipping table "T2"
"ALTER SESSION SET CURRENT_SCHEMA= "USER1""
"CREATE FORCE VIEW "USER1"."V_T1" ("ID","NAME") AS" " " "select "ID","NAME" from user1.sale_product" Import terminated successfully without warnings. ------------------------------5.exp database ---------------导出该schmea下的object ------------exp ‘userid/password@instance as sysdba’以dba权限备份
F:\cswggod_Documents\Documents>exp 'sys/oracle@orcl as SYSDBA' file='F:\cswggod_
Documents\Documents\full.dmp' full=Y
…………………………..但导出数据后，导入其他用户会失败，建议不要使用
. exporting dimensions
---------------------------------------------------------------------------- . exporting post-schema procedural objects and actions . exporting user history table . exporting default and system auditing options . exporting statistics Export terminated successfully without warnings.
F:\cswggod_Documents\Documents>exp
system/oracle@orcl file='F:\cswggod_
Documents\Documents\full.dmp' full=Y log=full.log
…………………………..
. exporting dimensions
. exporting post-schema procedural objects and actions . exporting user history table . exporting default and system auditing options . exporting statistics Export terminated successfully without warnings. -------------------------------------------------------------------------------
SQL> conn sys/oracle@orcl as sysdba
Connected. SQL> drop user user1;
User dropped.
---------------------------------------------
F:\cswggod_Documents\Documents>imp system/oracle file='F:\cswggod_Documents\Docu
ments\full.dmp' full=Y log=full.log
-----------恢复成功
SQL> select * from user1.t1;
ID
---------- 1 |
5.expdp
|
--------------------1.directory 建立
SQL> show user
USER is "SYS" SQL>
SQL> create directory db_dir as 'F:\cswggod_Documents\Documents\DB_backup';
Directory created.
SQL> grant read,write on directory db_dir to cswggod;
Grant succeeded.
-----------------------------------2.exp 启动
F:\cswggod_Documents\Documents>expdp user1/abc@orcl schemas=USER1 directory=db_d
ir dumpfile=user01.dmp logfile=user01.log
Export: Release 11.2.0.1.0 - Production on Sat Nov 3 16:56:35 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39006: internal error ORA-39213: Metadata processing is not available /*--------------------------------------------------------------------------------------
Metalink确认是一个又来已经的已知问题，可以尝试执行如下步骤解决：
connect / as sysdba
---------------------------------------------------------------------------------------*/
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\陈>sqlplus "/as SYSDBA"
SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 3 19:06:08 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> execute sys.dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
SQL>exit
--------------------3.导出导入schemas
F:\cswggod_Documents\Documents>expdp user1/abc@orcl schemas=USER1 directory=db_d
ir dumpfile=user01.dmp logfile=user01.log
Export: Release 11.2.0.1.0 - Production on Sat Nov 3 19:31:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "USER1"."SYS_EXPORT_SCHEMA_01": user1/********@orcl schemas=USER1 dire ctory=db_dir dumpfile=user01.dmp logfile=user01.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 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/VIEW/VIEW . . exported "USER1"."SALE_PRODUCT" 5.468 KB 4 rows . . exported "USER1"."T1" 5.015 KB 1 rows . . exported "USER1"."T2" 5.023 KB 2 rows Master table "USER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for USER1.SYS_EXPORT_SCHEMA_01 is: F:\CSWGGOD_DOCUMENTS\DOCUMENTS\DB_BACKUP\USER01.DMP Job "USER1"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:32:41
------------------------------------------------------------------------------------------
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS@ORCL AS SYSDBA SQL> drop user user1 cascade; User dropped SQL>
F:\cswggod_Documents\Documents>impdp system/oracle@orcl schemas=USER1 directory=
db_dir dumpfile=user01.dmp logfile=user01.log
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 20:47:52 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@orcl schemas=USER1 di rectory=db_dir dumpfile=user01.dmp logfile=user01.log Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE 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 "USER1"."SALE_PRODUCT" 5.468 KB 4 rows . . imported "USER1"."T1" 5.015 KB 1 rows . . imported "USER1"."T2" 5.023 KB 2 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/VIEW/VIEW Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 20:47:57
----------------------------------------impdp 导出SQL
F:\cswggod_Documents\Documents>impdp system/oracle@orcl schemas=USER1 directory=
db_dir dumpfile=user01.dmp logfile=user01.log SQLFILE=1.sql
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 20:38:00 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/********@orcl schemas=USER1 directory=db_dir dumpfile=user01.dmp logfile=user01.log SQLFILE=1.sql Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/VIEW/VIEW Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 20:38:07
--------------------------导出1.sql
---------------------------4.导出tablespace
SQL> show user
USER is "SYS" SQL> create user user3 identified by abc;
User created.
SQL> grant dba to user3;
Grant succeeded.
SQL> create tablespace ts3 datafile 'E:\app\cswggod\oradata\orcl\ts3.dbf' size
10M;
Tablespace created.
SQL> conn user3/abc
Connected. SQL> create table emp as select * from hr.employees;
Table created.
SQL> create table dep as select * from hr.departments;
Table created.
SQL>
========================导出tablespace ts3
F:\cswggod_Documents\Documents>expdp system/oracle@orcl tablespaces=ts3 director
y=db_dir dumpfile=ts3.dmp logfile=ts3a.log
Export: Release 11.2.0.1.0 - Production on Sat Nov 3 21:42:37 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/********@orcl tablespaces= ts3 directory=db_dir dumpfile=ts3.dmp logfile=ts3a.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "USER3"."DEP" 7 KB 27 rows . . exported "USER3"."EMP" 16.79 KB 107 rows Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is: F:\CSWGGOD_DOCUMENTS\DOCUMENTS\DB_BACKUP\TS3.DMP Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 21:42:47
==========================================
SQL> conn system/oracle
Connected. SQL> drop user user3 cascade;
User dropped.
SQL>
==========================================
SQL> show user
USER is "SYSTEM" SQL> create user user3 identified by abc;
User created.
SQL> grant dba to user3;
Grant succeeded.
===========================================
F:\cswggod_Documents\Documents>impdp system/oracle@orcl tablespaces=ts3 director
y=db_dir dumpfile=ts3.dmp logfile=ts3b.log
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 21:54:21 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/********@orcl tablespaces= ts3 directory=db_dir dumpfile=ts3.dmp logfile=ts3b.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "USER3"."DEP" 7 KB 27 rows . . imported "USER3"."EMP" 16.79 KB 107 rows Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at 21:54:24
---------------------------5.导出tables
F:\cswggod_Documents\Documents>expdp system/oracle@orcl tables=user3.emp,user1.t
1 directory=db_dir dumpfile=table.dmp logfile=table.log
Export: Release 11.2.0.1.0 - Production on Sat Nov 3 22:02:28 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@orcl tables=user3.emp, user1.t1 directory=db_dir dumpfile=table.dmp logfile=table.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "USER1"."T1" 5.015 KB 1 rows . . exported "USER3"."EMP" 16.79 KB 107 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: F:\CSWGGOD_DOCUMENTS\DOCUMENTS\DB_BACKUP\TABLE.DMP Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 22:02:35
==========================
SQL> show user
USER is "SYSTEM" SQL> drop table user3.emp;
Table dropped.
SQL> drop table user1.t1;
Table dropped.
========================
F:\cswggod_Documents\Documents>impdp system/oracle@orcl tables=user3.emp,user1.t
1 directory=db_dir dumpfile=table.dmp logfile=table1.log
Import: Release 11.2.0.1.0 - Production on Sat Nov 3 22:14:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@orcl tables=user3.emp, user1.t1 directory=db_dir dumpfile=table.dmp logfile=table1.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "USER1"."T1" 5.015 KB 1 rows . . imported "USER3"."EMP" 16.79 KB 107 rows Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 22:14:07 |
转载于:https://blog.51cto.com/cswggod/1049417