加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

exp/imp 按用户/全库   导表结构

(2011-03-29 17:43:14)
标签：

杂谈

 分类： Oracle学习
exp导出表结构，不导出表数据。

版本是从9.2.0.1 到10.2.0.1，正常情况下，数据从低版本迁移导高版本，一定要用低版本的exp程序导出，否则会有问题。
也就是说这里要用9i的client端导出， 用10g的imp导入，经确认造作步骤没有什么问题。

用exp命令导出表结构，不导出表数据。只需在命令行里加一个参数rows=n即可。表示不导出表数据。
exp username/pwd@sid file=d:\data\bak.dmp owner=(user) rows=n 

scp  xxxx:/tmp/waplat.dmp /tmp

imp username/pwd@sid file=/tmp/exp.exp fromuser=fromuser touser=touser log=/tmp/log.log


exp system/xx  full=y rows=n file=/tmp/full.exp log=/tmp/full.log
imp system/xx  full=y file=/tmp/full.exp ignore=y log=/tmp/full.log
---这个会按照原数据库的文件大小去创建新数据库的文件，所以一定要提前将相关的表空间建好！！！！


[oracle@localhost ~]$ exp mass/xx  owner=(mass) rows=n file=/tmp/mass.exp log=/tmp/mass.log

Export: Release 9.2.0.5.0 - Production on Sat Feb 12 13:55:04 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MASS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MASS
About to export MASS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MASS's tables via Conventional Path ...
. . exporting table                     CDML_BLOCK
. . exporting table              CDML_BLOCK_OBJECT
. . exporting table                  CDML_BOOK_TOP
. . exporting table                   CDML_COMMENT
. . exporting table                      CDML_LOGS
. . exporting table             CDML_MESSAGE_BOARD
. . exporting table                   CDML_MO_LOGS
. . exporting table                 CDML_OPERATION
. . exporting table        CDML_OPERATION_TEMPLATE
. . exporting table                      CDML_PAGE
. . exporting table                CDML_PAGE_BLOCK
. . exporting table             CDML_PAGE_TEMPLATE
. . exporting table                   CDML_PROJECT
. . exporting table                   CDML_SERVICE
. . exporting table                 CDML_SMS_ORDER
. . exporting table               CDML_TEMP_MOBILE
. . exporting table                      CDML_USER
. . exporting table                  CDML_USER_PAY
. . exporting table                      LOG_MINER
. . exporting table                       LOG_TEST
. . exporting table                   MASS_ADAPTER
. . exporting table              MASS_ADAPTER_TYPE
. . exporting table                     MASS_ADMIN
. . exporting table          MASS_ADMIN_BACKGROUND
. . exporting table          MASS_ADMIN_COOPERATOR
. . exporting table                      MASS_AREA
. . exporting table               MASS_AUTHOR_BOOK
. . exporting table                MASS_BACKGROUND
. . exporting table                     MASS_BLOCK
. . exporting table                MASS_BLOCK_TYPE
. . exporting table             MASS_BOOKID_FILTER
. . exporting table                   MASS_BOOKPIC
. . exporting table                 MASS_BOOKSHELF
. . exporting table               MASS_BOOK_AUTHOR
. . exporting table              MASS_BOOK_CHAPTER
. . exporting table          MASS_BOOK_CHAPTER_TMP
. . exporting table                MASS_BOOK_CLICK
. . exporting table       MASS_BOOK_CLICK_20090422
. . exporting table              MASS_BOOK_COMMENT
. . exporting table            MASS_BOOK_COPYRIGHT
. . exporting table   MASS_BOOK_COPYRIGHT_RESOURCE
. . exporting table                MASS_BOOK_ORDER
. . exporting table              MASS_BOOK_ORDER_2
. . exporting table            MASS_BOOK_POTENTIAL
. . exporting table                 MASS_BOOK_PUSH
. . exporting table                 MASS_BOOK_SORT
. . exporting table        MASS_BOOK_SORT_RESOURCE
. . exporting table              MASS_BOOK_USER_MN
. . exporting table           MASS_BOOK_USER_MN_V5
. . exporting table          MASS_BOOK_USER_UP_LOG
. . exporting table                  MASS_CATEGORY
. . exporting table         MASS_CATEGORY_RESOURCE
. . exporting table             MASS_CATEGORY_TYPE
. . exporting table            MASS_CENTER_PACKAGE
. . exporting table                   MASS_CHANNEL
. . exporting table           MASS_CHANNEL_JARINFO
. . exporting table            MASS_CHANNEL_ONLINE
. . exporting table         MASS_CHANNEL_PAGE_TYPE
. . exporting table            MASS_COLORSMS_ORDER
. . exporting table                  MASS_DEPT_BAK
. . exporting table                       MASS_FIT
. . exporting table                  MASS_FIT_TYPE
. . exporting table             MASS_FORUM_ARTICLE
. . exporting table               MASS_FORUM_BOARD
. . exporting table              MASS_FORUM_FRIEND
. . exporting table                MASS_FORUM_USER
. . exporting table             MASS_LAST_READ_LOG
. . exporting table             MASS_LEAVE_MESSAGE
. . exporting table          MASS_LEAVE_MESSAGE_V5
. . exporting table                      MASS_LOGS
. . exporting table                   MASS_MESSAGE
. . exporting table             MASS_MESSAGE_BOARD
. . exporting table          MASS_MTKBOOK_DOWNLOAD
. . exporting table       MASS_MTKBOOK_DOWNLOAD_V5
. . exporting table            MASS_MTK_BOOK_CLICK
. . exporting table         MASS_MTK_DOWNLOAD_LOGS
. . exporting table        MASS_MTK_MODEL_RESOURCE
. . exporting table            MASS_MTK_MODEL_TYPE
. . exporting table            MASS_MTK_OTHER_LOGS
. . exporting table             MASS_MTK_PACK_TEMP
. . exporting table               MASS_MTK_VERSION
. . exporting table          MASS_MTK_VISITED_LOGS
. . exporting table       MASS_MTK_VISITED_LOGS_V5
. . exporting table              MASS_PACKAGESHELF
. . exporting table           MASS_PACKAGE_CHANNEL
. . exporting table       MASS_PACKAGE_CHANNEL_END
. . exporting table           MASS_PACKAGE_JARINFO
. . exporting table                 MASS_PACK_BOOK
. . exporting table                      MASS_PAGE
. . exporting table                MASS_PAGE_BLOCK
. . exporting table                 MASS_PAGE_TYPE
. . exporting table                     MASS_PATCH
. . exporting table                MASS_PIC_FORMAT
. . exporting table                  MASS_PIC_SIZE
. . exporting table                 MASS_POPUP_MTK
. . exporting table                   MASS_PRODUCT
. . exporting table          MASS_PRODUCT_PACKAGED
. . exporting table                   MASS_PROJECT
. . exporting table              MASS_RANKING_LIST
. . exporting table         MASS_RANKING_LIST_WEEK
. . exporting table           MASS_READCHAPTER_LOG
. . exporting table        MASS_READ_FEEPOINT_LOGS
. . exporting table                 MASS_RECOMMEND
. . exporting table        MASS_RECOMMEND_RESOURCE
. . exporting table                  MASS_RESOURCE
. . exporting table    MASS_RESOURCE_BACK_20101029
. . exporting table              MASS_RESOURCE_FEE
. . exporting table           MASS_RESOURCE_FEE_V5
. . exporting table          MASS_RESOURCE_PACKAGE
. . exporting table       MASS_RESOURCE_SCORES_LOG
. . exporting table    MASS_RESOURCE_SCORES_LOG_V5
. . exporting table    MASS_RESOURCE_TEMP_20101029
. . exporting table              MASS_RESOURCE_TMP
. . exporting table            MASS_RESOURCE_TMPER
. . exporting table             MASS_RESOURCE_TYPE
. . exporting table           MASS_RESOURCE_UPDATE
. . exporting table   MASS_RSSITEM_USER_UPDATE_LOG
. . exporting table MASS_RSSITEM_USER_UPDATE_LOGV5
. . exporting table              MASS_RSS_CATEGORY
. . exporting table          MASS_RSS_CATEGORY_REL
. . exporting table                  MASS_RSS_FEED
. . exporting table             MASS_RSS_FEED_ITEM
. . exporting table         MASS_RSS_FEED_ITEM_BAK
. . exporting table         MASS_RSS_ITEM_CATEGORY
. . exporting table     MASS_RSS_ITEM_CATEGORY_REL
. . exporting table                   MASS_RSS_MSG
. . exporting table            MASS_RSS_USER_ORDER
. . exporting table        MASS_RSS_USER_ORDER_LOG
. . exporting table     MASS_RSS_USER_ORDER_LOG_V5
. . exporting table         MASS_RSS_USER_ORDER_V5
. . exporting table     MASS_RSS_USER_ORDER_V5_TMP
. . exporting table       MASS_RSS_USER_UPDATE_LOG
. . exporting table    MASS_RSS_USER_UPDATE_LOG_V5
. . exporting table          MASS_RSS_VISITED_LOGS
. . exporting table       MASS_RSS_VISITED_LOGS_V5
. . exporting table                MASS_SEARCH_KEY
. . exporting table    MASS_SENSKYUSER_FEE_CHAPTER
. . exporting table                      MASS_SORT
. . exporting table             MASS_SORT_RESOURCE
. . exporting table                 MASS_SORT_TEMP
. . exporting table                      MASS_TEST
. . exporting table                  MASS_TEST_TMP
. . exporting table                      MASS_USER
. . exporting table            MASS_USER_DOWN_LOGS
. . exporting table             MASS_USER_FEE_LOGS
. . exporting table               MASS_USER_FRIEND
. . exporting table                MASS_USER_ORDER
. . exporting table            MASS_USER_ORDER_LOG
. . exporting table         MASS_USER_ORDER_SINGLE
. . exporting table            MASS_USER_ORDER_TMP
. . exporting table          MASS_USER_PACKAGE_ZIP
. . exporting table                MASS_USER_PHONE
. . exporting table                   MASS_USER_UP
. . exporting table               MASS_VISITED_TAG
. . exporting table               MENU_CHARGE_INFO
. . exporting table                      MENU_INFO
. . exporting table              MENU_MANUFACTURER
. . exporting table                      MENU_MENU
. . exporting table                  MENU_PROVINCE
. . exporting table            MENU_PROVINCE_GROUP
. . exporting table                        MENU_SP
. . exporting table                    MENU_USER_C
. . exporting table             MENU_USER_DOWNLOAD
. . exporting table                    MENU_USER_S
. . exporting table           MLOG$_MASS_BOOKSHELF
. . exporting table           MLOG$_MASS_BOOK_SORT
. . exporting table     MLOG$_MASS_BOOK_SORT_RESOU
. . exporting table        MLOG$_MASS_RESOURCE_TMP
. . exporting table                MLOG$_MASS_SORT
. . exporting table                MLOG$_MASS_USER
. . exporting table          MLOG$_MASS_USER_ORDER
. . exporting table       MLOG$_TBL_BOOK_PARAMETER
. . exporting table                        MMS_KEY
. . exporting table                     PHONE_BOOK
. . exporting table                     PLAN_TABLE
. . exporting table                  PLAYNOW_ADMIN
. . exporting table            PLAYNOW_FROM_MOBILE
. . exporting table                  PLAYNOW_GAMES
. . exporting table                   PLAYNOW_LOGS
. . exporting table              READSCREEN_CHARGE
. . exporting table             READSCREEN_PACKAGE
. . exporting table                 READSCREEN_RES
. . exporting table                READSCREEN_TYPE
. . exporting table                READSCREEN_USER
. . exporting table         READSCREEN_USER_CHARGE
. . exporting table              RESOURCE_BOOK_BAK
. . exporting table          RESOURCE_BOOK_PACKING
. . exporting table           RUPD$_MASS_BOOKSHELF
. . exporting table           RUPD$_MASS_BOOK_SORT
. . exporting table     RUPD$_MASS_BOOK_SORT_RESOU
. . exporting table        RUPD$_MASS_RESOURCE_TMP
. . exporting table                RUPD$_MASS_SORT
. . exporting table                RUPD$_MASS_USER
. . exporting table          RUPD$_MASS_USER_ORDER
. . exporting table       RUPD$_TBL_BOOK_PARAMETER
. . exporting table                     SERVER_APP
. . exporting table                   SERVER_GROUP
. . exporting table           SERVER_MANAGER_GROUP
. . exporting table       SERVER_RESOURCE_REGISTER
. . exporting table                    SERVER_ROLE
. . exporting table                SERVER_ROLE_APP
. . exporting table                    SERVER_USER
. . exporting table          SERVER_USER_ATTRIBUTE
. . exporting table              SERVER_USER_GROUP
. . exporting table               SERVER_USER_ROLE
. . exporting table                 SERVER_WEBMENU
. . exporting table             SERVER_WEBMENU_APP
. . exporting table                    SS_FESTIVAL
. . exporting table               SS_FESTIVAL_TEMP
. . exporting table                        SS_LOGS
. . exporting table                    SS_RESOURCE
. . exporting table                SS_RESOURCE_BAK
. . exporting table       SS_RESOURCE_DOWNLOAD_LOG
. . exporting table               SS_RESOURCE_TYPE
. . exporting table              TAB_MASS_BOOKINFO
. . exporting table            TBL_BOOKSHELF_STATE
. . exporting table                  TBL_BOOK_ARPU
. . exporting table             TBL_BOOK_COPYRIGHT
. . exporting table        TBL_BOOK_COPYRIGHT_TEMP
. . exporting table             TBL_BOOK_DOWN_ARPU
. . exporting table                   TBL_BOOK_FEE
. . exporting table          TBL_BOOK_FEE_PROVINCE
. . exporting table              TBL_BOOK_FEE_STAT
. . exporting table              TBL_BOOK_FEE_TEMP
. . exporting table            TBL_BOOK_FEE_TEMP_1
. . exporting table                TBL_BOOK_FEE_TU
. . exporting table         TBL_BOOK_FREQUENCY_TMP
. . exporting table       TBL_BOOK_FREQUENCY_TMP_2
. . exporting table                TBL_BOOK_LEIJIA
. . exporting table         TBL_BOOK_LEIJIA_090911
. . exporting table           TBL_BOOK_MOBILE_TYPE
. . exporting table        TBL_BOOK_MONTH_READLIST
. . exporting table            TBL_BOOK_MONTH_STAT
. . exporting table            TBL_BOOK_ONLINE_DAY
. . exporting table       TBL_BOOK_ONLINE_DAY_TEMP
. . exporting table           TBL_BOOK_ONLINE_TEMP
. . exporting table             TBL_BOOK_PARAMETER
. . exporting table       TBL_BOOK_READ_DISTRIBUTE
. . exporting table             TBL_BOOK_READ_TEMP
. . exporting table          TBL_BOOK_USER_CHANNEL
. . exporting table         TBL_BOOK_USER_COMEFROM
. . exporting table     TBL_BOOK_USER_COMEFROM_TMP
. . exporting table            TBL_BOOK_USER_EXIST
. . exporting table        TBL_BOOK_USER_FREQUENCY
. . exporting table          TBL_BOOK_USER_KEYWORD
. . exporting table           TBL_BOOK_USER_ONLINE
. . exporting table             TBL_BOOK_USER_READ
. . exporting table         TBL_BOOK_USER_READLIST
. . exporting table TBL_BOOK_USER_READLIST20090417
. . exporting table          TBL_BOOK_USER_STAT103
. . exporting table             TBL_BOOK_USER_TIME
. . exporting table          TBL_BOOK_USER_TIMEDAY
. . exporting table             TBL_CHANGE_CHANNEL
. . exporting table               TBL_CHANNEL_NAME
. . exporting table              TBL_CHANNEL_USERS
. . exporting table             TBL_CHARGE_MESSAGE
. . exporting table              TBL_CUSTTRADE_NEW
. . exporting table                    TBL_DAY_LOG
. . exporting table               TBL_DELETEJARBAK
. . exporting table            TBL_DELETE_FILEPATH
. . exporting table           TBL_DINGWEI_ALLVISIT
. . exporting table                    TBL_JOB_LOG
. . exporting table                         TBL_MO
. . exporting table                TBL_MTK_CONTENT
. . exporting table                 TBL_MTK_INCOME
. . exporting table             TBL_MTK_INCOME_TMP
. . exporting table               TBL_MTK_TYPENAME
. . exporting table               TBL_MTREPORT_LOG
. . exporting table                 TBL_MT_MONITOR
. . exporting table                 TBL_READ_COUNT
. . exporting table               TBL_READ_MESSAGE
. . exporting table             TBL_SEARCH_MESSAGE
. . exporting table                TBL_STAT_RESULT
. . exporting table           TBL_SUNCITY_DAY_STAT
. . exporting table                       TBL_TIME
. . exporting table                   TBL_TIME_DAY
. . exporting table                TBL_USER_BROWSE
. . exporting table                 TBL_USER_COUNT
. . exporting table              TBL_USER_FEE_STAT
. . exporting table                TBL_USER_MOTEMP
. . exporting table              TBL_USER_READTEMP
. . exporting table               THIRDSCREEN_CITY
. . exporting table            THIRDSCREEN_CONTENT
. . exporting table          THIRDSCREEN_PIC_STYLE
. . exporting table          THIRDSCREEN_STOCK_PIC
. . exporting table  THIRDSCREEN_TYPECONTENT_PICID
. . exporting table             THIRDSCREEN_TYPEID
. . exporting table         THIRDSCREEN_TYPEID_PIC
. . exporting table               THIRDSCREEN_USER
. . exporting table        THIRDSCREEN_USER_TYPEID
. . exporting table        THIRDSCREEN_WEATHER_PIC
. . exporting table                 TOOL_MESSYCODE
. . exporting table                T_BOOK2_MESSAGE
. . exporting table                      T_CLWD_QS
. 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.
You have new mail in /var/spool/mail/oracle

[oracle@localhost ~]$ cd /tmp
[oracle@localhost tmp]$ ls
cleanuprc.py  lost+found  mass.exp  mass.log  ssh-ASiME27303  ssh-GFYaP18224  u02radio.tmp
[oracle@localhost tmp]$ ls -l mas*
-rw-r--r--  1 oracle dba 1040384 Feb 12 13:55 mass.exp
-rw-r--r--  1 oracle dba   16230 Feb 12 13:55 mass.log
[oracle@localhost tmp]$ ls -lh mas*
-rw-r--r--  1 oracle dba 1016K Feb 12 13:55 mass.exp
-rw-r--r--  1 oracle dba   16K Feb 12 13:55 mass.log


oracle@Staging /tmp $ scp xx:/tmp/waplat.dmp /tmp
The authenticity of host 'xx' can't be established.
RSA key fingerprint is 46:38:e3:9d:ef:03:45:b4:eb:f4:51:17:d7:57:f8:24.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'xx' (RSA) to the list of known hosts.
waplat.dmp                                                                                        100%  952KB 952.0KB/s   00:01   
oracle@Staging /tmp $

oracle@Staging /tmp $ imp mass/xx file=/tmp/mass.exp fromuser=mass touser=mass log=/tmp/mass.log


oracle@Staging /tmp $ imp waplat/xx  file=/tmp/waplat.dmp fromuser=waplat touser=waplat log=/tmp/waplat.log




oracle@Staging /tmp $ more /tmp/full.log

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
IMP-00015: following statement failed because the object already exists:
 "CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE  '/u02/app/oracle"
 "/oradata/sensky/undotbs01.dbf' SIZE 2000M       AUTOEXTEND ON NEXT 1048576 "
 " MAXSIZE 8000M EXTENT MANAGEMENT LOCAL "
IMP-00015: following statement failed because the object already exists:
 "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE  '/u02/app/oracl"
 "e/oradata/sensky/temp01.dbf' SIZE 53477376       AUTOEXTEND ON NEXT 655360 "
 " MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1048576"
IMP-00017: following statement failed with ORACLE error 1119:
 "CREATE TABLESPACE "CWMLITE" BLOCKSIZE 8192 DATAFILE  '/u02/app/oracle/orada"
 "ta/sensky/cwmlite01.dbf' SIZE 20971520       AUTOEXTEND ON NEXT 655360  MAX"
 "SIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  NOLOGG"
 "ING SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/u02/app/oracle/oradata/sensky/cwmlite01.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
IMP-00017: following statement failed with ORACLE error 1119:
 "CREATE TABLESPACE "DRSYS" BLOCKSIZE 8192 DATAFILE  '/u02/app/oracle/oradata"
 "/sensky/drsys01.dbf' SIZE 20971520       AUTOEXTEND ON NEXT 655360  MAXSIZE"
 " 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  NOLOGGING "
 "SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/u02/app/oracle/oradata/sensky/drsys01.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
IMP-00017: following statement failed with ORACLE error 1119:


oracle@Staging /u02 $ mkdir -p app/oracle/oradata/sensky/

oracle@Staging /u02 $ imp system/gf76tydr full=y file=/tmp/full.exp ignore=y log=/tmp/full.log

Import: Release 10.2.0.1.0 - Production on Tue Mar 29 16:22:26 2011

Copyright (c) 1982, 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

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM



SQL> create tablespace WEB_DATA datafile '/u02/app/oracle/oradata/sensky/WEB_DATA01.dbf' size 200m;
create tablespace WEB_IDX datafile '/u02/app/oracle/oradata/sensky/WEB_IDX01.dbf' size 200m;
create tablespace WEB_TEMP datafile '/u02/app/oracle/oradata/sensky/WEB_TEMP01.dbf' size 200m;
create tablespace WEB_ANALYZE datafile '/u02/app/oracle/oradata/sensky/WEB_ANALYZE01.dbf' size 200m;
create tablespace DEMENTORS_IND datafile '/u02/app/oracle/oradata/sensky/DEMENTORS_IND01.dbf' size 200m;
create tablespace HORCRUX_DATA datafile '/u02/app/oracle/oradata/sensky/HORCRUX_DATA01.dbf' size 200m;

IMP-00017: following statement failed with ORACLE error 10615:
 "CREATE USER "PINSE" IDENTIFIED BY VALUES 'A6F1F33B0B7D6C8F' DEFAULT TABLESP"
 "ACE "WEB_DATA" TEMPORARY TABLESPACE "WEB_TEMP""
IMP-00003: ORACLE error 10615 encountered
ORA-10615: Invalid tablespace type for temporary tablespace
IMP-00017: following statement failed with ORACLE error 10615:

create temporary tablespace WEB_TEMP tempfile '/u02/app/oracle/oradata/sensky/WEB_TEMP01.dbf' size 200m;


/u02/app/oracle/oradata/sensky/cwmlite01.dbf
/u02/app/oracle/oradata/sensky/drsys01.dbf
/u02/app/oracle/oradata/sensky/example01.dbf
/u02/app/oracle/oradata/sensky/indx01.dbf
/u02/app/oracle/oradata/sensky/odm01.dbf

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/sensky/tools01.dbf
/u02/app/oracle/oradata/sensky/xdb01.dbf


alter database datafile '/u02/app/oracle/oradata/sensky/cwmlite01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/drsys01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/example01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/indx01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/odm01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/tools01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/xdb01.dbf' offline drop;

drop tablespace cwmlite including contents and datafiles;
drop tablespace drsys including contents and datafiles;
drop tablespace example including contents and datafiles;
drop tablespace indx including contents and datafiles;
drop tablespace odm including contents and datafiles;
drop tablespace tools including contents and datafiles;
drop tablespace xdb including contents and datafiles;

create tablespace cwmlite datafile '/u02/app/oracle/oradata/sensky/cwmlite01.dbf' size 200m;
create tablespace drsys datafile '/u02/app/oracle/oradata/sensky/drsys01.dbf' size 200m;
create tablespace example datafile '/u02/app/oracle/oradata/sensky/example01.dbf' size 200m;
create tablespace indx datafile '/u02/app/oracle/oradata/sensky/indx01.dbf' size 200m;
create tablespace odm datafile '/u02/app/oracle/oradata/sensky/odm01.dbf' size 200m;
create tablespace tools datafile '/u02/app/oracle/oradata/sensky/tools01.dbf' size 200m;
create tablespace xdb datafile '/u02/app/oracle/oradata/sensky/xdb01.dbf' size 200m;






----误删除数据文件的恢复方法：

oracle@Staging ~ $ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 29 17:27:50 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1677721600 bytes
Fixed Size                  1219760 bytes
Variable Size             402654032 bytes
Database Buffers         1258291200 bytes
Redo Buffers               15556608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u02/app/oracle/oradata/sensky/cwmlite01.dbf'


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     158
Current log sequence           160

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/sensky/system01.dbf
/u02/oradata/sensky/undotbs01.dbf
/u02/oradata/sensky/sysaux01.dbf
/u02/oradata/sensky/users01.dbf
/u02/oradata/sensky/mass_data01.dbf
/u02/oradata/sensky/waplat_data01.dbf
/u02/app/oracle/oradata/sensky/cwmlite01.dbf
/u02/app/oracle/oradata/sensky/drsys01.dbf
/u02/app/oracle/oradata/sensky/example01.dbf
/u02/app/oracle/oradata/sensky/indx01.dbf
/u02/app/oracle/oradata/sensky/odm01.dbf

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/sensky/tools01.dbf
/u02/app/oracle/oradata/sensky/xdb01.dbf
/u02/app/oracle/oradata/sensky/WEB_DATA01.dbf
/u02/app/oracle/oradata/sensky/WEB_IDX01.dbf
/u02/app/oracle/oradata/sensky/WEB_ANALYZE01.dbf
/u02/app/oracle/oradata/sensky/DEMENTORS_IND01.dbf
/u02/app/oracle/oradata/sensky/HORCRUX_DATA01.dbf

18 rows selected.

SQL> alter database datafile '/u02/app/oracle/oradata/sensky/cwmlite01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/drsys01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/example01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/indx01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/odm01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/tools01.dbf' offline drop;
alter database datafile '/u02/app/oracle/oradata/sensky/xdb01.dbf' offline drop;

Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
SQL>
SQL>
SQL>
SQL> alter database open;

Database altered.

SQL> drop tablespace cwmlite including contents and datafiles;
drop tablespace drsys including contents and datafiles;
drop tablespace example including contents and datafiles;
drop tablespace indx including contents and datafiles;
drop tablespace odm including contents and datafiles;
drop tablespace tools including contents and datafiles;
drop tablespace xdb including contents and datafiles;

Tablespace dropped.

SQL>
Tablespace dropped.

SQL>
Tablespace dropped.

SQL>
Tablespace dropped.

SQL>
Tablespace dropped.

SQL>
Tablespace dropped.

SQL>
Tablespace dropped.

SQL> create tablespace cwmlite datafile '/u02/app/oracle/oradata/sensky/cwmlite01.dbf' size 200m;
create tablespace drsys datafile '/u02/app/oracle/oradata/sensky/drsys01.dbf' size 200m;
create tablespace example datafile '/u02/app/oracle/oradata/sensky/example01.dbf' size 200m;
create tablespace indx datafile '/u02/app/oracle/oradata/sensky/indx01.dbf' size 200m;
create tablespace odm datafile '/u02/app/oracle/oradata/sensky/odm01.dbf' size 200m;
create tablespace tools datafile '/u02/app/oracle/oradata/sensky/tools01.dbf' size 200m;
create tablespace xdb datafile '/u02/app/oracle/oradata/sensky/xdb01.dbf' size 200m;


Tablespace created.

喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有