oracle accessory,ORACLE数据库逻辑备份、表空间创建及用户授权

最新推荐文章于 2021-04-08 20:18:23 发布

时珠荣

最新推荐文章于 2021-04-08 20:18:23 发布

阅读量177

点赞数

文章标签： oracle accessory

1、Oracle数据库逻辑备份导入及导出：

2012年2月7日导出正式库数据

$exp userid=leopardsnow/leopardsnow@ywdb file=./leopardsnow20120207.dmp log=./leopardsnow20120207.log owner=leopardsnow grants=y

2012年1月13日导出正式库数据

$exp userid=leopardsnow/leopardsnow@ywdb file=./leopardsnow20120113.dmp log=./leopardsnow20120113.log owner=leopardsnow grants=y

2011年6月27日导出正式库数据

$exp userid=leopardsnow/leopardsnow@ywdb file=./leopardsnow20121215.dmp log=./leopardsnow20111215.log owner=leopardsnow grants=y

exp system/system88@ywdb file=./leopardsnow0627.dmp log=./leopardsnow0627.log owner=leopardsnow grants=y

2011年6月19日导出正式库数据

一种方式：

exp system/system88@ywdb file=./ywdb20110619.dmp log=./ywdb20110619.og full=y

二种方式：

exp system/system88@ywdb file=./leopardsnow.dmp log=./leopardsnow.log owner=leopardsnow grants=y

exp system/system88@ywdb file=./shark.dmp log=./shark.log owner=shark grants=y

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

2、创建表空间及用户授权

SQL> create tablespace shark datafile '/opt/oracle/oradata/YWDB/datafile/shark.dbf'

size 500M AutoExtend On Next 10M maxsize unlimited extent management local uniform size 128k

segment space management auto

SQL> create user shark identified by shark

default tablespace shark

temporary tablespace temp;

SQL> grant connect,resource to shark

---------------------------------------------------------------------------------------

SQL> create tablespace dttmp datafile '/opt/oracle/oradata/YWDB/datafile/dttmp.dbf'

size 6000M AutoExtend On Next 100M maxsize unlimited extent management local uniform size 128k

segment space management auto

SQL> create user leopardsnow identified by leopardsnow

default tablespace dttmp

temporary tablespace temp;

SQL> grant connect,resource to leopardsnow

-------------------------------------------------------------------------------

imp system/system88 file=./expfull20110619.dmp log=expfull20110620.log fromuser=(shark,leopardsnow) touser=(shark,leopardsnow)

3、更改用户密码：

SQL>alter user system identified by 要改的密码;

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

imp aichannel/aichannel@hust full=y file= d:\data\newsmgnt.dmp ignore=y

1. 导入一个完整数据库

imp system/manager file=bible_db log=dible_db full=y ignore=y

$exp scott/tiger tables=(emp,dept) file=/directory/scott.dmp grants=y

2 将数据库中的表inner_notify、notify_staff_relat导出

exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

2011年3月27日操作记录：

$exp userid=szfescotest5/szfescotest5@test file=./20110328baseadjust_log.dmp tables=baseadjust_log

$exp userid=szfescotest5/szfescotest5@test file=./20110328base_adjust_new.dmp tables=base_adjust_new

$exp userid=szfescotest5/szfescotest5@test file=./20110328employee_fee_period.dmp tables=employee_fee_period

$exp userid=szfescotest5/szfescotest5@test file=./20110328employee_fee_period_item.dmp tables=employee_fee_period_item

3 将数据库中system用户与sys用户的表导出

$exp system/manager@TEST file=d:\daochu.dmp owner=system grants=y

$exp userid=leopardsnow/leopardsnow@ywdb file=./20110329full.dmp log=./20110329full.log owner=leopardsnow grants=y

测试库用户szfescotest5备份：

$exp szfescotest5/szfescotest5@test file=./20110329full.dmp grants=y

Export: Release 10.2.0.1.0 - Production on Tue Mar 29 13:14:56 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions

EXP-00008: ORACLE error 4063 encountered

ORA-04063: package body "DMSYS.DBMS_DM_IMP_INTERNAL" has errors

ORA-06508: PL/SQL: could not find program unit being called: "DMSYS.DBMS_DM_IMP_INTERNAL"

ORA-06512: at "DMSYS.DBMS_DM_MODEL_EXP", line 303

ORA-06512: at line 1

EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp

. exporting foreign function library names for user LEOPARDSNOW

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user LEOPARDSNOW

About to export LEOPARDSNOW's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export LEOPARDSNOW's tables via Conventional Path ...

. . exporting table AAA 14 rows exported