2. analyze_comp.sql
analyze_comp.sql
BEGIN
SYS.DBMS_UTILITY.ANALYZE_SCHEMA('&OWNER','COMPUTE');
END;
/
3. pop_vol.sql
pop_vol.sql
insertintoutl_vol_facts
selecttable_name
,NVL(num_rows,0)asnum_rows
,trunc(last_analyzed)asmeas_dt
fromall_tables
or just user_tables
whereownerin('&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.
his tells us what the setting for NEXT is for these objects today.
11/30/98
SELECTsegment_name,segment_type,ds.next_extentasActual_Next
,dt.tablespace_name,dt.next_extentasDefault_Next
FROMdba_tablespacesdt,dba_segmentsds
WHEREdt.tablespace_name=ds.tablespace_name
ANDdt.next_extent!=ds.next_extent
ANDds.owner=UPPER('&OWNER')
ORDERBYtablespace_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
SELECTsegment_name,segment_type
,count(*)asnr_exts
,sum(DECODE(dx.bytes,dt.next_extent,0,1))asnr_illsized_exts
,dt.tablespace_name,dt.next_extentasdflt_ext_size
FROMdba_tablespacesdt,dba_extentsdx
WHEREdt.tablespace_name=dx.tablespace_name
ANDdx.owner='&OWNER'
GROUPBYsegment_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
SELECTtable_name
FROMall_tables
WHEREwner='&OWNER'
MINUS
SELECTtable_name
FROMall_constraints
WHEREwner='&&OWNER'
ANDconstraint_type='P';
4. disPK.sql
disPK.sql
（本文来源于图老师网站，更多请访问https://m.tulaoshi.com/bianchengyuyan/）To find out which primary keys are disabled
11/30/98
SELECTowner,constraint_name,table_name,status
FROMall_constraints
WHEREwner='&OWNER'ANDstatus='DISABLED’ANDconstraint_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
SELECTindex_name,table_name,uniqueness
FROMall_indexes
WHEREindex_namelike'&PKNAME%'
ANDwner='&OWNER'ANDuniqueness='NONUNIQUE'
SELECTc.constraint_name,i.tablespace_name,i.uniqueness
FROMall_constraintsc,all_indexesi
WHEREc.owner=UPPER('&OWNER')ANDi.uniqueness='NONUNIQUE'
ANDc.constraint_type='P'ANDi.index_name=c.constraint_name
6. mkrebuild_idx.sql
mkrebuild_idx.sql
Rebuild indexes to have correct storage parameters
11/2/98
SELECT'alterindex'||index_name||'rebuild'
,'tablespaceINDEXESstorage'
||'(initial256Knext256Kpctincrease0);'
FROMall_indexes
WHERE(tablespace_name!='INDEXES'
ORnext_extent!=(256*1024)
)
ANDwner='&OWNER'
/
7. datatype.sql
datatype.sql
To check datatype consistency between two environments
11/30/98
（本文来源于图老师网站，更多请访问https://m.tulaoshi.com/bianchengyuyan/） SELECTtable_name,column_name,data_type,data_length,data_precision,data_scale,nullable
FROMall_tab_columns--firstenvironment
WHEREwner='&OWNER'
MINUS
SELECTtable_name,column_name,data_type,data_length,data_precision,data_scale,nullable
FROMall_tab_columns@&my_db_link--secondenvironment
WHEREwner='&OWNER2'
orderbytable_name,column_name
8. obj_coord.sql
obj_coord.sql
To find out any difference in objects between two instances
12/08/98
SELECTobject_name,object_type
FROMuser_objects
MINUS
SELECTobject_name,object_type
FROMuser_objects@&my_db_link
