Blackboard - Analyze Schema

**Last Analyzed**

sqlplus "/as sysdba";

sqlplus> select OWNER,TABLE_NAME,last_analyzed from dba_tables where tablespace_name = 'BB_BB60_DATA';

sqlplus> select OWNER,TABLE_NAME,last_analyzed from dba_tables where tablespace_name = 'BBADMIN_DATA';

sqlplus> select OWNER,TABLE_NAME,last_analyzed from dba_tables where tablespace_name = 'BB_BB60_STATS_DATA';


**List Jobs**

SELECT job, schema_user, next_date, broken, what from dba_jobs;


**Remove Job**

execute sys.dbms_ijob.remove(n);

**Create New Analyzed_Schema Jobs**

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''BB_BB60_STATS'',

cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''SYS'',

cascade=>TRUE);',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'' BBADMIN'',

cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''BB_BB60'',

cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/


or simply run the following

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'BB_BB60_STATS', cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'BB_BB60', cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'BBADMIN', cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');