ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 不同用户和不同的default tablespace数据迁移

不同用户和不同的default tablespace数据迁移

原创 Linux操作系统 作者:frog-qs 时间:2011-07-27 14:53:35 0 删除 编辑
     同事在将不同用户的数据导入到其他用户中会提出错误信息,大概说的是表空间明不对。但是经过测试,没出现这种情况。
简单记录下过程:
SQL> create tablespace frog datafile '/opt/oracle/oradata/orcl/frog.dbf'
  2  size 50m extent management local segment space management manual;

Tablespace created.

SQL> create user frog identified by frog default tablespace frog ;

User created.

SQL> grant connect,resource to frog;

Grant succeeded.

SQL> conn frog/frog
Connected.
SQL> create table frog as select * from dba_objects;
create table frog as select * from dba_objects
                                   *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> desc dba_objects;
ERROR:
ORA-04043: object "SYS"."DBA_OBJECTS" does not exist


SQL> conn / as sysdba
Connected.
SQL> grant select on dba_objects to frog;

Grant succeeded.

SQL> conn frog/frog
Connected.
SQL> create table frog as select * from dba_objects;

Table created.

SQL> desc frog
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> create index ind_frog on frog(object_id);

Index created.

SQL> create sequence seq_frog
  2  increment by 1
  3  start with 1
  4  nomaxvalue
  5  nocycle
  6  cache 10;

Sequence created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
FROG                           TABLE

SQL> conn / as sysdba
Connected.
SQL> create tablespace fox datafile '/opt/oracle/oradata/orcl/fox.dbf'
  2  size 50m extent management local segment space management manual;

Tablespace created.

SQL> create user fox identified by fox defalut tablespace fox;
create user fox identified by fox defalut tablespace fox
                                  *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> change defaults
SP2-0023: String not found.
SQL> change /defalut/default
  1* create user fox identified by fox default tablespace fox
SQL> /

User created.

SQL> grant connect,resource to fox;

Grant succeeded.

SQL>



oracle@linux-3or0:~> exp frog/frog file=/opt/oracle/frog.dmp

Export: Release 10.2.0.1.0 - Production on Wed Jul 27 15:19:34 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 done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user FROG
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user FROG
About to export FROG's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export FROG's tables via Conventional Path ...
. . exporting table                           FROG      50318 rows exported
EXP-00091: Exporting questionable statistics.
. 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 with warnings.

oracle@linux-3or0:~> imp fox/fox file=/opt/oracle/frog.dmp fromuser=frog touser=fox
Import: Release 10.2.0.1.0 - Production on Wed Jul 27 15:22:08 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:V10.02.01 via conventional path

Warning: the objects were exported by FROG, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. . importing table                         "FROG"      50318 rows imported
Import terminated successfully without warnings.




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22779291/viewspace-703231/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ase15.5的下载地址
请登录后发表评论 登录
全部评论

注册时间:2009-10-27

  • 博文量
    132
  • 访问量
    243176