Script to gather statistics for all tables in a schema and schema index rebuild

Script to gather schema table statistics 

Get the last time the tabbles were analyzed in HR schema as below.

Do not put the table list if you want to check for all tables. 

SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM ALL_TABLES WHERE OWNER = 'HR' 
and TABLE_NAME IN ('COUNTRIES','REGIONS','LOCATIONS','DEPARTMENTS','JOBS','EMPLOYEES');

OWNER	TABLE_NAME	LAST_ANALYZED
HR	DEPARTMENTS	13-SEP-2024 11:30:01
HR	EMPLOYEES	13-SEP-2024 11:30:01
HR	JOBS	13-SEP-2024 11:30:01
HR	LOCATIONS	13-SEP-2024 11:30:01
HR	REGIONS	13-SEP-2024 11:30:01
HR	COUNTRIES	13-SEP-2024 11:30:01

The data shows the last timestamp the statistics were gathered on these tables was on 13-Sep-24 at 11:30 AM. We now run a block to gather statistics for all tables in HR schema as below

DECLARE
  CURSOR C1
  IS 
  SELECT TABLE_NAME
  FROM ALL_TABLES
  WHERE OWNER = 'HR';
  
  v_sql VARCHAR2(1000);
BEGIN
  FOR rec IN C1
  LOOP --{
        BEGIN
--    dbms_output.put_line('table_name  ' || rec.TABLE_NAME);
        v_sql := 'ANALYZE TABLE '|| rec.table_name || ' COMPUTE STATISTICS';
        EXECUTE IMMEDIATE v_sql;
     EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Gather statistics error for table ' || rec.table_name || ' ORA ERROR ' || SQLERRM);
      END;
  END LOOP;  
END;

After running the script the last_analyzed column shows a time of 18-Sep-24.

SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM ALL_TABLES WHERE OWNER = 'HR' 
and TABLE_NAME IN ('COUNTRIES','REGIONS','LOCATIONS','DEPARTMENTS','JOBS','EMPLOYEES');

OWNER	TABLE_NAME	LAST_ANALYZED
HR	DEPARTMENTS	18-SEP-2024 11:13:00
HR	EMPLOYEES	18-SEP-2024 11:13:00
HR	JOBS	18-SEP-2024 11:13:00
HR	LOCATIONS	18-SEP-2024 11:13:00
HR	REGIONS	18-SEP-2024 11:13:00
HR	COUNTRIES	18-SEP-2024 11:13:00

Script to rebuild all indexes in a schema

Get the last time the indexes were rebuilt in HR schema as below.

Do not put the table list if you want to check for all tables.

SELECT TABLE_NAME, TABLE_OWNER,INDEX_NAME,STATUS, LAST_ANALYZED, VISIBILITY, CONSTRAINT_INDEX 
from user_indexes where table_name in ('EMPLOYEES','DEPARTMENTS');

TABLE_NAME	TABLE_OWNER	INDEX_NAME	STATUS	LAST_ANALYZED	VISIBILITY	CONSTRAINT_INDEX
DEPARTMENTS	HR	DEPT_ID_PK	VALID	18-SEP-2024 11:13:00	VISIBLE	NO
EMPLOYEES	HR	EMP_EMAIL_UK	VALID	18-SEP-2024 11:13:00	VISIBLE	YES
EMPLOYEES	HR	EMP_EMP_ID_PK	VALID	18-SEP-2024 11:13:00	VISIBLE	NO

DECLARE
  CURSOR C1
  IS 
  SELECT INDEX_NAME FROM ALL_INDEXES WHERE OWNER = 'HR';
  
  v_sql VARCHAR2(1000);
BEGIN
  FOR rec IN C1
  LOOP --{
    BEGIN
--    dbms_output.put_line('index_name  ' || rec.index_name);
     v_sql := 'ALTER INDEX  '|| rec.index_name || ' REBUILD';
      EXECUTE IMMEDIATE v_sql;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Rebuild error for index ' || rec.index_name || ' ORA ERROR ' || SQLERRM);
      END;  
  END LOOP;  
END;

After running the script the last rebuild time of shows a time of 18-Sep-24 11:30:15.

SELECT TABLE_NAME, TABLE_OWNER,INDEX_NAME,STATUS, LAST_ANALYZED, VISIBILITY, CONSTRAINT_INDEX 
from user_indexes where table_name in ('EMPLOYEES','DEPARTMENTS');

TABLE_NAME	TABLE_OWNER	INDEX_NAME	STATUS	LAST_ANALYZED	VISIBILITY	CONSTRAINT_INDEX
DEPARTMENTS	HR	DEPT_ID_PK	VALID	18-SEP-2024 11:30:15	VISIBLE	NO
EMPLOYEES	HR	EMP_EMAIL_UK	VALID	18-SEP-2024 11:30:15	VISIBLE	YES
EMPLOYEES	HR	EMP_EMP_ID_PK	VALID	18-SEP-2024 11:30:15	VISIBLE	NO

Thought for the day

Commit to the LORD whatever you do, and he will establish your plans.
Proverbs 16:3