dba.txt

DBA具体操作方法以及常见问题解决办法

?WHERE se.saddr=su.session_addr;

?

?

DBA日常维护程序:

--?free.sql

--To?verify?free?space?in?tablespaces??

--Minimum?amount?of?free?space?

--document?your?thresholds:??

--?=??m??

SQL>SELECT?tablespace_name,?sum?(?blocks?)?as?free_blk?,?trunc?(?sum?(?bytes?)/(1024*1024)?)?as?free_m,?max?(?bytes?)?/?(1024)?as?big_chunk_k,?count?(*)?as?num_chunks?FROM?dba_free_space?GROUP?BY?tablespace_name;


1.?Space.sql??

--?space.sql??

--?To?check?free,?pct_free,?and?allocated?space?within?a?tablespace??

--?11/24/98??

SQL> col tablespace_name format a20
SQL>SELECT?tablespace_name,largest_free_chunk,nr_free_chunks,sum_alloc_blocks,sum_free_blocks,to_char(100*sum_free_blocks/sum_alloc_blocks,?'09.99')?||?'%'AS?pct_free?FROM?(?SELECT?tablespace_name?,?sum(blocks)?AS?sum_alloc_blocks?FROM?dba_data_files?GROUP?BY?tablespace_name)
,(SELECT?tablespace_name?AS?fs_ts_name,max(blocks)?AS?largest_free_chunk,count(blocks)?AS?nr_free_chunks,sum(blocks)?AS?sum_free_blocks?FROM

dba_free_space GROUP?BY?tablespace_name)?WHERE?tablespace_name=fs_ts_name;

2.?analyze5pct.sql??

--?analyze5pct.sql??

--?To?analyze?tables?and?indexes?quickly,?using?a?5%?sample?size??

--?(do?not?use?this?script?if?you?are?performing?the?overnight??

--?collection?of?volumetric?data)??

--?11/30/98??

BEGIN??
dbms_utility.analyze_schema?('&OWNER',?'ESTIMATE',?NULL,?5)?;??
END?;??
/??

3.?nr_extents.sql??

--?nr_extents.sql??

--?To?find?out?any?object?reaching???

--?extents,?and?manually?upgrade?it?to?allow?unlimited??

--?max_extents?(thus?only?objects?we?*expect*?to?be?big??

--?are?allowed?to?become?big)??

--?11/30/98??

SELECT?e.owner,?e.segment_type?,?e.segment_name?,?count(*)?as?nr_extents,s.max_extents,?to_char(sum(e.bytes)?/?(1024?*?1024)?,?'999,999.90')?as?MB?FROM?dba_extents?e,dba_segments?s?WHERE?e.segment_name?=?s.segment_name?GROUP?BY?e.owner,?e.segment_type,e.segment_name?,?s.max_extents?HAVING?count(*)?>?&THRESHOLD?OR?(?(?s.max_extents?-?count(*)?)??&&THRESHOLD?)?ORDER?BY?count(*)?desc;??

4.?spacebound.sql??

--?spacebound.sql??

--?To?identify?space-bound?objects.?If?all?is?well,?no?rows?are?returned.??

--?If?any?space-bound?objects?are?found,?look?at?value?of?NEXT?extent??

--?size?to?figure?out?what?happened.??

--?Then?use?coalesce?(alter?tablespace??coalesce?.??

--?Lastly,?add?another?datafile?to?the?tablespace?if?needed.??

--?11/30/98??

SELECT?a.table_name,a.next_extent,a.tablespace_name?
FROM?all_tables?a,(SELECT?tablespace_name,max(bytes)?as?big_chunk??
FROM?dba_free_space?GROUP?BY?tablespace_name)?f??
WHERE?f.tablespace_name?=?a.tablespace_name?AND?a.next_extent?>?f.big_chunk;??

B.?每晚处理程序??

1.?mk_volfact.sql??

--?mk_volfact.sql?(only?run?this?once?to?set?it?up;?do?not?run?it?nightly!)??

--?--?Table?UTL_VOL_FACTS??

CREATE?TABLE?utl_vol_facts(??
table_name?VARCHAR2(30),??
num_rows?NUMBER,??
meas_dt?DATE?)??
TABLESPACE?platab??
STORAGE(INITIAL?128k??
NEXT?128k??
PCTINCREASE?0??
MINEXTENTS?1??
MAXEXTENTS?unlimited)??
/??

--?Public?Synonym??

CREATE?PUBLIC?SYNONYM?utl_vol_facts?FOR?&OWNER..utl_vol_facts??

/??

--?Grants?for?UTL_VOL_FACTS??

GRANT?SELECT?ON?utl_vol_facts?TO?public??

/??

2.?analyze_comp.sql??

--??

--?analyze_comp.sql??

--??

BEGIN??

sys.dbms_utility.analyze_schema?(?'&OWNER','COMPUTE');??

END?;??

/??

3.?pop_vol.sql??

--??

--?pop_vol.sql??

--??

insert?into?utl_vol_facts??

select?table_name??

,?NVL?(?num_rows,?0)?as?num_rows??

,?trunc?(?last_analyzed?)?as?meas_dt??

from?all_tables?--?or?just?user_tables??

where?owner?in?('&OWNER')?--?or?a?comma-separated?list?of?owners??

/??

commit??

/??

　??

C.?每周处理程序??

1.?nextext.sql??

--??

--?nextext.sql??

--??

--?To?find?tables?that?don't?match?the?tablespace?default?for?NEXT?extent.??

--?The?implicit?rule?here?is?that?every?table?in?a?given?tablespace?should??

--?use?the?exact?same?value?for?NEXT,?which?should?also?be?the?tablespace's??

--?default?value?for?NEXT.??

--??

--?This?tells?us?what?the?setting?for?NEXT?is?for?these?objects?today.??

--??

--?11/30/98??

SELECT?segment_name,?segment_type,?ds.next_extent?as?Actual_Next??

,?dt.tablespace_name,?dt.next_extent?as?Default_Next??

FROM?dba_tablespaces?dt,?dba_segments?ds??

WHERE?dt.tablespace_name?=?ds.tablespace_name??

AND?dt.next_extent?!=ds.next_extent??

AND?ds.owner?=?UPPER?(?'&OWNER'?)??

ORDER?BY?tablespace_name,?segment_type,?segment_name??

2.?existext.sql??

--??

--?existext.sql??

--??

--?To?check?existing?extents??

--??

--?This?tells?us?how?many?of?each?object's?extents?differ?in?size?from??

--?the?tablespace's?default?size.?If?this?report?shows?a?lot?of?different??

--?sized?extents,?your?free?space?is?likely?to?become?fragmented.?If?so,??

--?this?tablespace?is?a?candidate?for?reorganizing.??

--??

--?12/15/98??

SELECT?segment_name,?segment_type??

,?count(*)?as?nr_exts??

,?sum?(?DECODE?(?dx.bytes,dt.next_extent,0,1)?)?as?nr_illsized_exts??

,?dt.tablespace_name,?dt.next_extent?as?dflt_ext_size??

FROM?dba_tablespaces?dt,?dba_extents?dx??

WHERE?dt.tablespace_name?=?dx.tablespace_name??

AND?dx.owner?=?'&OWNER'??

GROUP?BY?segment_name,?segment_type,?dt.tablespace_name,?dt.next_extent??

3.?No_pk.sql??

--??

--?no_pk.sql??

--??

--?To?find?tables?without?PK?constraint??

--??

--?11/2/98??

SELECT?table_name??

FROM?all_tables??

WHERE?owner?=?'&OWNER'??

MINUS??

SELECT?table_name??

FROM?all_constraints??

WHERE?owner?=?'&&OWNER'??

AND?constraint_type?=?'P'??

4.?disPK.sql??

--??

--?disPK.sql??

--??

--?To?find?out?which?primary?keys?are?disabled??

--??

--?11/30/98??

SELECT?owner,?constraint_name,?table_name,?status??

FROM?all_constraints??

WHERE?owner?=?'&OWNER'?AND?status?=?'DISABLED’?AND?constraint_type?=?'P'??

5.?nonuPK.sql??

--??

--?nonuPK.sql??

--??

--?To?find?tables?with?nonunique?PK?indexes.?Requires?that?PK?names??

--?follow?a?naming?convention.?An?alternative?query?follows?that??

--?does?not?have?this?requirement,?but?runs?more?slowly.??

--??

--?11/2/98??

SELECT?index_name,?table_name,?uniqueness??

FROM?all_indexes??

WHERE?index_name?like?'&PKNAME%'??


 

08 五月, 2007 23:07
五一琐记
Posted by cosio under [ 情感世界 ]
[ (0) 评论 ] | [ (0) 引用 ]

07.05.05 天气睛

阿峰，起床了！老妈这么一叫，我就从床上跳起来，揉了眼睛，在看一下手表才七点多，我就问老妈什么事，（此时，老妈的脸拉的很臭），老妈便很气愤地说.....

事情的经过：早上七点，舅舅要来，老妈很早就去市场买菜，当至一鱼摊买鱼，老妈刚拿出一张五十元给小贩，小贩说这是假钞，叫老妈换一张，老妈就拿出一张一百元，小贩也说这张也是假钞，突然鱼摊主旁边突然闪出一个人，拿这两张假钞也跟说这都是假钞，说要打110报警，此时老妈给鱼摊说打电话给家人，那鱼摊主不肯，此时围观的人很多，过一会儿，110的警察过来，一看情况，就拉着老妈到旁边，说我看你老人家这么老，就把钱没收，也不罚款算了。老妈先前没有遇到这种事情，一下子也愣住，思想也没有转过弯，就回到家里！

于是便出现上面的画面。

老妈越想越不对，说这钱是自己的儿子从银行取出来的钱，怎么可能是假钞，过一会儿，她自己便去一邻居阿姨家，这个阿姨以前也有遇到过这种事情，地点也是这个市场，她自己就上告，终于讨回了一个公道，她也建议我们要去上告，不是说这是件小事，而是公民的责任，让这现象尽可能少发生，让其他人不要在受骗，关键一点，警察的笔录里有记录我们曾使用过假钞的记录，这不是变成我们“身上背一黑锅”。

经过阿姨这么一提醒，我们家人也决定去“平反”....

于是，07.5.6写了一份事情经过，便去警察局报案。巧的是早上昨天值班的警察不在，要等到下午三点左右才上班，在警察局我们就问值班的警察说这个使用假钞对信用等有没有影响，警察说没有什么影响，这个只是一个记录，没有什么的，一听这个，我们想下午就没有在去的必要，抱着“花钱消灾”的心理，我和弟说下午就不用去。可是舅舅和老妈还是说既然都走到这一步，还是去警察局里说清楚......

舅舅和老妈回来已是5.6下午的四点半,后来一听，那个警察的笔录里也说这个假钞是被人调包了，我们两个一听，都一惊，先前的笔录不是说老妈用假钞，啥现在是说被调包....不解!事情就这样子结束

5.8 下班后，给老妈打个电话，邻居的几个阿姨都来家里做客，都为这件事叹不平，而且为我们没有去讨回150RMB而婉惜!电话里从老妈的口气里也听出来，她也是这么想的！想想算了，吃一堑长一智！我再提醒老妈下次遇到类似的情况，首先要镇定.

我想这样子事情应该就告一段落，写下此文，描述一下，谨示别人，希望不要有人犯同样的错误！

小Q 于07.05.08 23:32

 

08 五月, 2007 10:22
Oracle常用的函数
Posted by cosio under [ DBA学习 ]
[ (0) 评论 ] | [ (0) 引用 ]

1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;

A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32


2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C
-- -
赵 A

3.CONCAT
连接两个字符串;
SQL> select concat(010-,88888888)||转23 高乾竞电话 from dual;

高乾竞电话
----------------
010-88888888转23

4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap(smith) upp from dual;

UPP
-----
Smith


5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr(oracle traning,ra,1,2) instring from dual;

INSTRING
---------
9


6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞 3 北京市海锭区 6 9999.99 7


7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower(AaBbCcDd)AaBbCcDd from dual;

AABBCCDD
--------
aabbccdd


8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper(AaBbCcDd) upper from dual;

UPPER
--------
AABBCCDD


9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad(gao,10,*),17,*)from dual;

LPAD(RPAD(GAO,1
-----------------
*******gao*******
不够字符则用*来填满


10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim( gao qian jing , ), ) from dual;

LTRIM(RTRIM(
-------------
gao qian jing


11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr(13088888888,3,8) from dual;

SUBSTR(
--------
08888888


12.RE