analyze_compsql
analyze_compsql
BEGIN
SYSDBMS_UTILITYANALYZE_SCHEMA(&OWNERCOMPUTE);
END;
/
pop_volsql
pop_volsql
insertintoutl_vol_facts
selecttable_name
NVL(num_rows)asnum_rows
trunc(last_analyzed)asmeas_dt
fromall_tables
or just user_tables
whereownerin(&OWNER)
or a commaseparated list of owners
/
commit
/
C每周处理程序
nextextsql
nextextsql
To find tables that dont 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 tablespaces
default value for NEXT
his tells us what the setting for NEXT is for these objects today
//
SELECTsegment_namesegment_typedsnext_extentasActual_Next
dttablespace_namedtnext_extentasDefault_Next
FROMdba_tablespacesdtdba_segmentsds
WHEREdttablespace_name=dstablespace_name
ANDdtnext_extent!=dsnext_extent
ANDdsowner=UPPER(&OWNER)
ORDERBYtablespace_namesegment_typesegment_name;
existextsql
existextsql
To check existing extents
This tells us how many of each objects extents differ in size from
the tablespaces 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
//
SELECTsegment_namesegment_type
count(*)asnr_exts
sum(DECODE(dxbytesdtnext_extent))asnr_illsized_exts
dttablespace_namedtnext_extentasdflt_ext_size
FROMdba_tablespacesdtdba_extentsdx
WHEREdttablespace_name=dxtablespace_name
ANDdxowner=&OWNER
GROUPBYsegment_namesegment_typedttablespace_namedtnext_extent;
No_pksql
no_pksql
To find tables without PK constraint
//
SELECTtable_name
FROMall_tables
WHEREwner=&OWNER
MINUS
SELECTtable_name
FROMall_constraints
WHEREwner=&&OWNER
ANDconstraint_type=P;
disPKsql
disPKsql
To find out which primary keys are disabled
//
SELECTownerconstraint_nametable_namestatus
FROMall_constraints
WHEREwner=&OWNERANDstatus=DISABLEDANDconstraint_type=P;
nonuPKsql
nonuPKsql
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
/
/
SELECTindex_nametable_nameuniqueness
FROMall_indexes
WHEREindex_namelike&PKNAME%
ANDwner=&OWNERANDuniqueness=NONUNIQUE
SELECTnstraint_nameitablespace_nameiuniqueness
FROMall_constraintscall_indexesi
WHEREcowner=UPPER(&OWNER)ANDiuniqueness=NONUNIQUE
ANDnstraint_type=PANDiindex_name=nstraint_name
mkrebuild_idxsql
mkrebuild_idxsql
Rebuild indexes to have correct storage parameters
//
SELECTalterindex||index_name||rebuild
tablespaceINDEXESstorage
||(initialKnextKpctincrease);
FROMall_indexes
WHERE(tablespace_name!=INDEXES
ORnext_extent!=(*)
)
ANDwner=&OWNER
/
datatypesql
datatypesql
To check datatype consistency between two environments
//
SELECTtable_namecolumn_namedata_typedata_lengthdata_precisiondata_scalenullable
FROMall_tab_columnsfirstenvironment
WHEREwner=&OWNER
MINUS
SELECTtable_namecolumn_namedata_typedata_lengthdata_precisiondata_scalenullable
FROMall_tab_columns@&my_db_linksecondenvironment
WHEREwner=&OWNER
orderbytable_namecolumn_name
obj_coordsql
obj_coordsql
To find out any difference in objects between two instances
//
SELECTobject_nameobject_type
FROMuser_objects
MINUS
SELECTobject_nameobject_type
FROMuser_objects@&my_db_link
