Oracle DBA 常用命令

1、ORACLE EXPDP IMPDP命令使用详细相关参数以及导出示例：1. DIRECTORY指定转储文件和日志文件所在的目录DIRECTORY=directory_objectDirectory_object用于指定目录对象名称.需要注意,目录对象是使用CREATE DIRECTORY语句建立的对象,而不是OS目录Expdp scott/tiger DIRECTORY= DMP  DUMPFILE=a.dump create or replace directory dmp  as '

2、d:/dmp' expdp zftang/zftangzftang directory=dmp  dumpfile=test.dmp content=metadata_only 2. CONTENT该选项用于指定要导出的内容.默认值为ALLCONTENT=ALL | DATA_ONLY | METADATA_ONLY当设置CONTENT为ALL时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义 expdp zftang/zftangzftang directory=dm

3、p  dumpfile=test.dmp content=metadata_only-只导出对象定义expdp zftang/zftangzftang directory=dmp  dumpfile=test.dmp content=data_only-导出出所有数据3. DUMPFILE用于指定转储文件的名称,默认名称为expdat.dmpDUMPFILE=directory_object:file_name ,.Directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指定directory_objec

4、t,导出工具会自动使用DIRECTORY选项指定的目录对象expdp zftang/zftangzftang directory=dmp  dumpfile=test1.dmp数据泵工具导出的步骤： 1、创建DIRECTORYcreate directory dir_dp as 'D:/oracle/dir_dp' 2、授权Grant read,write on directory dir_dp to zftang;-查看目录及权限SELECT privilege, directory_name, DIRECTORY_PATH FROM

5、user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;3、执行导出expdp zftang/zftangfgisdb schemas=zftang directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;连接到: Oracle Database 10g Enterprise Edition Release With the Partitioning, OLAP an

6、d Data Mining options启动 "ZFTANG"."SYS_EXPORT_SCHEMA_01":  zftang/*fgisdb schory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;  */备注：   1、directory=dir_dp必须放在前面，如果将其放置最后，会提示 ORA-39002: 操作无效       

7、60;                                                 

8、60;   ORA-39070: 无法打开日志文件。                                           &

9、#160;                 ORA-39087: 目录名 DATA_PUMP_DIR; 无效        2、在导出过程中，DATA DUMP 创建并使用了一个名为SYS_EXPORT_SCHEMA_01的对象，此对象就是DATA DUMP导出过程中所用的JOB名字，如果在执行这个命令时如果没有指定导出的JOB名字那么就

10、会产生一个默认的JOB名字，如果在导出过程中指定JOB名字就为以指定名字出现     如下改成：     expdp zftang/zftangfgisdb schemas=zftang directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;   3、导出语句后面不要有分号，否则如上的导出语句中的job表名为my_job1;，而不是my_job1。因此导致expdp zft

11、ang/zftang attach=zftang.my_job1执行该命令时一直提示找不到job表数据泵导出的各种模式：1、 按表模式导出：expdp zftang/zftangfgisdb  tables=zftang.b$i_exch_info,zftang.b$i_manhole_info dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job2、按查询条件导出：expdp zftang/zftangfgisdb  tables=zftang.b$i

12、_exch_info dumpfile =expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query='"where rownum<11"'3、按表空间导出：Expdp zftang/zftangfgisdb dumpfile=expdp_tablespace.dmp tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job4、导出方案Expdp

13、zftang/zftang DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=zftang,gwm5、导出整个数据库：expdp zftang/zftangfgisdb dumpfile =full.dmp full=y logfile=full.log directory=dir_dp job_name=my_jobimpdp导入模式： 1、按表导入p_street_area.dmp文件中的表，此文件是以gwm用户按schemas=gwm导出的：impdp gwm/gwmfgisdb  dumpfile =p_street_area

14、.dmp logfile=imp_p_street_area.log directory=dir_dp tables=p_street_area job_name=my_job2、按用户导入（可以将用户信息直接导入，即如果用户信息不存在的情况下也可以直接导入）impdp gwm/gwmfgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job3、不通过expdp的步骤生成dmp文件而直接导入的方法：-从源数据库中向目标数据库导入表p_street_area

15、impdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area logfile=p_street_area.log  job_name=my_jobigisdb是目的数据库与源数据的链接名，dir_dp是目的数据库上的目录4、更换表空间  采用remap_tablespace参数   -导出gwm用户下的所有数据expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm注：如果是用sy

16、s用户导出的用户数据，包括用户创建、授权部分，用自身用户导出则不含这些内容-以下是将gwm用户下的数据全部导入到表空间gcomm（原来为gmapdata表空间下）下impdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp remap_tablespace=gmapdata:gcommOracle Purge和drop的区别Purge和drop的区别：Oracle 10g提供的flashback drop 新特性为了加快用户错误操作的恢复，Oracle10g提供了flashback drop的功能。而在以前的版本中，除了不完全恢复，通常

17、没有一个好的解决办法。Oracle 10g的flashback drop功能，允许你从当前数据库中恢复一个被drop了的对象，在执行drop操作时，现在Oracle不是真正删除它，而是将该对象自动将放入回收站。对于一个对象的删除，其实仅仅就是简单的重令名操作。所谓的回收站，是一个虚拟的容器，用于存放所有被删除的对象。在回收站中，被删除的对象将占用创建时的同样的空间，你甚至还可以对已经删除的表查询，也可以利用flashback功能来恢复它，这个就是flashback drop功能。回收站内的相关信息可以从recyclebin/user_recyclebin/dba_recyclebin等视图中获

18、取，或者通过SQL*Plus的show recyclebin 命令查看。C:>sqlplus /nologSQL*Plus: Release .0 - Production on 星期三6月1 10:09:32 2005Copyright (c) 1982, 2004, Oracle.  All rights reserved.SQL> conn tiger/tigerxe已连接。SQL> select count(*) from goodsinfo1;  COUNT(*)-     38997SQL

19、> drop table goodsinfo1;表已删除。SQL> commit;提交完成。SQL> select count(*) from goodsinfo1;select count(*) from goodsinfo1                     *第1 行出现错误:ORA-00942: table or view does not exist 啊

20、！天啊！我删错了表，怎么办好呢？啊！将数据库闪回到刚才删除表前的时间就可以啦。不行！那其它的操作也会一齐闪回。现在可以用flashback drop的功能了。 SQL> show recyclebin;ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME- - - -GOODSINFO1 

21、60;     BIN$RFG58GsfRheKlVKnWw8KKQ=$0 TABLE        2005-06-01:10:11:03 SQL> FLASHBACK TABLE goodsinfo1 TO BEFORE DROP; 闪回完成。 SQL> select count(*) from goodsinfo1;  COUNT(*)-     38997 看看已删除的表回来了

22、。真的谢天谢地啊！ SQL> show recyclebin; 如果想要彻底清除这些对象，可以使用Purge命令，如:SQL> select count(*) from goodsinfo2;  COUNT(*)-     38997SQL> drop table goodsinfo2;表已删除。SQL> commit;提交完成。SQL> show recyclebin;ORIGINAL NAME    RECYCLEBIN NAME  

23、0;             OBJECT TYPE  DROP TIME- - - -GOODSINFO2       BIN$BgSuEWMOSLOGZPcIc97O8w=$0 TABLE        2005-06-01:10:13:18SQL> purge table goodsinfo2;表已清除。SQL> sho

24、w recyclebin;SQL>使用purge recyclebin可以清除回收站中的所有对象。类似的我们可以通过purge user_recyclebin或者是purge dba_recyclebin来清除不同的回收站对象。通过PURGE TABLESPACE TSNAME，PURGE TABLESPACE TSNAME USER USERNAME命令来选择清除回收站。  ps：用户drop后不进recycle bin，所以不用purgeORACLE的Copy命令和create table,insert into的比较在数据表间复制数据是Oracle DBA经常面

25、对的任务之一，Oracle为这一任务提供了多种解决方案，SQL*Plus Copy 命令便是其中之一。SQL*Plus Copy 命令通过SQL*Net在不同的表(同一服务器或是不同服务器)之间复制数据或移动数据。在实际运行环境中若能恰当地选择使用SQL*Plus Copy 命令可以有效地提高数据复制的性能。下面将简要介绍SQL*Plus Copy 命令使用，并在性能方面与其他两种方案进行对比，力求能提供一个使用Copy 命令的方案参考。1.语法及使用说明1.1 语法下面我们来看一下SQL*Copy 命令的语法及使用说明。在解释SQL*Plus Copy 命令的语法之前，我们必须要明确SQL*

26、Plus Copy 命令不是一个方法或是函数，也不是一个SQL语句，它是一个命令(command)，当然这个命令必须在SQL*Plus里运行。SQL*Plus Copy 命令的语法：COPY FROM database | TO database | FROM database TO database APPEND|CREATE|INSERT|REPLACE destination_table (column, column, column, .)USING query我们分部分来解释一下：COPY 这个不太需要解释，主命令，声明要执行COPY操作From Database 源数据库To Da

27、tabase 目标数据库此处注意花括号中有三种可选的写法(以”|”隔开)，如果源数据表和目标数据表在同一个Schema中，则可以只写From Database，也可以只写To Database，当然还可以是第三种写法，把From Database和To Database写全。但如果源数据表和目标数据表不在同一个Schema中，则必须用第三种写法，即把From Database和To Database都写全From Database和To Database的格式是一样的：USERID/PASSWORDSID，这个大家都应该很熟悉了。 网管网www_bitscn_comAPPEND|CREATE|

28、INSERT|REPLACE 声明操作数据的方式，下面分别解释一下：Append 向已有的目标表中追加记录，如果目标表不存在，自动创建，这种情况下和Create等效。Create 创建目标表并且向其中追加记录，如果目标表已经存在，则会返回错误。Insert 向已有的目标表中插入记录，与Append不同的是，如果目标表不存在，不自动创建而是返回错误。Replace 用查询出来的数据覆盖已有的目标表中的数据，如果目标表不存在，自动创建。destination_table 目标表的名字(column, column, column, .) 可以指定目标表中列的名字，如果不指定，则自动使用Query中

29、的列名。USING query 查询语句，交流的数据来自这儿。1.2 使用范例下面我们通过几个例子来看一下SQL*Plus Copy 命令的使用：1.2.1 在同一个服务器的同一个Schema中复制数据：同时指定From database和To databaseSQL> copy from scott/tigerlsj to scott/tigerlsj create dept1 using select * from dept;只指定From Database SQL> copy from scott/tigerlsj&

30、#160;create dept2 using select * from dept; 中国网管联盟bitsCN.com 只指定To Database SQL> copy to scott/tigerlsj create dept3 using select * from dept;1.2.2 在同一个服务器的不同Schema中复制数据：这种情况下必须同时指定From Database和To DatabaseSQL> copy from scott/tigerlsj to lsjdemo/lsjdemolsj&#

31、160;create dept using select * from dept;注意这种情况下，using select * from dept 中并不需要使用scott.demp的形式。1.2.3 在不同的服务器间复制数据：SQL> conn lsj/lsjsunserve已连接。SQL> copy from scott/tigerlsj to lsj/lsjsunserve create dept using select * from dept;2.4 性能实验结果实验数据：记录数：5,082,500数据量：504M实验结果

32、方案-执行时间(秒) -Undo(M) -Redo(M)Copy command -520.51-0 - 592Insert intoselect - 631.64 -345 -1720Create Table- 244.79 -0 -5152.总结Create Tableas select是最快的，而且生成的Undo和Redo信息最少，所以只要可能，请尽量使用这种方案。但这种方案有一定的限制，即目标表必须是不存在的，不能用它向已有的目标表中追加记录。Insert into select 是最慢的，而且生成最多的Undo和Redo信息，对I/O的压力最大，优势在于大家对它比较熟悉，使用起来比较

33、简单，适合于处理少量的数据，若要处理大量的数据，不推荐使用这种方案。Copy Command可以处理Create Table不能处理的情况，即向已有的数据表中追加记录，相对于insert来说，效率更高一些，生成更少的Redo信息，不生成Undo信息，所以在执行大量的数据追加时，推荐使用Copy Command命令。基于PL/SQL的数据库备份方法PL/SQL Developer是Oracle数据库中用于导入或导出数据库的主要工具，本文主要介绍了利用PL/SQL Developer导入和导出数据库的过程，并对导入或导出时的一些注意事项进行了说明，接下来我们就一一介绍。导出步骤：1 tools -

34、>export user object 选择选项，导出.sql文件。2 tools ->export tables-> Oracle Export 选择选项导出.dmp文件。导入步骤：注：导入之前最好把以前的表删除，当然导入另外数据库除外。1 tools->import tables->SQL Inserts 导入.sql文件。2 tools->import talbes->Oracle Import然后再导入dmp文件。一些说明：Tools->Export User Objects导出的是建表语句（包括存储结构）。PS：这种方式只能导

35、出属于这个用户的表，其他用户的表不能导出，建议用命令行(exp,imp)导出。Tools->Export Tables里面包含三种导出方式，三种方式都能导出表结构以及数据，如下：· Oracle Export· Sql Insert· pl/sql developer第一种是导出为.dmp的文件格式，.dmp文件是二进制的，可以跨平台，还能包含权限，效率也很不错，用得最广 。第二种是导出为.sql文件的，可用文本编辑器查看，通用性比较好，但效率不如第一种，适合小数据量导入导出。尤其注意的是表中不能有大字段 （blob,clob,long），如果有，会提示不能

36、导出(提示如下： table contains one or more LONG columns cannot export in sql format,user Pl/sql developer format instead)。第三种是导出为.pde格式的，.pde为Pl/sql developer自有的文件格式，只能用Pl/sql developer自己导入导出，不能用编辑器查看。PS：只有"Oracle Export"方式导出再导入后，表结构和索引结构没有发生变化，另两种方式都改变了索引类型。oracle 用户 从一个表空间 另一个表空间-重要- selec

37、t count(*) from user_tables; select * from user_lobs; select 'alter table ' |t.table_name|' move lob('|t.column_name| ') store as (tablespace ilearn);' from user_lobs t;-下面这个是形式的写法？、 select 'ALTER TABLE ' |t.table_name| ' MOVE TABLESPACE ilearn 

38、; LOB ('|t.column_name|') store as (tablespace ilearn);' from user_lobs t; select 'alter index '|index_name |' rebuild;'from user_indexes t where t.status ='UNUSABLE' select index_name from user_indexes t where t.status ='UNUSABLE' -alter in

39、dex index_name rebuild; Select 'alter table '| table_name | ' move tablespace ilearn;' from user_tables; select 'alter index '| index_name |' rebuild tablespace ilearn;' from user_indexes; select table_name,tablespace_name from user_tables; select&

40、#160;  segment_name,bytes/(1024*1024),t.segment_type   from   user_segments t   where   tablespace_name='USERS'  select   segment_name,bytes/(1024*1024),t.segment_type   from   user_segments t   wh

41、ere   tablespace_name='ILEARN'   and t.segment_name='SYS_IL0000030462C00010$'   ; select count(*)  from user_all_tables; select * from PORTAL_PREFERENCE; select * from  PLAN_TABLE; drop table PORTAL_PREFERENCE; drop tab

42、le PLAN_TABLE; select * from user_indexes t where t.index_name='SYS_IL0000030402C00010$' Move表、索引、LOB/Long move一个表到另外一个表空间时,索引不会跟着一起move，而且会失效。（LOB类型例外） 表move，我们分为： *普通表move *分区表move *LONG,LOB大字段类型move来进行测试和说明。 索引的move，我们通过rebuild来实现 一：move普通表、索引基

43、本语法： alter table tab_name move tablespace tbs_name; move过的普通表，在不用到失效的索引的操作语句中，语句执行正常，但如果操作的语句用到了索引（主键当做唯一索引），则此时报告用到的索引失效，语句执行失败，其他如外键，非空约束，缺省值等不会失效。 我们需要重新创建主键或索引，基本语法为： alter index index_name rebuild; alter index pk_name rebuild; 如果我们需要move索引，则使用rebuild语法： alter

44、index index_name rebuild tablespace tbs_name; alter index pk_name rebuild tablespace tbs_name; 提示：查询表所具有的索引，可以使用user_indexes视图（索引和主键都在这个视图里可找到）。 二：move分区表及索引和普通表一样，索引会失效，区别的仅仅是语法而已。 分区基本语法：特别提醒注意，如果是单级分区，则使用关键字PARTITION，如果是多级分区，则使用SUBPARTITION替代PARTITION。 如果分区或分区索引比较大，可以使用并行

45、move或rebuild，PARALLEL (DEGREE 2);如： ALTER TABLE PART_ALARMTEXTDATA move SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2); ALTER INDEX GLOBAL_ALARMTEXTDATA REBUILD tablespace users PARALLEL (DEGREE 2); ALTER INDEX LOCAL_ALARMTEXTDATA REBUILD SUBPARTITION ALA

46、RMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2); 移动表的某个分区： = ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name; 重建全局索引： = ALTER INDEX global_index REBUILD;或 ALTER INDEX global_index REBUILD tablespace tbs_name; xsb注: 分区操作时可以带上

47、with update global indexes选项更新全局索引。 重建局部索引： = ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;或 ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name; 提示： USER_PART_TABLES USER_IND_PARTITIONS USER

48、_IND_SUBPARTITIONS USER_LOB_PARTITIONS USER_LOB_SUBPARTITIONS USER_PART_INDEXES USER_PART_LOBS可查询分区相关内容，同时，分区对象，也是segment，所以也可在dba_segments里查的到。 三：move LONG,LOB类型（据说DBMS_REDEFINITION包可以提供一些方便，没用过。） I:LONG类型 long类型不能通过MOVE来传输特别提示，尽量不要用LONG类型，特难管理。参考：1，LONG不能使用insert

49、 into . select .等带select的模式。如 create table t123 (id int,en long);则 insert into t123(id,en) select * from t123;报告错误，可以用pl/sql来帮助解决，如： declare cursor cur_t123 is select * from t123; use_t123 cur_t123%rowtype; begin open cur_t123; loop fetch cur_t123 into us

50、e_t123; exit when cur_t123%notfound; insert into t123(id,en) values (use_t123.id,use_t123.en); end loop; close cur_t123; end; / 对有LONG类型字段的表的转移，可以使用： create新表的方法。 * create一个新的表，存储在需要转移的表空间。 * 创建新的索引（使用tablespace 子句指定新的表空间）。 * 把数据转移过来 方法一：用C

51、OPY的方法： copy from bigboar/bigboarbigboar_sid insert t123(id,en) using select id,en from t123; 方法二：PL/SQL（如上） 方法三：直接就把LONG转换成CLOB类型 create table t321(id int,en clob) tablespace users; insert into t321(id,en) select id,to_lob(en) from t123; 方法四：exp/imp exp bigboar/bi

52、gboar file=a.dat tables=t123 imp bigboar/bigboar file=a.dat full=y IGNORE =y * drop掉旧表。 * rename 新表为旧表表名。 II: LOB类型在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type= LOBSEGMENT），另一个用来存放索引（segment_type=LOBINDEX）。默认它们会存储在和表一起的表空间。我们对表MOVE时， LOG类型字段和该字段的索引不会跟着MOVE，必须

53、要单独来进行MOVE，语法如下如： alter table t321 move tablespace users; alter table t321 move lob(en) store as (tablespace users); ref: 移动LOB字段至新表空间 xsb 发表于:2006.06.13 15:03 :分类: ( Oracle ) :阅读:(1939次) : Permanent link : 引用 (0) 把现有用户的数据都转到一个新建的表空间中方法一1.建立新的表空间 2.建立新用户，默认表空间为刚建立的新的表空间 

54、;3.设定用户权限，quota还是要设定一下。 4.从原用户导出数据：exp username/password file=filename owner=username rows=y grants=y 5.从原用户那里，用PLSQL Developer（其他的工具也可以）导出原用户的所有object，生成sql文件。 6.编辑sql文件，替换所有的旧表空间为新表空间。 7.用新用户登录，并执行sql文件，生成表结构 8.执行Imp导入数据：imp newusername/password file=filename log=logfile f

55、romuser=olduser touser=newuser ignore=y 9.查看新用户下的表的存储情况，确定完全存放在新的表空间下面： select table_name,tablespace_name from user_tables; 如果没有lob字段的表,不用执行5、6、7三步。 方法二缺省情况下，导入试图在与导出相同的表空间中创建对象。如果用户不具有那个表空间的权限，或者那个表空间不存在时，Oracle在用户帐户的缺省表空间中创建数据库对象。这些特性可以用于使用导出与导入在表空间之间移动数据库对象。要为USER_A将TABLESPACE

56、_A的所有对象移动到TABLESPACE_B，应遵循以下步骤：         1   为USER_A导出TABLESPACE_A中的所有对象。    2   执行REVOKE   UNLIMITED   TABLESPACE   ON   TABLESPACE_A   FROM   USER_A;以收回任何授予用户帐

57、户的无限制表空间权限。         3   执行ALTER   USER   USER_A   QUOTA   0   ON   TABLESPACE_A;以使USER_A帐户不能在TABLESPACE_A上创建任何对象。         4   删除TABLESPACE_A

58、中USER_A拥有的所有对象。         5   执行ALTER   USER   USER_A   DEFAULT   TABLESPACE   TABLESPACE_B;以使TABLESPACE_B成为USER_A用户帐户的缺省表空间。Oracle试图将对象导入TABLESPACE_A，因为这些对象是从TABLESPACE_A导出的。注意用户不具有TABLESPACE_A上的配额。然后将

59、查看用户的缺省表空间。在Oracle可以将数据导入TABLESPACE_B之前，必须给予USER_A用户该表空间上足够大的配额，如下步骤所示。         6   执行ALTER   USER   USER_A   QUOTA   UNLIMITED   ON   TABLESPACE_B;       

60、  7   导入被导出的数据库对象。缺省情况下，导入工具试图将它们导入到TABLESPACE_A中，然而，因为用户不具有这个表空间的配额，所以所有的对象将被创建在USER_A的缺省表空间TABLESPACE_B中。Top REVOKE   UNLIMITED   TABLESPACE   ON   itmpspace  FROM  itmp  ; ALTER   USER    

61、;  itmp    QUOTA   0   ON   itmpspace ; ALTER   USER  itmp       DEFAULT   TABLESPACE   idmp; ALTER   USER   itmp     DEFAULT 

62、0; TABLESPACE    idmp; ALTER   USER   itmp      QUOTA   UNLIMITED   ON    idmp;    alter table t move lob(c2) store as (tablespace users); alter table test move tablespace users lob(c2) store as (tablespace users); ref: Move表、索引、LOB/Long （导出、导入时同分区表一样，必须预创建此表空间）