常用SQL
永久licensesAJZU-WOPP-VWM2-H98K-XSU6-PP34-O4OZ-CPPN-Precover database using backup controlfile allow 1 corruption;hwclock --set --date="3/29/2011 16:45:05"dbms_workload_repository.CREA
·
永久licenses
AJZU-WOPP-VWM2-H98K-XSU6-PP34-O4OZ-CPPN-P
recover database using backup controlfile allow 1 corruption;
hwclock --set --date="3/29/2011 16:45:05"
dbms_workload_repository.CREATE_SNAPSHOT();
[执行计划]
------------------------
select * from table(dbms_xplan.display_cursor('10pgwdhnpv0sn'))
select sql_id,count(1) from dba_hist_active_sess_history
where snap_id between 1355 and 1356 and event='library cache: mutex X' group by sql_id;
select sql_id,count(1) from dba_hist_active_sess_history
where snap_id between 1355 and 1356 and event='enq: TX - row lock contention' group by sql_id;
[longops]查看执行语句的进度
---------------------------
SELECT SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE" FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/ora_arch/1_780_716312058.dbf',DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle10/oradata/orcl/redo02.log',DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => '14-Jul-2011 16:00:00', ENDTIME => '14-Jul-2011 18:30:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
select timestamp,SQL_REDO from V$LOGMNR_CONTENTS where SEG_OWNER='SCPDB1' and SEG_NAME='U_UVS_BASETAB';
EXECUTE DBMS_LOGMNR.END_LOGMNR();
[primary key]
-------------------------
alter table basetab_eppc drop constraint u261_1476 ;
alter table basetab_eppc add constraint primary key (msisdn);
[NULL 值]
-------------------------
空值表示在行中留下一个空白列。某个行中的空值列并不表示该列有一个零值。空值表示该列没有值。
如果列的数据缺失、不适用、或未知，可用空值表示。一个列只有在不对此列指定NOT NULL限制时才
允许空值。如果设置某列作为表的主键，则该列不允许为空值。应该尽量使有空值的列置于表的末尾，
这样做是因为oracle存储空值的方式。因为空值表示没有值，因此不能与其它的值进行比较，只能用
IS NULL、NOT NULL 来判断是否有值。
[SQL 查询] //把互锁的打印出来 2011-05-30
-------------------------
select ses.sid, ses.serial, ses.object_name, sql.sql_text from v$sqlarea sql,
(
Select decode(s.sql_hash_value,0,s.prev_hash_value,s.sql_hash_value) as hash,
Decode(s.sql_hash_value,0,s.prev_sql_addr,s.sql_address) as addr,
s.sid as sid,
s.serial# as serial,
o.object_name as object_name
from v$session s, v$lock l, dba_objects o
where l.type='TM' and l.id1=o.object_id and l.sid=s.sid and s.sid <> ( select sid from v$mystat where rownum = 1)
) ses
where sql.hash_value = ses.hash and sql.address = ses.addr ;
[undo 使用率]
-----------------------------------
mystat
-------------------
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
mystat2
--------------------
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
[数据空间使用率]
----------------------------------------
select t.tablespace_name,t.Total_MB,ROUND(t.Total_MB-NVL(f.Free_MB,0),2) Used_MB,ROUND((t.Total_MB-NVL(f.Free_MB,0))*100/t.Total_MB,2) Used_Rate,
ROUND(NVL(f.Free_MB,0),2) Free_MB,ROUND(NVL(f.Free_MB,0)*100/t.Total_MB,2) Free_Rate
from (select tablespace_name,sum(bytes)/1024/1024 Total_MB
from dba_data_files
group by tablespace_name
) t,
(select tablespace_name,sum(bytes)/1024/1024 Free_MB
from dba_free_space
group by tablespace_name
) f
where t.tablespace_name = f.tablespace_name(+);
[undo 使用率]
---------------------------------
select a.tablespace_name,
used_undo,
total_undo,
trunc(used_undo / total_undo * 100, 2) || '%' used_rag
from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
from dba_undo_extents
where status = 'ACTIVE'
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) total_undo
from dba_data_files
where tablespace_name in
(select value
from v$spparameter
where name = 'undo_tablespace'
and (sid = (select instance_name from v$instance) or
sid = '*'))
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
[监控谁在使用UNDO]
-----------------------------------------
SELECT r.name rbs,
nvl(s.username, 'None') ouser,
s.osuser client,
p.username puser,
s.sid,
s.serial#,
p.spid pid,
TO_CHAR(sysdate - (s.last_call_et) / 86400, 'mm/dd/yyhh24:mi:ss') as last_txn,
trunc(t.used_ublk * TO_NUMBER(x.value) / 1024 / 1024,2) as undo_mb
FROM v$process p,
v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND s.paddr = p.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
ORDER BY r.name
[数据库一致性]
-----------------------------------
select checkpoint_change# from v$database;
select * from v$log;
select file#,ts#,status,CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
select file#,status,recover,ts#,CHECKPOINT_CHANGE# from v$datafile_header;
select file#, status, tablespace_name,name, dbms_flashback.get_system_change_number CUR_SCN,
to_char(checkpoint_time,'YYYY-MM-DD HH24:MI:SS') CKPT_DT,checkpoint_change# CKPT_SCN, checkpoint_count CKPT_CNT
from v$datafile_header;
[如何找到占用CPU极高的进程执行的SQL语句]
-----------------------------------
select sql_text from v$sqlarea where (hash_value,address) in
( Select decode(s.sql_hash_value,0,s.prev_hash_value,s.sql_hash_value),
Decode(s.sql_hash_value,0,s.prev_sql_addr,s.sql_address)
From v$session s where (s.paddr = (Select addr from v$process where spid='3883'))
);
[何知道哪个SQL最消耗UNDO 表空间]
-------------------
select l.SQL_TEXT,t.UBABLK
from v$session s ,v$transaction t,v$sql l
where s.taddr=t.addr and s.PREV_HASH_VALUE=l.HASH_VALUE
order by UBABLK
[数据空间大小]
--------------------------------
select df.tablespace_name "Tablespace", round(df.bytes/(1024*1024)) "Total(MB)", round(sum(fs.bytes)/(1024*1024)) "Free(MB)", round((df.bytes-sum(fs.bytes))*100/df.bytes) "% Used" from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df where fs.tablespace_name = df.tablespace_name group by df.tablespace_name, df.bytes;
select b.tablespace_name,mbytes_alloc,mbytes_free
from
(select round(sum(bytes)/1024/1024) mbytes_free,tablespace_name from dba_free_space group by tablespace_name ) a,
(select round(sum(bytes)/1024/1024) mbytes_alloc,tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name(+) = b.tablespace_name;
a.tablespace_name (+)= b.tablespace_name 右连接 保留b.tablespace_name的所有项
a.tablespace_name = b.tablespace_name(+) 左连接 保留a.tablespace_name的所有项
[表分区]
---------------------------------
select partition_name,PARTITION_POSITION, TABLE_NAME from user_tab_partitions order by table_name,PARTITION_POSITION ;
begin
for x in ( select partition_name from user_tab_partitions where table_name = 'BIG_TABLE2' )
loop
execute immediate 'alter table big_table2 move partition ' || x.partition_name;
end loop;
end;
/
[JOBS]
----------------------------------
select owner,job_name,JOB_STYLE,ENABLED,state from dba_scheduler_jobs;
exec dbms_scheduler.disable(name =>'EXFSYS.RLM$SCHDNEGACTION') ;
exec dbms_scheduler.disable(name =>'EXFSYS.RLM$EVTCLEANUP') ;
select job,what,to_char(next_date, 'yyyy/mm/dd hh24:mi:ss'),broken,failures from dba_jobs where BROKEN not in ('N','n');
[表存储]
----------------------------------
create table tab2 nologging STORAGE(INITIAL 1M next 1048576) TABLESPACE users as select * from my_tables;
select PCT_FREE,PCT_USED,LOGGING,NUM_ROWS, BLOCKS,CHAIN_CNT,AVG_SPACE,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables ;
[外部表]
----------------------------------
从控制文件创建外部表
sqlldr mydb/mydb control=ts_productorder.ctl external_table=GENERATE_ONLY
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/tkyte'
外部表定义 [[
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'
LOGFILE 'demo1.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255) ,
"DNAME" CHAR(255),
"LOC" CHAR(255),
)
)
location
(
'demo1.ctl'
)
) REJECT LIMIT UNLIMITED
]] //外部表定义
sed -e '/Loader/,/CREATE TABLE/d' -e '/statements to cleanup/,/CPU time/d' -e '/load internal/d' -e '/--/d'
sed -n -e '/CREATE TABLE.*SYS_SQLLDR/,/REJECT LIMIT/p'
sed -n -e '/INSERT.*INTO/,/FROM.*SYS_SQLLDR/p'
[统计更新]
-----------------
select dbms_metadata.get_ddl('INDEX','SYS_C0017095','SCU') from dual; --ARGS: TYPE,NAME,SCHEMA
execute dbms_stats.gather_table_stats(ownname => '$SCHEMA',tabname => 'w_uvs_parentchild' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'w_uvs_rechgmanlog' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'u_uvs_attached' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'u_uvs_basetab' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
select dbms_stats.to_estimate_percent_type(dbms_stats.get_param('ESTIMATE_PERCENT')) from dual;
DBMS_STATS.AUTO_SAMPLE_SIZE
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'w_uvs_rechgmanlog', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'for all indexed columns',cascade => true);
[impdb/expdp]
-------------------
impdp mydb/mydb DIRECTORY=MY_DIR DUMPFILE=ts_productorder.dmp REMAP_SCHEMA=USRDB212:mydb REMAP_TABLESPACE=CBS_USER_DAT:MYTBS
[未知]
-------------------------
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm in ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size');
[失效对象]
-------------------
select a.owner oown, a.object_name oname, a.object_type otype, 'Missing Package Body' prob
from sys.dba_objects a
where a.object_type = 'PACKAGE'
and a.owner = &ownr
and not exists
(select 'x' from dba_objects b
where b.object_name = a.object_name
and b.owner = a.owner and b.object_type = 'PACKAGE BODY')
union
select owner oown, object_name oname, object_type otype, 'Invalid Object' prob
from sys.dba_objects
where object_type in
('PROCEDURE','PACKAGE','FUNCTION','TRIGGER','PACKAGE BODY')
and owner = &ownr
and status != 'VALID'
order by 1,2,3,4;"
[环境变量]
-------------------
select userenv('lang') from dual;
SELECT SYS_CONTEXT ('USERENV', 'service_name') from dual;
[分析函数]
-------------------------
select b.col-a.col
(select rownum,l.first_time,to_number(p.name) instid from v$log_history l,v$parameter p where l.thread# = p.name ) a,
(select rownum,col from table) b
where a.rownum+1=b.rownum;
var c1 number;
exec select to_number(value) into :c1 from v$parameter where name = 'thread';
select a.thread#,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') pre,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') next
from
(select rownum as r,thread#,first_time from v$log_history where first_time>sysdate-3 and thread# = (select to_number(value) from v$parameter where name = 'thread')) a,
(select rownum as r,thread#,first_time from v$log_history where first_time>sysdate-3 and thread# = (select to_number(value) from v$parameter where name = 'thread')) b
where a.r+1=b.r and (a.first_time + 1/24/60) > b.first_time ;
select thread#,to_char(pre,'yyyy-mm-dd hh24:mi:ss') prev,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') next
from (SELECT thread#,first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-3 and thread# = (select INSTANCE_NUMBER from v$instance) )
WHERE pre + 20/24/60 > first_time;
SELECT first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-1 and thread# = (select to_number(value) from v$parameter where name = 'thread') and pre is null;
SELECT thread#, to_char(first_time,'yyyy-mm-dd hh24:mi:ss'), to_char((lag(first_time) over(ORDER BY 1)),'yyyy-mm-dd hh24:mi:ss') pre from v$log_history where first_time>sysdate-3 and thread# = 1
SELECT first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-1 and
SELECT thread#,first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-1 and thread# = (select INSTANCE_NUMBER from v$instance );
select thread#,to_char(pre,'yyyy-mm-dd hh24:mi:ss') prev,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') next from (SELECT thread#,first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-3 and thread# = (select INSTANCE_NUMBER from v$instance) ) WHERE pre + 20/24/60 > first_time;
[关于处理LONG/LOB字段的案例]
----------------------------------------
I:LONG类型
long类型不能通过MOVE来传输特别提示，尽量不要用LONG类型，特难管理!!!
1，LONG不能使用insert 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 use_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 子句指定新的表空间）。
* 把数据转移过来
方法一：用COPY的方法：
copy frombigboar/bigboar@bigboar_sidinsert 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/bigboar 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，必须要单独来进行MOVE，语法如下如：
alter table t321 move tablespace users;
alter table t321 move lob(en) store as (tablespace users);
III: 另外，据说DBMS_REDEFINITION包可以提供整理LONG/LOB字段的一些方便，没用过。
[SCN]
---------------------
SQL> variable scn number
SQL> exec :scn := dbms_flashback.get_system_change_number
SQL> print SCN
SQL> select * from t as of scn :scn;
select scn_to_timestamp(dbms_flashback.get_system_change_number) from dual;
数据字典
$ORACLE_HOME/rdbms/admin/catalog.sql
视图
V$DATAFILE
V$LOGFILE
v$log
V$CONTROLFILE
V$TEMPFILE
[游标]
------------------
SQL> DECLARE
2 CURSOR obj IS
3 select object_id from user_objects;
4 v_id user_objects%TYPE;
5 begin
6 open obj;
7 loop
8 fetch obj into v_id;
9 exit when obj%NOTFOUND;
10 dbms_output.put_line(v_id);
11 end loop;
12 close obj;
13 end;
14 /
1. 使用show errors 定位
show errors procedure ...
2. 使用数据字典user_errors 定位
select line||'/'||position AS "LINE/COL",text error from user_errors where name = '...'
[回滚段与表空间]
-----------------------------
select * from DBA_ROLLBACK_SEGS;
exec sys.dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL( 'XSCP_RBS' );
[开启archivelog mode]
--------------------
1. SHUTDOWN
2. Back up the database.
3. Edit the initialization parameter file
4. STARTUP MOUNT
5. ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
6. SHUTDOWN IMMEDIATE
7. Back up the database.
ALTER SYSTEM ARCHIVE LOG START;
[Disabling Automatic Archiving]
--------------------------------
ALTER SYSTEM ARCHIVE LOG STOP;
[本地表空间]
---------------------
the DBMS_SPACE_ADMIN package provides maintenance procedures
for locally managed tablespaces.
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
[临时表空间]
------------------------------
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
ALTER TABLESPACE lmtemp
ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 2M REUSE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
RESIZE
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 4M;
DROP
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
[修改VIP]
-------------------------------
srvctl modify nodeapps -n god63 -A 10.71.164.67/255.255.255.0/en0
srvctl modify nodeapps -n god64 -A 10.71.164.68/255.255.255.0/en0
[修改 resource_limit]
-----------------------------
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
[PROFILE]
---------------------------------
SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;
SELECT * FROM DBA_TS_QUOTAS;
SELECT * FROM DBA_PROFILES ORDER BY PROFILE;
[FLASH]
-------------------
db_recovery_file_dest string +RAW_FLA
db_recovery_file_dest_size big integer 20G
db_flashback_retention_target integer 1440 (in minutes)
log_archive_dest_1 string LOCATION=+RAW_FLA/
[RMAN]
---------------------------
rman @cmd.txt nocatalog log /opt/oracle/cmd.log append
cmd.txt
----------
CONNECT TARGET /
LIST BACKUP;
EXIT;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+RAW_FLA/controlfile_%F';
select ts.name as tsname,data.name as datafile,BYTES from v$tablespace ts, v$datafile data where ts.TS# = data.TS# ;
RMAN> list backup of controlfile;
RMAN> show all;
RMAN> list backup;
RMAN> backup database ;
restore spfile to '/dev/vgora/rlv_spfile' from '/oracle/arch/c-1197148405-20090604-09'
SQL
-------------------------
select msgid, case when errorno = 2 then errorno * 10 end from scp_error;
select dt2-dt1 from (select to_timestamp('29-feb-2000 01:02:03.122000','dd-mon-yyyy hh24:mi:ss.ff') dt1, to_timestamp('15-mar-2001 11:22:33.000000','dd-mon-yyyy hh24:mi:ss.ff') dt2 from dual);
HP 动态库搜索路径: SHLIB_PATH
CREATE TABLE DUMMY (DUMMY NUMBER);
SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex;
select file_number , sum(bytes)/(1024*1024) from v$asm_file group by file_number;
[logfile]
------------------------
alter database add logfile instance 'orc1' group 6 ('+RAW_ORA/orc/redo06.log') SIZE 300M;
thread 其实是指instance ID
ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;
[卸载HOME]
-------------------
runInstaller -deinstall
[flash_recovery_area_usage]
-------------------------------
select file_type,percent_space_used,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES from v$flash_recovery_area_usage;
dbms_crypto.hash( utl_raw.cast_to_raw('123/ad/78'), dbms_crypto.hash_sh1 )
[注意]
-----------------
对列应用函数TRIM 很容易导致无法使用该列上现有的索引
[使用create directory语句创建目录对象]
--------------------------------------
SQL>create directory dump_dir as '/oracle/datapump/dumps'
SQL>create directory log_dir as '/oracle/datapump/logs'
将目录对设置为公共读写
SQL>grant read,write on directory dump_dir to public;
授权
SQL>grant read,write on directory dump_dir to scott
execute dbms_stats.gather_table_stats(ownname => '$SCHEMA',tabname => 'customer' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
[获取日志文件使用率:]
---------------------------
如何查询redo logfile的使用率
获得Redo Block Size的非典型方法
隐含参数_disable_logging的几点说明
转储日志文件头 获得日志信息
Oracle Diag:如何处理ORA-600 2662错误
redo logfile的使用,不能通过v$视图查询，但是我们可以通过底层视图得到.
首先介绍一下引用到的视图及字段.
x$kccle---- [K]ernel [C]ache [C]ontrolfile management [L]ogfil[E] record 这个视图记录了logfile的使用情况
其中,
LESIZ ------logfile大小(以逻辑块表示)
LESEQ------log sequence #
LEBSZ------logfile逻辑块大小
顺便说一下，redo logfile是以操作系统块为单位的.所以，这里的LEBSZ就是OS的块大小.
如果我们需要获取OS块大小，就可以从这里查询得到:
SQL>select max(lebsz) lbsize from x$kccle;
LBSIZE
----------
512
x$kcccp----[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress 检查点增进(progress)
CPODR_SEQ------日志文件的seq #
CPODR_BNO------日志文件中使用块的数量
获取日志文件使用率:
select le.leseq SEQ,100*cp.cpodr_bno/LE.lesiz PCT from x$kcccp cp,x$kccle le WHERE le.leseq=cp.cpodr_seq;
[获取表碎片状况]
--------------------------------
select table_name, num_rows, blocks, avg_row_len, last_analyzed, blocks - round(num_rows * avg_row_len * (100 + pct_free) * 0.01 / 1024 / 8) from dba_tables where blocks - num_rows * avg_row_len * (100 + pct_free) * 0.01 / 1024 / 8 > 12800;
[ASM]
----------------------
mount -v cdrfs -o ro /dev/cd0 /mnt
/etc/init.d/oracleasm create disk ASM_DISK_NAME device_name
select GROUP_NUMBER as num, NAME, SECTOR_SIZE, BLOCK_SIZE, ALLOCATION_UNIT_SIZE as usize, STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;
select GROUP_NUMBER as num, NAME, STATE, TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;
alter diskgroup RAW_ORA dismount;
alter diskgroup all dismount;
create diskgroup RAW_FLA external redundancy disk '/dev/ora_fla'
alter diskgorup RAW_FLA drop disk /dev/rhdisk7;
select name,mode_status, state, disk_number from v$asm_disk;
select name from v$fixed_table where name like 'V%ASM%'
select group_number g#,disk_number d#,name,mount_status,header_status,total_mb,free_mb from v$asm_disk;
SELECT SYS_CONTEXT('sys_cluster_properties','cluster_state') FROM DUAL;
select name, mode_status, state, disk_number,path from v$asm_disk;
select GROUP_NUMBER as num, NAME, STATE, TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;
ALTER DISKGROUP RAW_FLA OFFLINE DISK RAW_FLA_0000;
alter diskgroup ORA_FLA add disk '/dev/rhdisk12' NAME fla_disk2;
alter diskgroup ORA_FLA add disk '/dev/ora_fla' NAME fla_disk2;
ALTER DISKGROUP ORA_FLA DROP DISK FLA_DISK2;
alter system set log_archive_format='ora11g%t_%s_%r.log' scope=spfile;
alter system set log_archive_start=TRUE scope=spfile;
alter system set log_archive_dest_1='LOCATION=+RAW_FLA' scope=spfile;
alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;
alter system set cluster_database=true scope=spfile;
alter system set NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS' scope=spfile;
create diskgroup RAW_FLA external redundancy disk '/dev/ora_fla';
alter database noarchivelog;
alter system set log_archive_start=FALSE;
alter system unset log_archive_dest_1 scope=spfile;
drop diskgroup RAW_FLA INCLUDING CONTENTS ;
alter system set asm_diskstring='/dev/ora_fla','/dev/ora_idx','/dev/raw_ora','/dev/ora_data'
create diskgroup RAW_ORA external redundancy disk '/dev/raw_ora';
create diskgroup RAW_DATA external redundancy disk '/dev/ora_data';
create diskgroup RAW_IDX external redundancy disk '/dev/ora_idx';
create diskgroup RAW_FLA external redundancy disk '/dev/ora_fla';
select name, mode_status, state, disk_number,path from v$asm_disk;
完全关闭数据库
shutdown immediate
在asmcmd 下执行
cp +DG_DATA/ora11g/datafile/smpdatatbs.256.668196179 +DG_DBFILE/ora11g/datafile/smpdatatbs.256.668196179
cp +DG_DATA/ora11g/datafile/ocststbs.dbf +DG_DBFILE/ora11g/datafile/ocststbs.dbf
在sqlplus中执行
startup mount
alter database rename datafile '+DG_DATA/ora11g/datafile/smpdatatbs.256.668196179' to '+DG_DBFILE/ora11g/datafile/smpdatatbs.256.668196179';
alter database rename datafile '+DG_DATA/ora11g/datafile/ocststbs.dbf' to '+DG_DBFILE/ora11g/datafile/ocststbs.dbf';
[用户认证]
-------------------------------
远程登录两种方式
1. 操作系统认证，客户端以oracle登录，AS SYSDBA
2. oracle自身认证， 客户端直接conn sys@ORC1 as sysdba
emca -config dbcontrol db -repos create -cluster
emca -repos drop -cluster
alter user sys identified by oracle;
alter system set os_authent_prefix=NULL scope=spfile;
alter system set remote_login_passwordfile=SHARED scope=spfile;
alter system set remote_os_authent=false scope=spfile;
orapwd file=/oracle/db/orapwdorc1 password=oracle entries=3 force=y ignorecase=y nosysdba=n
create user pubx identified by pubx default tablespace users temporary tablespace temp;
[删除重复记录]
-------------------------
1、查询主键名
select * from USER_CONSTRAINTS where table_name='W_UVS_MESSAGE' and constraint_type='P';
2、删除主键
ALTER TABLE W_UVS_MESSAGE DROP CONSTRAINT "SYS_C009390"
3、删除重复记录
4、增加主键
ALTER TABLE W_UVS_MESSAGE ADD CONSTRAINT SYS_C009390 PRIMARY KEY (MESSAGEID,LANGUAGEID);
select table_name,constraint_name,column_name,position from user_cons_columns order by table_name,constraint_name,position;
declare
cusor cur is select depno from emp;
TYPE TAB is TABLE of emp.depno%TYPE;
X1 number(5) :=1;
DEP TAB;
begin
open cur;
loop
fetch cur into DEP(X1);
EXIT WHEN cur%NOTFOUND;
X1 := X1 + 1;
end loop;
close cur;
for id in 1..3 loop
dbms_output.put_line(DEP(id));
end loop;
end;
/
1.创建一个空表t
mydb@orc1> create table t
2 as
3 select * from all_objects
4 where 1=0;
Table created.
2.查询为这个表t分配的段，这里初始分配的为65536
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments ;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 65536 1
DATAFILE TABLE 65536 1
3.插入数据再观察，t的段增大为2097152
mydb@orc1> insert into t select * from all_objects;
11982 rows created.
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 2097152 17
4.现在回滚，分配的空间显然没释放
mydb@orc1> rollback;
Rollback complete.
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 2097152 17
5.执行收缩，很明显分配的空间释放了
mydb@orc1> alter table t enable row movement;
mydb@orc1> alter table t shrink space cascade;
Table altered.
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments ;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 65536 1
DATAFILE TABLE 65536 1
[数据库恢复]
---------------------------
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
45432830
SQL> select checkpoint_change# from v$datafile_header;
select file#,checkpoint_change#,to_char(CHECKPOINT_TIME,'dd-mon-yyyy hh24:mi:ss') from v$datafile_header;
recover database until time '27-JAN-10 03:00:49';
recover database until change 45412580;
RECOVER DATABASE UNTIL TIME '2010-01-28:02:00:00'
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops,v$sql where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value;
[UNDO]
----------------------
select ((select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name='UNDOTBS1'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes) from dba_data_files where tablespace_name='UNDOTBS1')
"PCT_INUSE"
from dual;
select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name='UNDOTBS1' and status in ('ACTIVE','UNEXPIRED');
select username,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request from v$lock,v$session where v$lock.type = 'TX' and v$lock.sid = v$session.sid and v$session.username = USER;
select XIDUSN,XIDSLOT,XIDSQN from v$transaction;
[csscan]
--------------------
csscan csmig/csmig FULL=y TOCHAR=al32utf8 ARRAY=10240 PROCESS=3
ALTER DATABASE CHARACTER SET
CSALTER DATABASE CHARACTER SET AL32UTF8;
@@CSALTER.PLB
[用户]
-------------------------
create user ops$mydb identified externally default tablespace users temporary tablespace "TEMP";
select scn_to_timestamp(5363744) from dual;
expdp usrdb212/usrdb212 directory=my_dir tables=\(ts_productorder\) dumpfile=ts_productorder.dmp CONTENT=METADATA_ONLY
[锁]
-------------------------
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
select s.sid,s.SERIAL# from v$lock l, dba_objects u, v$session s where OBJECT_NAME='大写表名' and u.OBJECT_ID=l.id1 and l.sid=s.sid;
[ORA-01591]
--------------------------
类似于WEBLOGIC或者TOAD等软件连接数据库时，此时机器忽然断电，机器重启后，ORACLE总是报：ORA-01591: lock held by in-doubt distributed transaction 8.47.144854"，这是为什么呢？原来上次断电时，两阶段提交过程中部分事务已经提交，可是另一些事务尚未
提交，出现了事务不一致的情况。
解决办法如下：
1、使用Oracle DBA用户，查询如下数据字典：select * from dba_2pc_pending
2、强制Rollback或者Commit该事务：
COMMIT FORCE "8.47.144854"; 或者 ROLLBACK FORCE "8.47.144854";
3、再次启动业务即可
_allow_resetlogs_corruption
AUTOTRACE
----------------------------------
SQL> set autotrace traceonly explain
SQL> select * from U_UVS_AttachedBak where OperTimeStamp = ?
...
SQL> set autotrace off
utlxpls.sql
------------------
Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Force serial option for backward compatibility
Rem
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
utlxplp.sql
-------------
Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Use default mode which will display only relevant information
Rem
select * from table(dbms_xplan.display());
Autotrace
---------------------------
select spid,s.sid,s.serial#,p.username,p.program from v$process p, v$session s where p.addr = s.paddr and s.sid = (select sid from v$mystat where rownum = 1);
alter session set SQL_TRACE true;
select * from ...
alter session set SQL_TRACE false;
show parameter user_dump_dest
tkprof ora11g1_ora_$spid.trc outfile
检查外键未加索引
---------------------------
ops$tkyte@ORA10G> column columns format a30 word_wrapped
ops$tkyte@ORA10G> column tablename format a15 word_wrapped
ops$tkyte@ORA10G> column constraint_name format a15 word_wrapped
ops$tkyte@ORA10G> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null)
||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null)
||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3,
cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /
TRACE
-----------------------------------
select c.value || '/' || d.instance_name ||'_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
--and b.audsid = userenv('sessionid')
and b.sid = (select sid from v$mystat where rownum=1)
and c.name = 'user_dump_dest'
SQL> set feedback off
SQL> set serveroutput on
SQL> declare
2 event_level number;
3 begin
4 for event_number in 10000..10999 loop
5 sys.dbms_system.read_ev(event_number, event_level);
6 if (event_level > 0) then
7 sys.dbms_output.put_line(
8 'Event ' ||
9 to_char(event_number) ||
10 ' is set at level ' ||
11 to_char(event_level)
12 );
13 end if;
14 end loop;
15 end;
16 /
exec dbms_system.set_ev(132,660,10046,8,'mydb');
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
[如果查看某个操作系统上的Oracle进程执行的SQL]
-------------------------------------------------------
select sql_text, spid, v$session.program, process from v$sqlarea, v$session, v$process
where v$sqlarea.address =v$session.sql_address
and v$sqlarea.hash_value = v$session.sql_hash_value
and v$session.paddr = v$process.addr
and v$process.spid = 11355;
[LOGMNR]
-----------------------------
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/ora_arch/1_780_716312058.dbf',DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle10/oradata/orcl/redo02.log',DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(options=>16);
SQL> select username,TABLE_SPACE,sql_redo from v$logmnr_contents where rownum < 100;
SQL> @?/rdbms/admin/awrrpt.sql
Enter value for report_type:html
Enter value for num_days:3
Enter value for begin_snap: scf 出问题的时候是3月7号0点，在显示的snap列表中找到包含这个时间的时间段，把那两个snap id
作为begin_snap和end_snap
Enter value for report_name: 直接回车
SQL> @?/rdbms/admin/ashrpt.sql
Enter value for report_type:html
Enter value for begin_time:03/06 23:50
Enter value for duration:180
Enter value for report_name: 直接回车
AJZU-WOPP-VWM2-H98K-XSU6-PP34-O4OZ-CPPN-P
recover database using backup controlfile allow 1 corruption;
hwclock --set --date="3/29/2011 16:45:05"
dbms_workload_repository.CREATE_SNAPSHOT();
[执行计划]
------------------------
select * from table(dbms_xplan.display_cursor('10pgwdhnpv0sn'))
select sql_id,count(1) from dba_hist_active_sess_history
where snap_id between 1355 and 1356 and event='library cache: mutex X' group by sql_id;
select sql_id,count(1) from dba_hist_active_sess_history
where snap_id between 1355 and 1356 and event='enq: TX - row lock contention' group by sql_id;
[longops]查看执行语句的进度
---------------------------
SELECT SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE" FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;
[logmnr]
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/ora_arch/1_780_716312058.dbf',DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle10/oradata/orcl/redo02.log',DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => '14-Jul-2011 16:00:00', ENDTIME => '14-Jul-2011 18:30:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
select timestamp,SQL_REDO from V$LOGMNR_CONTENTS where SEG_OWNER='SCPDB1' and SEG_NAME='U_UVS_BASETAB';
EXECUTE DBMS_LOGMNR.END_LOGMNR();
[primary key]
-------------------------
alter table basetab_eppc drop constraint u261_1476 ;
alter table basetab_eppc add constraint primary key (msisdn);
[NULL 值]
-------------------------
空值表示在行中留下一个空白列。某个行中的空值列并不表示该列有一个零值。空值表示该列没有值。
如果列的数据缺失、不适用、或未知，可用空值表示。一个列只有在不对此列指定NOT NULL限制时才
允许空值。如果设置某列作为表的主键，则该列不允许为空值。应该尽量使有空值的列置于表的末尾，
这样做是因为oracle存储空值的方式。因为空值表示没有值，因此不能与其它的值进行比较，只能用
IS NULL、NOT NULL 来判断是否有值。
[SQL 查询] //把互锁的打印出来 2011-05-30
-------------------------
select ses.sid, ses.serial, ses.object_name, sql.sql_text from v$sqlarea sql,
(
Select decode(s.sql_hash_value,0,s.prev_hash_value,s.sql_hash_value) as hash,
Decode(s.sql_hash_value,0,s.prev_sql_addr,s.sql_address) as addr,
s.sid as sid,
s.serial# as serial,
o.object_name as object_name
from v$session s, v$lock l, dba_objects o
where l.type='TM' and l.id1=o.object_id and l.sid=s.sid and s.sid <> ( select sid from v$mystat where rownum = 1)
) ses
where sql.hash_value = ses.hash and sql.address = ses.addr ;
[undo 使用率]
-----------------------------------
mystat
-------------------
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
mystat2
--------------------
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
[数据空间使用率]
----------------------------------------
select t.tablespace_name,t.Total_MB,ROUND(t.Total_MB-NVL(f.Free_MB,0),2) Used_MB,ROUND((t.Total_MB-NVL(f.Free_MB,0))*100/t.Total_MB,2) Used_Rate,
ROUND(NVL(f.Free_MB,0),2) Free_MB,ROUND(NVL(f.Free_MB,0)*100/t.Total_MB,2) Free_Rate
from (select tablespace_name,sum(bytes)/1024/1024 Total_MB
from dba_data_files
group by tablespace_name
) t,
(select tablespace_name,sum(bytes)/1024/1024 Free_MB
from dba_free_space
group by tablespace_name
) f
where t.tablespace_name = f.tablespace_name(+);
[undo 使用率]
---------------------------------
select a.tablespace_name,
used_undo,
total_undo,
trunc(used_undo / total_undo * 100, 2) || '%' used_rag
from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
from dba_undo_extents
where status = 'ACTIVE'
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) total_undo
from dba_data_files
where tablespace_name in
(select value
from v$spparameter
where name = 'undo_tablespace'
and (sid = (select instance_name from v$instance) or
sid = '*'))
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
[监控谁在使用UNDO]
-----------------------------------------
SELECT r.name rbs,
nvl(s.username, 'None') ouser,
s.osuser client,
p.username puser,
s.sid,
s.serial#,
p.spid pid,
TO_CHAR(sysdate - (s.last_call_et) / 86400, 'mm/dd/yyhh24:mi:ss') as last_txn,
trunc(t.used_ublk * TO_NUMBER(x.value) / 1024 / 1024,2) as undo_mb
FROM v$process p,
v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND s.paddr = p.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
ORDER BY r.name
[数据库一致性]
-----------------------------------
select checkpoint_change# from v$database;
select * from v$log;
select file#,ts#,status,CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
select file#,status,recover,ts#,CHECKPOINT_CHANGE# from v$datafile_header;
select file#, status, tablespace_name,name, dbms_flashback.get_system_change_number CUR_SCN,
to_char(checkpoint_time,'YYYY-MM-DD HH24:MI:SS') CKPT_DT,checkpoint_change# CKPT_SCN, checkpoint_count CKPT_CNT
from v$datafile_header;
[如何找到占用CPU极高的进程执行的SQL语句]
-----------------------------------
select sql_text from v$sqlarea where (hash_value,address) in
( Select decode(s.sql_hash_value,0,s.prev_hash_value,s.sql_hash_value),
Decode(s.sql_hash_value,0,s.prev_sql_addr,s.sql_address)
From v$session s where (s.paddr = (Select addr from v$process where spid='3883'))
);
[何知道哪个SQL最消耗UNDO 表空间]
-------------------
select l.SQL_TEXT,t.UBABLK
from v$session s ,v$transaction t,v$sql l
where s.taddr=t.addr and s.PREV_HASH_VALUE=l.HASH_VALUE
order by UBABLK
[数据空间大小]
--------------------------------
select df.tablespace_name "Tablespace", round(df.bytes/(1024*1024)) "Total(MB)", round(sum(fs.bytes)/(1024*1024)) "Free(MB)", round((df.bytes-sum(fs.bytes))*100/df.bytes) "% Used" from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df where fs.tablespace_name = df.tablespace_name group by df.tablespace_name, df.bytes;
select b.tablespace_name,mbytes_alloc,mbytes_free
from
(select round(sum(bytes)/1024/1024) mbytes_free,tablespace_name from dba_free_space group by tablespace_name ) a,
(select round(sum(bytes)/1024/1024) mbytes_alloc,tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name(+) = b.tablespace_name;
a.tablespace_name (+)= b.tablespace_name 右连接 保留b.tablespace_name的所有项
a.tablespace_name = b.tablespace_name(+) 左连接 保留a.tablespace_name的所有项
[表分区]
---------------------------------
select partition_name,PARTITION_POSITION, TABLE_NAME from user_tab_partitions order by table_name,PARTITION_POSITION ;
begin
for x in ( select partition_name from user_tab_partitions where table_name = 'BIG_TABLE2' )
loop
execute immediate 'alter table big_table2 move partition ' || x.partition_name;
end loop;
end;
/
[JOBS]
----------------------------------
select owner,job_name,JOB_STYLE,ENABLED,state from dba_scheduler_jobs;
exec dbms_scheduler.disable(name =>'EXFSYS.RLM$SCHDNEGACTION') ;
exec dbms_scheduler.disable(name =>'EXFSYS.RLM$EVTCLEANUP') ;
select job,what,to_char(next_date, 'yyyy/mm/dd hh24:mi:ss'),broken,failures from dba_jobs where BROKEN not in ('N','n');
[表存储]
----------------------------------
create table tab2 nologging STORAGE(INITIAL 1M next 1048576) TABLESPACE users as select * from my_tables;
select PCT_FREE,PCT_USED,LOGGING,NUM_ROWS, BLOCKS,CHAIN_CNT,AVG_SPACE,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables ;
[外部表]
----------------------------------
从控制文件创建外部表
sqlldr mydb/mydb control=ts_productorder.ctl external_table=GENERATE_ONLY
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/tkyte'
外部表定义 [[
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'
LOGFILE 'demo1.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255) ,
"DNAME" CHAR(255),
"LOC" CHAR(255),
)
)
location
(
'demo1.ctl'
)
) REJECT LIMIT UNLIMITED
]] //外部表定义
sed -e '/Loader/,/CREATE TABLE/d' -e '/statements to cleanup/,/CPU time/d' -e '/load internal/d' -e '/--/d'
sed -n -e '/CREATE TABLE.*SYS_SQLLDR/,/REJECT LIMIT/p'
sed -n -e '/INSERT.*INTO/,/FROM.*SYS_SQLLDR/p'
[统计更新]
-----------------
select dbms_metadata.get_ddl('INDEX','SYS_C0017095','SCU') from dual; --ARGS: TYPE,NAME,SCHEMA
execute dbms_stats.gather_table_stats(ownname => '$SCHEMA',tabname => 'w_uvs_parentchild' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'w_uvs_rechgmanlog' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'u_uvs_attached' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'u_uvs_basetab' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
select dbms_stats.to_estimate_percent_type(dbms_stats.get_param('ESTIMATE_PERCENT')) from dual;
DBMS_STATS.AUTO_SAMPLE_SIZE
execute dbms_stats.gather_table_stats(ownname => user,tabname => 'w_uvs_rechgmanlog', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'for all indexed columns',cascade => true);
[impdb/expdp]
-------------------
impdp mydb/mydb DIRECTORY=MY_DIR DUMPFILE=ts_productorder.dmp REMAP_SCHEMA=USRDB212:mydb REMAP_TABLESPACE=CBS_USER_DAT:MYTBS
[未知]
-------------------------
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm in ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size');
[失效对象]
-------------------
select a.owner oown, a.object_name oname, a.object_type otype, 'Missing Package Body' prob
from sys.dba_objects a
where a.object_type = 'PACKAGE'
and a.owner = &ownr
and not exists
(select 'x' from dba_objects b
where b.object_name = a.object_name
and b.owner = a.owner and b.object_type = 'PACKAGE BODY')
union
select owner oown, object_name oname, object_type otype, 'Invalid Object' prob
from sys.dba_objects
where object_type in
('PROCEDURE','PACKAGE','FUNCTION','TRIGGER','PACKAGE BODY')
and owner = &ownr
and status != 'VALID'
order by 1,2,3,4;"
[环境变量]
-------------------
select userenv('lang') from dual;
SELECT SYS_CONTEXT ('USERENV', 'service_name') from dual;
[分析函数]
-------------------------
select b.col-a.col
(select rownum,l.first_time,to_number(p.name) instid from v$log_history l,v$parameter p where l.thread# = p.name ) a,
(select rownum,col from table) b
where a.rownum+1=b.rownum;
var c1 number;
exec select to_number(value) into :c1 from v$parameter where name = 'thread';
select a.thread#,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') pre,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') next
from
(select rownum as r,thread#,first_time from v$log_history where first_time>sysdate-3 and thread# = (select to_number(value) from v$parameter where name = 'thread')) a,
(select rownum as r,thread#,first_time from v$log_history where first_time>sysdate-3 and thread# = (select to_number(value) from v$parameter where name = 'thread')) b
where a.r+1=b.r and (a.first_time + 1/24/60) > b.first_time ;
select thread#,to_char(pre,'yyyy-mm-dd hh24:mi:ss') prev,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') next
from (SELECT thread#,first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-3 and thread# = (select INSTANCE_NUMBER from v$instance) )
WHERE pre + 20/24/60 > first_time;
SELECT first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-1 and thread# = (select to_number(value) from v$parameter where name = 'thread') and pre is null;
SELECT thread#, to_char(first_time,'yyyy-mm-dd hh24:mi:ss'), to_char((lag(first_time) over(ORDER BY 1)),'yyyy-mm-dd hh24:mi:ss') pre from v$log_history where first_time>sysdate-3 and thread# = 1
SELECT first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-1 and
SELECT thread#,first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-1 and thread# = (select INSTANCE_NUMBER from v$instance );
select thread#,to_char(pre,'yyyy-mm-dd hh24:mi:ss') prev,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') next from (SELECT thread#,first_time, lag(first_time) over(ORDER BY 1) pre from v$log_history where first_time>sysdate-3 and thread# = (select INSTANCE_NUMBER from v$instance) ) WHERE pre + 20/24/60 > first_time;
[关于处理LONG/LOB字段的案例]
----------------------------------------
I:LONG类型
long类型不能通过MOVE来传输特别提示，尽量不要用LONG类型，特难管理!!!
1，LONG不能使用insert 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 use_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 子句指定新的表空间）。
* 把数据转移过来
方法一：用COPY的方法：
copy frombigboar/bigboar@bigboar_sidinsert 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/bigboar 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，必须要单独来进行MOVE，语法如下如：
alter table t321 move tablespace users;
alter table t321 move lob(en) store as (tablespace users);
III: 另外，据说DBMS_REDEFINITION包可以提供整理LONG/LOB字段的一些方便，没用过。
[SCN]
---------------------
SQL> variable scn number
SQL> exec :scn := dbms_flashback.get_system_change_number
SQL> print SCN
SQL> select * from t as of scn :scn;
select scn_to_timestamp(dbms_flashback.get_system_change_number) from dual;
数据字典
$ORACLE_HOME/rdbms/admin/catalog.sql
视图
V$DATAFILE
V$LOGFILE
v$log
V$CONTROLFILE
V$TEMPFILE
[游标]
------------------
SQL> DECLARE
2 CURSOR obj IS
3 select object_id from user_objects;
4 v_id user_objects%TYPE;
5 begin
6 open obj;
7 loop
8 fetch obj into v_id;
9 exit when obj%NOTFOUND;
10 dbms_output.put_line(v_id);
11 end loop;
12 close obj;
13 end;
14 /
1. 使用show errors 定位
show errors procedure ...
2. 使用数据字典user_errors 定位
select line||'/'||position AS "LINE/COL",text error from user_errors where name = '...'
[回滚段与表空间]
-----------------------------
select * from DBA_ROLLBACK_SEGS;
exec sys.dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL( 'XSCP_RBS' );
[开启archivelog mode]
--------------------
1. SHUTDOWN
2. Back up the database.
3. Edit the initialization parameter file
4. STARTUP MOUNT
5. ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
6. SHUTDOWN IMMEDIATE
7. Back up the database.
ALTER SYSTEM ARCHIVE LOG START;
[Disabling Automatic Archiving]
--------------------------------
ALTER SYSTEM ARCHIVE LOG STOP;
[本地表空间]
---------------------
the DBMS_SPACE_ADMIN package provides maintenance procedures
for locally managed tablespaces.
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
[临时表空间]
------------------------------
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
ALTER TABLESPACE lmtemp
ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 2M REUSE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
RESIZE
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 4M;
DROP
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
[修改VIP]
-------------------------------
srvctl modify nodeapps -n god63 -A 10.71.164.67/255.255.255.0/en0
srvctl modify nodeapps -n god64 -A 10.71.164.68/255.255.255.0/en0
[修改 resource_limit]
-----------------------------
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
[PROFILE]
---------------------------------
SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;
SELECT * FROM DBA_TS_QUOTAS;
SELECT * FROM DBA_PROFILES ORDER BY PROFILE;
[FLASH]
-------------------
db_recovery_file_dest string +RAW_FLA
db_recovery_file_dest_size big integer 20G
db_flashback_retention_target integer 1440 (in minutes)
log_archive_dest_1 string LOCATION=+RAW_FLA/
[RMAN]
---------------------------
rman @cmd.txt nocatalog log /opt/oracle/cmd.log append
cmd.txt
----------
CONNECT TARGET /
LIST BACKUP;
EXIT;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+RAW_FLA/controlfile_%F';
select ts.name as tsname,data.name as datafile,BYTES from v$tablespace ts, v$datafile data where ts.TS# = data.TS# ;
RMAN> list backup of controlfile;
RMAN> show all;
RMAN> list backup;
RMAN> backup database ;
restore spfile to '/dev/vgora/rlv_spfile' from '/oracle/arch/c-1197148405-20090604-09'
SQL
-------------------------
select msgid, case when errorno = 2 then errorno * 10 end from scp_error;
select dt2-dt1 from (select to_timestamp('29-feb-2000 01:02:03.122000','dd-mon-yyyy hh24:mi:ss.ff') dt1, to_timestamp('15-mar-2001 11:22:33.000000','dd-mon-yyyy hh24:mi:ss.ff') dt2 from dual);
HP 动态库搜索路径: SHLIB_PATH
CREATE TABLE DUMMY (DUMMY NUMBER);
SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex;
select file_number , sum(bytes)/(1024*1024) from v$asm_file group by file_number;
[logfile]
------------------------
alter database add logfile instance 'orc1' group 6 ('+RAW_ORA/orc/redo06.log') SIZE 300M;
thread 其实是指instance ID
ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;
[卸载HOME]
-------------------
runInstaller -deinstall
[flash_recovery_area_usage]
-------------------------------
select file_type,percent_space_used,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES from v$flash_recovery_area_usage;
dbms_crypto.hash( utl_raw.cast_to_raw('123/ad/78'), dbms_crypto.hash_sh1 )
[注意]
-----------------
对列应用函数TRIM 很容易导致无法使用该列上现有的索引
[使用create directory语句创建目录对象]
--------------------------------------
SQL>create directory dump_dir as '/oracle/datapump/dumps'
SQL>create directory log_dir as '/oracle/datapump/logs'
将目录对设置为公共读写
SQL>grant read,write on directory dump_dir to public;
授权
SQL>grant read,write on directory dump_dir to scott
execute dbms_stats.gather_table_stats(ownname => '$SCHEMA',tabname => 'customer' ,estimate_percent => null ,method_opt => 'for all indexed columns',cascade => true);
[获取日志文件使用率:]
---------------------------
如何查询redo logfile的使用率
获得Redo Block Size的非典型方法
隐含参数_disable_logging的几点说明
转储日志文件头 获得日志信息
Oracle Diag:如何处理ORA-600 2662错误
redo logfile的使用,不能通过v$视图查询，但是我们可以通过底层视图得到.
首先介绍一下引用到的视图及字段.
x$kccle---- [K]ernel [C]ache [C]ontrolfile management [L]ogfil[E] record 这个视图记录了logfile的使用情况
其中,
LESIZ ------logfile大小(以逻辑块表示)
LESEQ------log sequence #
LEBSZ------logfile逻辑块大小
顺便说一下，redo logfile是以操作系统块为单位的.所以，这里的LEBSZ就是OS的块大小.
如果我们需要获取OS块大小，就可以从这里查询得到:
SQL>select max(lebsz) lbsize from x$kccle;
LBSIZE
----------
512
x$kcccp----[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress 检查点增进(progress)
CPODR_SEQ------日志文件的seq #
CPODR_BNO------日志文件中使用块的数量
获取日志文件使用率:
select le.leseq SEQ,100*cp.cpodr_bno/LE.lesiz PCT from x$kcccp cp,x$kccle le WHERE le.leseq=cp.cpodr_seq;
[获取表碎片状况]
--------------------------------
select table_name, num_rows, blocks, avg_row_len, last_analyzed, blocks - round(num_rows * avg_row_len * (100 + pct_free) * 0.01 / 1024 / 8) from dba_tables where blocks - num_rows * avg_row_len * (100 + pct_free) * 0.01 / 1024 / 8 > 12800;
[ASM]
----------------------
mount -v cdrfs -o ro /dev/cd0 /mnt
/etc/init.d/oracleasm create disk ASM_DISK_NAME device_name
select GROUP_NUMBER as num, NAME, SECTOR_SIZE, BLOCK_SIZE, ALLOCATION_UNIT_SIZE as usize, STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;
select GROUP_NUMBER as num, NAME, STATE, TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;
alter diskgroup RAW_ORA dismount;
alter diskgroup all dismount;
create diskgroup RAW_FLA external redundancy disk '/dev/ora_fla'
alter diskgorup RAW_FLA drop disk /dev/rhdisk7;
select name,mode_status, state, disk_number from v$asm_disk;
select name from v$fixed_table where name like 'V%ASM%'
select group_number g#,disk_number d#,name,mount_status,header_status,total_mb,free_mb from v$asm_disk;
SELECT SYS_CONTEXT('sys_cluster_properties','cluster_state') FROM DUAL;
select name, mode_status, state, disk_number,path from v$asm_disk;
select GROUP_NUMBER as num, NAME, STATE, TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;
ALTER DISKGROUP RAW_FLA OFFLINE DISK RAW_FLA_0000;
alter diskgroup ORA_FLA add disk '/dev/rhdisk12' NAME fla_disk2;
alter diskgroup ORA_FLA add disk '/dev/ora_fla' NAME fla_disk2;
ALTER DISKGROUP ORA_FLA DROP DISK FLA_DISK2;
alter system set log_archive_format='ora11g%t_%s_%r.log' scope=spfile;
alter system set log_archive_start=TRUE scope=spfile;
alter system set log_archive_dest_1='LOCATION=+RAW_FLA' scope=spfile;
alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;
alter system set cluster_database=true scope=spfile;
alter system set NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS' scope=spfile;
create diskgroup RAW_FLA external redundancy disk '/dev/ora_fla';
alter database noarchivelog;
alter system set log_archive_start=FALSE;
alter system unset log_archive_dest_1 scope=spfile;
drop diskgroup RAW_FLA INCLUDING CONTENTS ;
alter system set asm_diskstring='/dev/ora_fla','/dev/ora_idx','/dev/raw_ora','/dev/ora_data'
create diskgroup RAW_ORA external redundancy disk '/dev/raw_ora';
create diskgroup RAW_DATA external redundancy disk '/dev/ora_data';
create diskgroup RAW_IDX external redundancy disk '/dev/ora_idx';
create diskgroup RAW_FLA external redundancy disk '/dev/ora_fla';
select name, mode_status, state, disk_number,path from v$asm_disk;
完全关闭数据库
shutdown immediate
在asmcmd 下执行
cp +DG_DATA/ora11g/datafile/smpdatatbs.256.668196179 +DG_DBFILE/ora11g/datafile/smpdatatbs.256.668196179
cp +DG_DATA/ora11g/datafile/ocststbs.dbf +DG_DBFILE/ora11g/datafile/ocststbs.dbf
在sqlplus中执行
startup mount
alter database rename datafile '+DG_DATA/ora11g/datafile/smpdatatbs.256.668196179' to '+DG_DBFILE/ora11g/datafile/smpdatatbs.256.668196179';
alter database rename datafile '+DG_DATA/ora11g/datafile/ocststbs.dbf' to '+DG_DBFILE/ora11g/datafile/ocststbs.dbf';
[用户认证]
-------------------------------
远程登录两种方式
1. 操作系统认证，客户端以oracle登录，AS SYSDBA
2. oracle自身认证， 客户端直接conn sys@ORC1 as sysdba
emca -config dbcontrol db -repos create -cluster
emca -repos drop -cluster
alter user sys identified by oracle;
alter system set os_authent_prefix=NULL scope=spfile;
alter system set remote_login_passwordfile=SHARED scope=spfile;
alter system set remote_os_authent=false scope=spfile;
orapwd file=/oracle/db/orapwdorc1 password=oracle entries=3 force=y ignorecase=y nosysdba=n
create user pubx identified by pubx default tablespace users temporary tablespace temp;
[删除重复记录]
-------------------------
1、查询主键名
select * from USER_CONSTRAINTS where table_name='W_UVS_MESSAGE' and constraint_type='P';
2、删除主键
ALTER TABLE W_UVS_MESSAGE DROP CONSTRAINT "SYS_C009390"
3、删除重复记录
4、增加主键
ALTER TABLE W_UVS_MESSAGE ADD CONSTRAINT SYS_C009390 PRIMARY KEY (MESSAGEID,LANGUAGEID);
select table_name,constraint_name,column_name,position from user_cons_columns order by table_name,constraint_name,position;
declare
cusor cur is select depno from emp;
TYPE TAB is TABLE of emp.depno%TYPE;
X1 number(5) :=1;
DEP TAB;
begin
open cur;
loop
fetch cur into DEP(X1);
EXIT WHEN cur%NOTFOUND;
X1 := X1 + 1;
end loop;
close cur;
for id in 1..3 loop
dbms_output.put_line(DEP(id));
end loop;
end;
/
1.创建一个空表t
mydb@orc1> create table t
2 as
3 select * from all_objects
4 where 1=0;
Table created.
2.查询为这个表t分配的段，这里初始分配的为65536
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments ;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 65536 1
DATAFILE TABLE 65536 1
3.插入数据再观察，t的段增大为2097152
mydb@orc1> insert into t select * from all_objects;
11982 rows created.
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 2097152 17
4.现在回滚，分配的空间显然没释放
mydb@orc1> rollback;
Rollback complete.
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 2097152 17
5.执行收缩，很明显分配的空间释放了
mydb@orc1> alter table t enable row movement;
mydb@orc1> alter table t shrink space cascade;
Table altered.
mydb@orc1> select segment_name,segment_type,bytes,extents from user_segments ;
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
------------------------------ ------------------ ---------- ----------
T TABLE 65536 1
DATAFILE TABLE 65536 1
[数据库恢复]
---------------------------
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
45432830
SQL> select checkpoint_change# from v$datafile_header;
select file#,checkpoint_change#,to_char(CHECKPOINT_TIME,'dd-mon-yyyy hh24:mi:ss') from v$datafile_header;
recover database until time '27-JAN-10 03:00:49';
recover database until change 45412580;
RECOVER DATABASE UNTIL TIME '2010-01-28:02:00:00'
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops,v$sql where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value;
[UNDO]
----------------------
select ((select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name='UNDOTBS1'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes) from dba_data_files where tablespace_name='UNDOTBS1')
"PCT_INUSE"
from dual;
select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name='UNDOTBS1' and status in ('ACTIVE','UNEXPIRED');
select username,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request from v$lock,v$session where v$lock.type = 'TX' and v$lock.sid = v$session.sid and v$session.username = USER;
select XIDUSN,XIDSLOT,XIDSQN from v$transaction;
[csscan]
--------------------
csscan csmig/csmig FULL=y TOCHAR=al32utf8 ARRAY=10240 PROCESS=3
ALTER DATABASE CHARACTER SET
CSALTER DATABASE CHARACTER SET AL32UTF8;
@@CSALTER.PLB
[用户]
-------------------------
create user ops$mydb identified externally default tablespace users temporary tablespace "TEMP";
select scn_to_timestamp(5363744) from dual;
expdp usrdb212/usrdb212 directory=my_dir tables=\(ts_productorder\) dumpfile=ts_productorder.dmp CONTENT=METADATA_ONLY
[锁]
-------------------------
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
select s.sid,s.SERIAL# from v$lock l, dba_objects u, v$session s where OBJECT_NAME='大写表名' and u.OBJECT_ID=l.id1 and l.sid=s.sid;
[ORA-01591]
--------------------------
类似于WEBLOGIC或者TOAD等软件连接数据库时，此时机器忽然断电，机器重启后，ORACLE总是报：ORA-01591: lock held by in-doubt distributed transaction 8.47.144854"，这是为什么呢？原来上次断电时，两阶段提交过程中部分事务已经提交，可是另一些事务尚未
提交，出现了事务不一致的情况。
解决办法如下：
1、使用Oracle DBA用户，查询如下数据字典：select * from dba_2pc_pending
2、强制Rollback或者Commit该事务：
COMMIT FORCE "8.47.144854"; 或者 ROLLBACK FORCE "8.47.144854";
3、再次启动业务即可
_allow_resetlogs_corruption
AUTOTRACE
----------------------------------
SQL> set autotrace traceonly explain
SQL> select * from U_UVS_AttachedBak where OperTimeStamp = ?
...
SQL> set autotrace off
utlxpls.sql
------------------
Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Force serial option for backward compatibility
Rem
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
utlxplp.sql
-------------
Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Use default mode which will display only relevant information
Rem
select * from table(dbms_xplan.display());
Autotrace
---------------------------
select spid,s.sid,s.serial#,p.username,p.program from v$process p, v$session s where p.addr = s.paddr and s.sid = (select sid from v$mystat where rownum = 1);
alter session set SQL_TRACE true;
select * from ...
alter session set SQL_TRACE false;
show parameter user_dump_dest
tkprof ora11g1_ora_$spid.trc outfile
检查外键未加索引
---------------------------
ops$tkyte@ORA10G> column columns format a30 word_wrapped
ops$tkyte@ORA10G> column tablename format a15 word_wrapped
ops$tkyte@ORA10G> column constraint_name format a15 word_wrapped
ops$tkyte@ORA10G> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null)
||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null)
||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3,
cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /
TRACE
-----------------------------------
select c.value || '/' || d.instance_name ||'_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
--and b.audsid = userenv('sessionid')
and b.sid = (select sid from v$mystat where rownum=1)
and c.name = 'user_dump_dest'
SQL> set feedback off
SQL> set serveroutput on
SQL> declare
2 event_level number;
3 begin
4 for event_number in 10000..10999 loop
5 sys.dbms_system.read_ev(event_number, event_level);
6 if (event_level > 0) then
7 sys.dbms_output.put_line(
8 'Event ' ||
9 to_char(event_number) ||
10 ' is set at level ' ||
11 to_char(event_level)
12 );
13 end if;
14 end loop;
15 end;
16 /
exec dbms_system.set_ev(132,660,10046,8,'mydb');
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
[如果查看某个操作系统上的Oracle进程执行的SQL]
-------------------------------------------------------
select sql_text, spid, v$session.program, process from v$sqlarea, v$session, v$process
where v$sqlarea.address =v$session.sql_address
and v$sqlarea.hash_value = v$session.sql_hash_value
and v$session.paddr = v$process.addr
and v$process.spid = 11355;
[LOGMNR]
-----------------------------
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/ora_arch/1_780_716312058.dbf',DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle10/oradata/orcl/redo02.log',DBMS_LOGMNR.ADDFILE);
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(options=>16);
SQL> select username,TABLE_SPACE,sql_redo from v$logmnr_contents where rownum < 100;
SQL> @?/rdbms/admin/awrrpt.sql
Enter value for report_type:html
Enter value for num_days:3
Enter value for begin_snap: scf 出问题的时候是3月7号0点，在显示的snap列表中找到包含这个时间的时间段，把那两个snap id
作为begin_snap和end_snap
Enter value for report_name: 直接回车
SQL> @?/rdbms/admin/ashrpt.sql
Enter value for report_type:html
Enter value for begin_time:03/06 23:50
Enter value for duration:180
Enter value for report_name: 直接回车
瓜分20万奖金 获得内推名额 丰厚实物奖励 易参与易上手
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)