数据的维护上oracle,Oracle数据库维护总结

;

-- 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 '&KNAME%'

AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE'

SELECT c.constraint_name, i.tablespace_name, i.uniqueness

FROM all_constraints c , all_indexes i

WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE'

AND c.constraint_type = 'P' AND i.index_name = c.constraint_name

6. mkrebuild_idx.sql

--

-- mkrebuild_idx.sql

--

-- Rebuild indexes to have correct storage parameters

--

-- 11/2/98

SELECT 'alter index ' || index_name || ' rebuild '

, 'tablespace INDEXES storage '

|| ' ( initial 256 K next 256 K pctincrease 0 ) ; '

FROM all_indexes

WHERE ( tablespace_name != 'INDEXES'

OR next_extent != ( 256 * 1024 )

)

AND owner = '&OWNER'

/

7. datatype.sql

--

-- datatype.sql

--

-- To check datatype consistency between two environments

--

-- 11/30/98

SELECT

table_name,

column_name,

data_type,

data_length,

data_precision,

data_scale,

nullable

FROM all_tab_columns -- first environment

WHERE owner = '&OWNER'

MINUS

SELECT

table_name,

column_name,

data_type,

data_length,

data_precision,

data_scale,

nullable

FROM all_tab_columns@&my_db_link -- second environment

WHERE owner = '&OWNER2'

order by table_name, column_name

8. obj_coord.sql

--

-- obj_coord.sql

--

-- To find out any difference in objects between two instances

--

-- 12/08/98

SELECT object_name, object_type

FROM user_objects

MINUS

SELECT object_name, object_type

FROM user_objects@&my_db_link

六. 参考文献

1. Loney, Kevin Oracle8 DBA Handbook

2. Cook, David Database Management from Crisis to Confidence

[]

3. Cox, Thomas B. The Database Administration Maturity Model