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

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

上一页  [1] [2] [3] [4]  下一页