oracle常用技巧Word文件下载.docx
- 文档编号:22274549
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:17
- 大小:26.04KB
oracle常用技巧Word文件下载.docx
《oracle常用技巧Word文件下载.docx》由会员分享,可在线阅读,更多相关《oracle常用技巧Word文件下载.docx(17页珍藏版)》请在冰豆网上搜索。
下班时坐地铁,突然想到了Translate函数,原来可以这么简单:
TRANSLATE(TABLE_NAME,'
0123456789'
##########'
)PATTERN_NAME
我写这个SQL是用来找出一些表结构应当相同的表,最终的SQL应当如下:
SELECTTABLE_NAMEFROMUSER_TABLES
WHERETRANSLATE(TABLE_NAME,'
)IN
(SELECT
FROMUSER_TABLES
GROUPBYTRANSLATE(TABLE_NAME,'
)
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。
%...'
的(不以%结尾),可以利用reverse+functionindex的形式,变化成like'
..%'
代码:
--'
建测试表和Index,注意,重点在于带reverse的functionindex。
同时,一定要使用CBO才行……
sys@mescp>
selectreverse('
123'
)fromdual;
REVERSE('
--------------------------------
321
1rowselected.
createtabletest_likeasselectobject_id,object_namefromdba_objects;
Tablecreated.
createindextest_like__nameontest_like(object_name);
Indexcreated.
createindextest_like__name_reverseontest_like(reverse(object_name));
analyzetabletest_likecomputestatisticsfortableforallindexes;
Tableanalyzed.
setautotracetraceexp
常量开头的like,会利用index,没问题……'
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)
--'
开头和结尾都是%,对不起,很难优化'
select*fromtest_likewhereobject_namelike'
%%'
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=6Card=655Bytes=15720)
10TABLEACCESS(FULL)OF'
(Cost=6Card=655ytes=15720)
以常量结束,直接写的时候是不能应用index的'
%S'
(Cost=6Card=655Bytes=15720)
以常量结束的,加个reverse函数,又可以用上index了'
select*fromtest_likewherereverse(object_name)likereverse('
%AS'
);
(Cost=2Card=655Bytes=15720)
TEST_LIKE__NAME_REVERSE'
6sqlpluscopy命令在两个数据库间转移数据
本文介绍了如何利用sqlpluscopy命令在两个数据库间转移数据
无需用到dblink,两个数据库间不需直接通讯,当然,需要有一个client段能同时以sqlplus连接到两个数据库
问题的提出
论坛上有人提出这样的问题:
假设有两个数据库,分别处于两个不同的网但有一个客户机安了两块网卡可以同时连到两个数据库请问如果不通过在客户机上建中转表,有没有办法实现这两个数据库中从某一个往另一个拷表.
问题的解答
可以使用sqlplus的copy命令来达到。
copy的命令的这个样子的:
usage:
COPYFROM<
db>
TO<
<
opt>
table>
{(<
cols>
)}USING<
sel>
:
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
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查看表空间使用情况
coltablespaceformata20
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%'
altertable'
||table_name||'
movetablespaceinfodat03nologging;
fromuser_tables
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
bufferoverflow,limitof2000bytes问题的解决
方法1:
setserveroutputonsize10000000
方法2:
execdbms_output.enable(999999999999999999999);
14获取对象创建语句
dbroam@SETTLEDB>
Setpagesize1000;
Setlinesize1000;
setlong2000;
selectdbms_metadata.get_ddl('
TABLE'
DT_R_OTHER_PARTY'
FUNCTION'
IS_NEGATIVE'
PROCEDURE'
P_SP1_OTHER_SETTLE'
DB_LINK'
BILL_LINK'
VIEW'
CFG_M_CM_INFO'
TABLESPACE'
C001_1'
PACKAGE'
SG_IHASH'
TRIGGER'
QD_LOG_BUR'
SYNONYM'
CHINA_MSC'
USER'
DBROAM'
基本上用到的语法如下:
a.获取单个的建表和建索引的语法
setheadingoff;
setechooff;
Setpages999;
setlong90000;
spoolDEPT.sql
DEPT'
SCOTT'
INDEX'
DEPT_IDX'
spooloff;
b.获取一个SCHEMA下的所有建表和建索引的语法,以scott为例:
setpagesize0
setlong90000
setfeedbackoff
setechooff
spoolscott_schema.sql
connectscott/tiger;
SELECTDBMS_METADATA.GET_DDL('
u.table_name)
FROMUSER_TABLESu;
u.index_name)
FROMUSER_INDEXESu;
c.
获取某个SCHEMA的建全部存储过程的语法
connectbrucelau/brucelau;
spoolprocedures.sql
DBMS_METADATA.GET_DDL('
u.object_name)
from
user_objectsu
where
object_type='
另:
dbms_metadata.get_ddl('
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||'
.'
movepartition'
||partition_name||'
tablespace新表空间名parallel;
fromdba_tab_partitions
WHEREtablespace_name='
表空间名'
groupbytable_name,table_owner,partition_name
重建分区索引:
||owner||'
||segment_name||'
rebuildpartition'
select*
FROMDBA_SEGMENTS
WHERETABLESPACE_name='
表空间名
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 常用 技巧