--
-- analyze_comp.sql
--
BEGIN
SYS.DBMS_UTILITY.ANALYZE_SCHEMA ( '&OWNER','COMPUTE');
END;
/
--
-- 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.每周处理程序
--
-- 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;
--
-- 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;
--
-- no_pk.sql
--
-- To find tables without PK constraint
--
-- 11/2/98
SELECT table_name
FROM all_tables
WHERE wner = '&OWNER'
MINUS
SELECT table_name
FROM all_constraints
WHERE wner = '&&OWNER'
AND constraint_type = 'P' ;
--
-- disPK.sql
--
-- To find out which primary keys are disabled
--
-- 11/30/98
SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE wner = '&OWNER' AND status = 'DISABLED’ AND constraint_type = 'P';
--
-- 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 wner = '&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
--
-- 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 wner = '&OWNER'
/
--
-- 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 wner = '&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 wner = '&OWNER2'
order by table_name, column_name
--
-- 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
posted on 2012-08-29 15:36
kxbin 阅读(152)
评论(0) 编辑 收藏 所属分类:
ORACLE 、
转发