ITPub博客

SQL> show parameter skipNAME TYPE VALUE------------------------------------ ----------- ------------------------------skip_unusable_indexes boolean TRUE默认情况下skip_unusable_indexes=trueSQL> create table tt (id int,name varchar2(100)) 2 ;Table created.SQL> create table tt1 (id int,name varchar2(100)) 2 ;Table created.SQL> create unique index tt_index on tt (id);Index created.创建 unique indexSQL> alter table tt add pr

ALTER TABLE DICT_KEYVALUETYPE MOVE TABLESPACE CHSIS_DATA LOB (TYPEXML) STORE AS (TABLESPACE CHSIS_DATA);移动脚本：select 'alter table '|| table_name || ' move tablespace chsis_data lob('||column_name||') store as (tablespace chsis_data);' from user_lobs where tablespace_name='USERS'[@more@]

首先查询 dba_lobs得到相关信息。SQL> select table_name,column_name,segment_name from dba_lobs where table_name='ESB_MF_INFORMATION';TABLE_NAME COLUMN_NAME SEGMENT_NAME-------------------- ------------------------- -------------------------------------------------------ESB_MF_INFORMATION EXCEPTION_DATA SYS_LOB0000078584C00009$$ESB_MF_INFORMATION RUNTIME_EXCEPTION SYS_LOB0000078584C00008$$然后根据出来的信息alter table ESBAGENT.ESB_MF_INFORMATION

oracle提供了很多工具来查找最低效的SQL，但是，本质上就是通过如下SQL语句，从相关系统视图中获取最低效的SQL： select rownum as rank, a.* from (select PARSING_SCHEMA_NAME RUN_USER, --执行用户 SQL_FULLTEXT, --SQL文本,全，带格式 sql_text, --SQL文本前面部分 elapsed_Time , --单位是微秒，语句执行完的总时间，包括CPU时间和等待时间 cpu_time , --CPU时间，语句运行的时间 elapsed_Time - cpu_time wait_time, trunc((elapsed_Time - cpu_time)*100/elapsed_Time,2) "wait_time_per%",--等待时间所占

利用RMAN进行基于时间点的修复，奇怪的是设置了nls_date_format之后，就不能登录RMAN了?现象如下：C:>set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'C:>rman nocatalog target rman/rman@testRecovery Manager: ...

Normal 0 0 2 false false false MicrosoftInternetExplorer4 <style.st1\:*{behavior.:url(#ieooui) }</style....

http://www.itpub.net/thread-906008-1-1.html [@more@]

CREATE OR REPLACE TRIGGER trg_dropdeny BEFORE  DROP OR TRUNCATE ON DATABASEBEGIN IF sys.login_user<>'SYSTEM' and sys.login_user<>'SYS'   THEN  raise_application_erro...

/* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} 非分区表转换为分区表 1)

st1:*{behavior:url(#ieooui) } /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} table.Mso

st1:*{behavior:url(#ieooui) } /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} table.MsoTab

Normal 0 0 2 false false false MicrosoftInternetExplorer4 <style.st1\:*{behavior.:url(#ieooui) }</style.<styl...

<style.v\:* {behavior.:url(#default#VML);}o\:* {behavior.:url(#default#VML);}w\:* {behavior.:url(#default#VML);}.shape {behavior.:url(#default#VML);}</style. Normal 0 0 2 ...

Service nameSelect * from dba_servicesalter system set service_names=a,b,c,d; select service_name from v$session where sid=(select sid from v$mystat where rownum=1)  àcheck the service...

Service nameSelect * from dba_servicesalter system set service_names=a,b,c,d;select service_name from v$session where sid=(select sid from v$mystat where rownum=1) àcheck the service nameselect * from v$servicemetric à查看业务情况一起负载。1.failovera).connect time failover àclient端实现的把地址列表加进去，连的时候选择实现。b).TAF(transparent application failover) àclient端实现的(FAILOVER_MODE =(TYPE = SELECT) --select/session(转移session)(METHOD = BASIC) (RETRIES = 180) --尝试180次 (DELAY =

Normal 0 0 2 false false false MicrosoftInternetExplorer4 <style.st1\:*{behavior.:url(#ieooui) }</style.<styl...

Scn_to_timestamp and Timestamp_to_scn SQL> select current_scn,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from v$database; CURRENT_SCN TO_CHAR(SYSDATE,'YY----------- ------------------- 12529246 2012/06/18 13:14:19 SQL> select scn_to_timestamp(12529246) from dual; SCN_TO_TIMESTAMP(12529246)---------------------------------------------------------------------------18-JUN-12 01.14.18.000000000 PM SQL> select systimestamp from dual; SYSTIMESTAMP------------------------------------

4.UNDOalter tablespace undotbs1 retention guarantee[noguarantee]使用到的视图：select * from gv$rollnameselect * from gv$rollstatselect * from dba_rollback_segsselect * from dba_undo_extents(a).strings命令得到回滚段的信息：[oracle@deer ~]$ strings /u01/oradata/deer/system01.dbf | grep _SYSSMU|cut -d $ -f 1 |sort -uand substr(drs.segment_name,1,7) != '_SYSSMU' D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );_SYSSMU10_3550978943_SYSSMU10_3904554333_SYSSMU11_286947212_SYSSMU12_3068564564_SYSS

tl: 16 fb: --H-FL-- lb: 0x2 cc: 2dscn 0x0000.00af2558col 0: [ 2] c1 04col 1: [ 3] 78 78 78end_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 9831 maxblk 9831 Flashback version query不会跨越DDL操作SQL> select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,id,name from t4 versions between scn minvalue and maxvalue where xxx=xxx;select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_