prompt *******************************************************
prompt ***   S E T U P   T E S T   E N V I R O N M E N T   ***
prompt *******************************************************
DROP USER statsuser CASCADE;
CREATE USER statsuser IDENTIFIED BY statsuser DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp ACCOUNT UNLOCK;

GRANT CONNECT TO statsuser;
GRANT CREATE TABLE TO statsuser;

ALTER USER statsuser QUOTA UNLIMITED ON users;
CONN statsuser/statsuser


CREATE TABLE t1 (
  id NUMBER,
  description VARCHAR2(1000),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);


INSERT /*+append*/ INTO t1
SELECT rownum, RPAD('s', 1000, 's')
FROM dual
CONNECT BY level <= 10000;
COMMIT;


prompt
prompt
prompt *********************************************************
prompt ***   S H O W   C U R R E N T   S T A T I S T I C S   ***
prompt *********************************************************
SELECT
   num_rows,
   blocks,
   avg_row_len,
   sample_size,
   TO_CHAR(last_analyzed, 'YYYY-MM-DD_HH24:MI:SS') "LAST ANALYZED"
  FROM ALL_TAB_STATISTICS
 WHERE owner = 'STATSUSER'
   AND table_name = 'T1';


prompt
prompt
prompt *********************************************
prompt ***   G A T H E R   S T A T I S T I C S   ***
prompt *********************************************
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 't1');


prompt
prompt
prompt ***************************************
prompt ***   T R U N C A T E   T A B L E   ***
prompt ***************************************
TRUNCATE TABLE t1;


prompt
prompt
prompt *********************************************************
prompt ***   S H O W   C U R R E N T   S T A T I S T I C S   ***
prompt *********************************************************
SELECT
   num_rows,
   blocks,
   avg_row_len,
   sample_size,
   TO_CHAR(last_analyzed, 'YYYY-MM-DD_HH24:MI:SS') "LAST ANALYZED"
  FROM ALL_TAB_STATISTICS
 WHERE owner = 'STATSUSER'
   AND table_name = 'T1';


prompt
prompt
prompt *********************************************
prompt ***   G A T H E R   S T A T I S T I C S   ***
prompt *********************************************
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 't1');


prompt
prompt
prompt *********************************************************
prompt ***   S H O W   C U R R E N T   S T A T I S T I C S   ***
prompt *********************************************************
SELECT
   num_rows,
   blocks,
   avg_row_len,
   sample_size,
   TO_CHAR(last_analyzed, 'YYYY-MM-DD_HH24:MI:SS') "LAST ANALYZED"
  FROM ALL_TAB_STATISTICS
 WHERE owner = 'STATSUSER'
   AND table_name = 'T1';


prompt
prompt
prompt *************************************************
prompt ***   R E C R E A T E   T E S T   T A B L E   ***
prompt *************************************************
DROP TABLE t1;
CREATE TABLE t1 (
  id NUMBER,
  description VARCHAR2(1000),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);


INSERT /*+append*/ INTO t1
SELECT rownum, RPAD('s', 1000, 's')
FROM dual
CONNECT BY level <= 10000;
COMMIT;


prompt
prompt
prompt *********************************************************
prompt ***   S H O W   C U R R E N T   S T A T I S T I C S   ***
prompt *********************************************************
SELECT
   num_rows,
   blocks,
   avg_row_len,
   sample_size,
   TO_CHAR(last_analyzed, 'YYYY-MM-DD_HH24:MI:SS') "LAST ANALYZED"
  FROM ALL_TAB_STATISTICS
 WHERE owner = 'STATSUSER'
   AND table_name = 'T1';


prompt
prompt
prompt *********************************************
prompt ***   D E L E T E   S T A T I S T I C S   ***
prompt *********************************************
EXEC DBMS_STATS.DELETE_TABLE_STATS(USER, 't1');


prompt
prompt
prompt *********************************************************
prompt ***   S H O W   C U R R E N T   S T A T I S T I C S   ***
prompt *********************************************************
SELECT
   num_rows,
   blocks,
   avg_row_len,
   sample_size,
   TO_CHAR(last_analyzed, 'YYYY-MM-DD_HH24:MI:SS') "LAST ANALYZED"
  FROM ALL_TAB_STATISTICS
 WHERE owner = 'STATSUSER'
   AND table_name = 'T1';


prompt
prompt
prompt ***********************************************************
prompt ***   C L E A N U P   T E S T   E N V I R O N M E N T   ***
prompt ***********************************************************
CONNECT / AS SYSDBA
DROP USER statsuser CASCADE;