ORACLEデータファイルを指定した表領域にインポート
最近仕事中にORACLEが指定した表領域にデータファイルをインポートしているのを見かけましたが、インポートに成功しました.しかし、この機能は私たちが仕事の中でよく出会う需要と言えるので、ここで記録して、後で使っても探しやすいです.操作フローを直接貼り付けます!
ORACLE中国公式サイト:
http://www.oracle.com/technology/global/cn/index.html
Microsoft Windows [ 6.1.7600]
(c) 2009 Microsoft Corporation。 。
C:\Users\Administrator>sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.3.0 - Production on 8 10 20:53:45 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
-- ， drop
SQL> drop user pccuser cascade;
drop user pccuser cascade
*
1 :
ORA-01918: 'pccUSER'
--
SQL> DESC DBA_DATA_FILES;
?
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES;
FILE_NAME
----------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\OAMIS\USERS01.DBF
USERS
D:\ORACLE\PRODUCT\10.2.0\ORADATA\OAMIS\SYSAUX01.DBF
SYSAUX
D:\ORACLE\PRODUCT\10.2.0\ORADATA\OAMIS\UNDOTBS01.DBF
UNDOTBS1
6 。
--
SQL> CREATE TABLESPACE TEST_pcc_TBS DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\O
AMIS\TEST_pcc_TBS01.DBF' SIZE 100M
2 AUTOEXTEND on NEXT 500M MAXSIZE UNLIMITED
3 LOGGING
4 ONLINE
5 PERMANENT
6 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
7 BLOCKSIZE 8K
8 SEGMENT SPACE MANAGEMENT AUTO;
。
-- ，
SQL> CREATE USER pccUSER IDENTIFIED BY pccUSER DEFAULT TABLESPACE TEST_pcc_TBS;
。
SQL> GRANT CONNECT TO pccUSER;
。
SQL> GRANT RESOURCE TO pccUSER;
。
-- （ ）
SQL> ALTER USER pccUSER QUOTA 0 ON USERS;
。
--
SQL> ALTER USER pccUSER QUOTA UNLIMITED ON TEST_pcc_TBS;
。
--
SQL> CONN pccUSER/pccUSER
。
SQL> SHOW USER;
USER "pccUSER"
SQL> DESC USER_USERS;
?
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> SELECT USERNAME,DEFAULT_TABLESPACE FROM USER_USERS;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
pccUSER TEST_pcc_TBS
SQL>
SQL> exit
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Users\Administrator>d:
-- imp
D:\>imp parfile=imp.txt
Import: Release 10.2.0.3.0 - Production on 8 10 21:13:04 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
EXPORT:V10.02.01
IMP-00013: DBA DBA
IMP-00000:
-- ， dba
-- dba
D:\>sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.3.0 - Production on 8 10 21:14:08 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
-- pccuser dba
SQL> grant dba to pccuser;
。
-- imp
D:\>imp parfile=imp.txt
Import: Release 10.2.0.3.0 - Production on 8 10 21:14:37 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pro
With the Partitioning, OLAP and Data Mining options
EXPORT:V10.02.01
: pcc ,
ZHS16GBK AL16UTF16 NCHAR
ZHS16CGB231280 ( )
. pcc pccUSER
. . "COUNTSUM" 1
. . "PCSS_TF_TJ_TAB" 21868
. . "pcc_ALL_LOG_TAB" 407
. . "pcc_ALL_USER_BAK_TAB" 8407
. . "pcc_ALL_USER_TAB" 2659
. . "pcc_CITY_DESC_TAB" 1
. . "pcc_CLIENT_VER" 1
. . "pcc_DELETE_USERS_BAK_T" 6085
. . "pcc_DEL_ENINFO_BACK_TAB" 36
. . "pcc_EN_FILE_TAB" 2422
. . "pcc_EN_GPUSER_BACK_TAB" 97
. . "pcc_EN_GPUSER_TAB" 4874
. . "pcc_EN_GROUP_BACK_TAB" 1
. . "pcc_EN_GROUP_TAB" 427
. . "pcc_EN_INFO_TAB" 222
. . "pcc_EN_LOGO_TAB" 0
. . "pcc_EN_MNUSER_BACK_TAB" 21
. . "pcc_EN_MNUSER_TAB" 205
. . "pcc_EN_QUERY_GTO" 107
, 。
--
SQL> select table_name,tablespace_name from tabs
TABLE_NAME TABLESPACE_NAME
------------------------------ -----------------
COUNTSUM TEST_pcc_TBS
PCSS_CDR_GTO_GTT_TAB TEST_pcc_TBS
PCSS_CDR_LMQ_TAB TEST_pcc_TBS
PCSS_CDR_OCO_OCT_TAB TEST_pcc_TBS
PCSS_CDR_OFO_OFT_TAB TEST_pcc_TBS
PCSS_CDR_OTO_OTT_TAB TEST_pcc_TBS
PCSS_CDR_UAG_UDG_TAB TEST_pcc_TBS
PCSS_CDR_ULO_ULF_TAB TEST_pcc_TBS
PCSS_TF_TJ_TAB TEST_pcc_TBS
pcc_ALL_LOG_TAB TEST_pcc_TBS
pcc_ALL_USER_BAK_TAB TEST_pcc_TBS
-- dba ， ， ， dba
SQL>CONN / AS SYSDBA;
SQL> revoke dba from pccuser;
。
-- imp.txt ：
userid=pccuser/pccuser--[ ： oracle dba ， pcc dba ； dba ]
buffer=4096000
log=imp.log
file=D:\orcl_db.dump
fromuser=pcc
touser=pccuserORACLE中国公式サイト:
http://www.oracle.com/technology/global/cn/index.html