收藏
下载资源下载资源 加入VIP,免费下载

oracle常用技巧.docx

上传人：b****7 文档编号：9138444 上传时间：2023-02-03 格式：DOCX 页数：17 大小：26.04KB
下载 相关 举报
oracle常用技巧.docx_第1页
第1页 / 共17页
oracle常用技巧.docx_第2页
第2页 / 共17页
oracle常用技巧.docx_第3页
第3页 / 共17页
oracle常用技巧.docx_第4页
第4页 / 共17页
oracle常用技巧.docx_第5页
第5页 / 共17页
点击查看更多>>
下载资源
资源描述

oracle常用技巧.docx

《oracle常用技巧.docx》由会员分享，可在线阅读，更多相关《oracle常用技巧.docx（17页珍藏版）》请在冰豆网上搜索。

oracle常用技巧.docx

oracle常用技巧

1移动表空间命令

altertableSP_COUNT_MON_02movetablespacetrantbs1

2源串like‘%子串%’与instr（源串,子串）>0等价

在预处理get_kor_msisdn将like‘%子串%’静态sql改为动态sql

instr（源串,:

v1（子串））>0

3查找表结构应该相同的表

今天写了下面这一段SQL语句,在写之前总觉得有更简便的方法,可就是没有想出来:

SELECT

  REPLACE（REPLACE（REPLACE（REPLACE（REPLACE（

      REPLACE（REPLACE（REPLACE（REPLACE（REPLACE（TABLE_NAME,'0','#'）,

        '1','#'）,'2','#'）,'3','#'）,'4','#'）

            ,'5','#'）,'6','#'）,'7','#'）,'8','#'）,'9','#'）  PATTERN_NAME

FROMUSER_TABLES

  下班时坐地铁,突然想到了Translate函数,原来可以这么简单:

SELECT

  TRANSLATE（TABLE_NAME,'0123456789','##########'）PATTERN_NAME

FROMUSER_TABLES

  我写这个SQL是用来找出一些表结构应当相同的表,最终的SQL应当如下:

SELECTTABLE_NAMEFROMUSER_TABLES

WHERETRANSLATE（TABLE_NAME,'0123456789','##########'）IN

（SELECT

  TRANSLATE（TABLE_NAME,'0123456789','##########'）PATTERN_NAME

FROMUSER_TABLES

GROUPBYTRANSLATE（TABLE_NAME,'0123456789','##########'）

HAVINGCOUNT（*）>1）

  看来选择最佳的方法是很必要的.

4exp/imp使用技巧

4.1exp命令

exp用户名/密码@oracle_sidfile=表名.dmptables=表名

默认情况下，即导数据又导表结构（full=y）

exp用户名/密码@oracle_sidrows=nfile=表名.dmptables=表名

只到表结构不导数据

exp用户名/密码@oracle_sidrows=yfile=表名.dmptables=表名

导表结构也导数据

4.2imp命令

Imp用户名/密码@oracle_sidfile=表名.dmptables=表名

默认情况下，即导数据又导表结构（createtable）如果数据里原来有这个表那么报错

Imp用户名/密码@oracle_sidignore=yfile=表名.dmptables=表名

只导入数据不修改表结构（createtable）

4.3.建立一个与现存数据库相同,但不包含数据的空库

对全库作俄Export或Import时,使用参数ROWS=N

例:

expsystem/managerfull=Yrows=Nfile=full.dmp

impsystem/managerfull=Yrows=Nfile=full.dmp

5like语句优化

简单说两句，具体看例子

1。

尽量不要使用like'%..%'

2。

对于like'..%..'（不以%开头），Oracle可以应用colunm上的index

3。

对于like'%...'的（不以%结尾），可以利用reverse+functionindex的形式，变化成like'..%'

代码:

--'建测试表和Index，注意，重点在于带reverse的functionindex。

同时，一定要使用CBO才行……

sys@mescp>selectreverse（'123'）fromdual;REVERSE（'123'）

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

321

1rowselected.

sys@mescp>createtabletest_likeasselectobject_id,object_namefromdba_objects;

Tablecreated.

sys@mescp>createindextest_like__nameontest_like（object_name）;

Indexcreated.

sys@mescp>createindextest_like__name_reverseontest_like（reverse（object_name））;

Indexcreated.

sys@mescp>analyzetabletest_likecomputestatisticsfortableforallindexes;

Tableanalyzed.

sys@mescp>setautotracetraceexp

--'常量开头的like,会利用index，没问题……'

sys@mescp>select*fromtest_likewhereobject_namelikeAS%';

ExecutionPlan

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

0SELECTSTATEMENTOptimizer=CHOOSE（Cost=2Card=655Bytes=15720）

10TABLEACCESS（BYINDEXROWID）OF'TEST_LIKE'（Cost=2Card=655Bytes=15720）

21INDEX（RANGESCAN）OF'TEST_LIKE__NAME'（NON-UNIQUE）（Cost=2Card=118）

--'开头和结尾都是%,对不起，很难优化'

sys@mescp>select*fromtest_likewhereobject_namelike'%%';

ExecutionPlan

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

0SELECTSTATEMENTOptimizer=CHOOSE（Cost=6Card=655Bytes=15720）

10TABLEACCESS（FULL）OF'TEST_LIKE'（Cost=6Card=655ytes=15720）

--'以常量结束，直接写的时候是不能应用index的'

sys@mescp>select*fromtest_likewhereobject_namelike'%S';

ExecutionPlan

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

0SELECTSTATEMENTOptimizer=CHOOSE（Cost=6Card=655Bytes=15720）

10TABLEACCESS（FULL）OF'TEST_LIKE'（Cost=6Card=655Bytes=15720）

--'以常量结束的，加个reverse函数，又可以用上index了'

sys@mescp>select*fromtest_likewherereverse（object_name）likereverse（'%AS'）;

ExecutionPlan

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

0SELECTSTATEMENTOptimizer=CHOOSE（Cost=2Card=655Bytes=15720）

10TABLEACCESS（BYINDEXROWID）OF'TEST_LIKE'（Cost=2Card=655Bytes=15720）

21INDEX（RANGESCAN）OF'TEST_LIKE__NAME_REVERSE'（NON-UNIQUE）（Cost=2Card=118）

6sqlpluscopy命令在两个数据库间转移数据

本文介绍了如何利用sqlpluscopy命令在两个数据库间转移数据

无需用到dblink,两个数据库间不需直接通讯，当然，需要有一个client段能同时以sqlplus连接到两个数据库

问题的提出

论坛上有人提出这样的问题：

假设有两个数据库,分别处于两个不同的网但有一个客户机安了两块网卡可以同时连到两个数据库请问如果不通过在客户机上建中转表,有没有办法实现这两个数据库中从某一个往另一个拷表.

问题的解答

可以使用sqlplus的copy命令来达到。

copy的命令的这个样子的：

usage:

COPYFROMTO

{（）}USING:

databasestring,e.g.,scott/tiger@d:

chicago-mktg

:

ONEofthekeywords:

APPEND,CREATE,INSERTorREPLACE

:

nameofthedestinationtable

:

acomma-separatedlistofdestinationcolumnaliases

:

anyvalidSQLSELECTstatement

实际上写的应该很清楚了，按照那样的语法就可以完成了。

它相当于从FROMDB根据把数据取到缓冲区，再根据在TODB中创建table（create）或者重新创建table（replace），然后把数据insert或者appendinsert到TODB的table中。

Ø有一点需要注意的，通常会设置高一点的arraysize，以便使sqlplus与DB的交互减少，以达到更好的性能。

Ø另外，在写databasestring时，可以不必写出密码，sqlplus会稍后会提示你输入密码。

一个完整的例子:

sqlplus/nolog

SQL>setarraysize5000

SQL>copyfromserol/luo@mescptoserol/luo@ractestcreatetest_copy_tableusingselect*fromdba_objects;

Arrayfetch/bindsizeis5000.（arraysizeis5000）

Willcommitwhendone.（copycommitis0）

Maximumlongsizeis5000.（longis5000）

SQLRCNincpytblfailed:

-2120

TableTEST_COPY_TABLEcreated.

12579rowsselectedfromserol@mescp.

12579rowsinsertedintoTEST_COPY_TABLE.

12579rowscommittedintoTEST_COPY_TABLEatserol@ractest.

7查看数据库使用空间

selectff.stablespace_name,

ff.btotal,

（ff.b-fr.b）usage,

fr.bfree,

round（（ff.b-fr.b）/ff.b*100）||'%'usagep

from（selecttablespace_names,sum（bytes）/1024/1024/1024b

fromdba_data_files

groupbytablespace_name）ff,

（selecttablespace_names,sum（bytes）/1024/1024/1024b

fromdba_free_space

groupbytablespace_name）fr

whereff.s=fr.s

8查看表空间使用情况

SQL>coltablespaceformata20

SQL>select

     b.file_id文件ID号,

     b.tablespace_name表空间名,

     b.bytes/1024总计大小,

     （b.bytes-sum（nvl（a.bytes,0）））/1024已使用,

     sum（nvl（a.bytes,0））/1024剩余空间,

     （b.bytes-sum（nvl（a.bytes,0）））/（b.bytes）*100剩余百分比

     fromdba_free_spacea,dba_data_filesb

     wherea.file_id=b.file_id

     groupbyb.tablespace_name,b.file_id,b.bytes

     orderbyb.file_id;

 

9成批索引或表移动表空间

select'alterindex'||index_name||'rebuildtablespaceinfoidx03nologging;'fromuser_indexes

wheretable_namelike'LOAD%'

select'altertable'||table_name||'movetablespaceinfodat03nologging;'fromuser_tables

wheretable_namelike'LOAD%'

10改变表名

altertableorder_info_inc_arenametoorder_info_inc_b;

11改变列名

altertabletrenamecolumnoldtonew;

12查看session信息

SELECTA.OWNER,  

A.OBJECT_NAME,  

B.XIDUSN,  

B.XIDSLOT,  

B.XIDSQN,  

B.SESSION_ID,  

B.ORACLE_USERNAME,  

B.OS_USER_NAME,  

B.PROCESS,  

B.LOCKED_MODE,  

C.MACHINE,  

C.STATUS,  

C.SERVER,  

C.SID,  

C.SERIAL#,  

C.PROGRAM  

FROMALL_OBJECTSA,  

V$LOCKED_OBJECTB,  

V$SESSIONC

WHERE（A.OBJECT_ID=B.OBJECT_ID）

AND（B.PROCESS=C.PROCESS）

ORDERBY1,2;

13打印输出信息

出现下面的错误怎么办：

ORA-20000:

ORU-10027:

bufferoverflow,limitof2000bytes

ORA-20000:

ORU-10027:

bufferoverflow,limitof2000bytes问题的解决

方法1：

setserveroutputonsize10000000

方法2：

execdbms_output.enable（999999999999999999999）;

14获取对象创建语句

dbroam@SETTLEDB>Setpagesize1000;

dbroam@SETTLEDB>Setlinesize1000;

dbroam@SETTLEDB>setlong2000;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'TABLE','DT_R_OTHER_PARTY'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'TABLE','DT_R_OTHER_PARTY'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'FUNCTION','IS_NEGATIVE'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'PROCEDURE','P_SP1_OTHER_SETTLE'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'DB_LINK','BILL_LINK'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'VIEW','CFG_M_CM_INFO'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'TABLESPACE','C001_1'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'PACKAGE','SG_IHASH'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'TRIGGER','QD_LOG_BUR'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'SYNONYM','CHINA_MSC'）fromdual;

dbroam@SETTLEDB>selectdbms_metadata.get_ddl（'USER','DBROAM'）fromdual;

基本上用到的语法如下：

  

a.获取单个的建表和建索引的语法

setheadingoff;

setechooff;

Setpages999;

setlong90000;

  

spoolDEPT.sql

selectdbms_metadata.get_ddl（'TABLE','DEPT','SCOTT'）fromdual;  

selectdbms_metadata.get_ddl（'INDEX','DEPT_IDX','SCOTT'）fromdual;

spooloff;

  

b.获取一个SCHEMA下的所有建表和建索引的语法，以scott为例：

  

setpagesize0

setlong90000

  

setfeedbackoff

  

setechooff  

spoolscott_schema.sql  

connectscott/tiger;

SELECTDBMS_METADATA.GET_DDL（'TABLE',u.table_name）

    FROMUSER_TABLESu;

SELECTDBMS_METADATA.GET_DDL（'INDEX',u.index_name）

    FROMUSER_INDEXESu;

spooloff;

  

c.  获取某个SCHEMA的建全部存储过程的语法

  

connectbrucelau/brucelau;

  

spoolprocedures.sql

  

select

  DBMS_METADATA.GET_DDL（'PROCEDURE',u.object_name）

from

  user_objectsu

  

where

  object_type='PROCEDURE';

  

spooloff;

  

  

另：

  

dbms_metadata.get_ddl（'TABLE','TAB1','USER1'）  

三个参数中，第一个指定导出DDL定义的对象类型（此例中为表类型），第二个是对象名（此例中即表名），第三个是对象所在的用户名。

setechooff

setfeedbackoff

setheadingoff

spoolabc.log

select*fromtest;

spooloff

15获取存储过程，函数，包的创建语句

Setpagesize1000;

Setlinesize1000;

selecttextfromall_sourcewhereowner=userandname=upper（'p_sp1_other_settle'）;

16计算某个表所占空间大小

selectsegment_name,sum（bytes）/1024/1024/1024g

fromdba_segments

whereowner=user

groupbysegment_name

17获取正在执行sql

selecta.username,a.sid,b.sql_textfromv$sessiona,v$sqlareabwherea.sql_address=b.address

18移动分区表

移分区表:

SELECT'altertable'||table_owner||'.'||table_name||'movepartition'||partition_name||'tablespace新表空间名parallel;'

fromdba_tab_partitions

WHEREtablespace_name='表空间名'

groupbytable_name,table_owner,partition_name

重建分区索引:

SELECT'alterindex'||owner||'.'||segment_name||'rebuildpartition'||partition_name||'tablespace新表空间名parallel;'

select*

FROMDBA_SEGMENTS

WHERETABLESPACE_name='表空间名

展开阅读全文
猜你喜欢
相关搜索

当前位置：首页 > 工程科技 > 城乡园林规划

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1