Print Friendly, PDF & Email

NEEDS
How can I remove all objects that were created in the ALTERNATIVE TO V$ACTIVE_SESSION_HISTORY VIEW IN STANDARD EDITION and/or the DUMPING SQL EXECUTION HISTORY INFORMATION SNAPSHOTS IN STANDARD EDITION

RELATED
ALTERNATIVE TO V$ACTIVE_SESSION_HISTORY VIEW IN STANDARD EDITION
DUMPING SQL EXECUTION HISTORY INFORMATION SNAPSHOTS IN STANDARD EDITION

SOLUTION

USER

Connect as a privileged user, For instance as the SYS

SQL> SHOW USER
USER is "SESSHIS"
SQL> CONNECT / AS SYSDBA
Connected.

 

OBJECTS

Find all objects that belonged to the SESSIONS user

SQL> SHO USER
USER is "SYS"


SET PAGES 999
SET LINES 300 
COL OWNER FOR A10 
COL OBJECT_NAME FOR A30 
COL OBJECT_TYPE FOR A15
COL STATUS FOR A10 
COL CREATED FOR A20 

SELECT OWNER,
       OBJECT_NAME,
       OBJECT_TYPE,
       STATUS, 
       CREATED
FROM
       DBA_OBJECTS
WHERE
       OWNER='SESSHIS'
ORDER BY OBJECT_TYPE, OBJECT_NAME;
OWNER      OBJECT_NAME                    OBJECT_TYPE     STATUS     CREATED
---------- ------------------------------ --------------- ---------- --------------------
SESSHIS    SESSIONS_HISTORY_IDX_001       INDEX           VALID      06-APR-21
SESSHIS    SQL_HISTORY_IDX_001            INDEX           VALID      06-APR-21
SESSHIS    PURGING_SESSIONS_STATISTICS    JOB             VALID      06-APR-21
SESSHIS    PURGING_SQL_STATISTICS         JOB             VALID      06-APR-21
SESSHIS    STORING_SESSIONS_STATISTICS    JOB             VALID      06-APR-21
SESSHIS    STORING_SQL_STATISTICS         JOB             VALID      06-APR-21
SESSHIS    GATHER_SESSIONS_STATISTICS     PROCEDURE       VALID      06-APR-21
SESSHIS    GATHER_SQL_STATISTICS          PROCEDURE       VALID      06-APR-21
SESSHIS    PURGE_SESSIONS_STATISTICS      PROCEDURE       VALID      06-APR-21
SESSHIS    PURGE_SQL_STATISTICS           PROCEDURE       VALID      06-APR-21
SESSHIS    SESSIONS_HISTORY               TABLE           VALID      06-APR-21
SESSHIS    SQL_HISTORY                    TABLE           VALID      06-APR-21

12 rows selected.

 
If objects were created accidentally by another user

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED
FROM  DBA_OBJECTS WHERE OBJECT_NAME IN (
'PURGING_SESSIONS_STATISTICS','PURGING_SQL_STATISTICS',
'STORING_SESSIONS_STATISTICS', 'STORING_SQL_STATISTICS',
'GATHER_SESSIONS_STATISTICS', 'GATHER_SQL_STATISTICS',
'PURGE_SESSIONS_STATISTICS','PURGE_SQL_STATISTICS',
'SESSIONS_HISTORY','SQL_HISTORY',
'SESSIONS_HISTORY_IDX_001','SQL_HISTORY_IDX_001')
ORDER BY OBJECT_TYPE, OBJECT_NAME;

 

STOP JOBS

Find out any SESSHIS jobs that are currently executing

SET LINES 300 
SET PAGES 999 
COL ELAPSED_TIME for A20 
COL CPU_USED for A20 
COL JOB_NAME FOR A30 
SELECT SESSION_ID,JOB_NAME,ELAPSED_TIME,CPU_USED FROM DBA_SCHEDULER_RUNNING_JOBS WHERE OWNER='SESSHIS';
SESSION_ID JOB_NAME                       ELAPSED_TIME         CPU_USED
---------- ------------------------------ -------------------- --------------------
       108 STORING_SESSIONS_STATISTICS    +000 00:07:31.95     +000 00:00:00.78

 
Stop any running jobs

COL KILL_SESSHIS_RUNNING_JOBS FOR A60
SET LINES 60
COL KILL_SESSHIS_RUNNING_JOBS FOR A60
SELECT 'ALTER SYSTEM KILL SESSION ' || Q'[']' || SID || ',' || SERIAL# ||  Q'[' IMMEDIATE;]' KILL_SESSHIS_RUNNING_JOBS 
FROM 
       V$SESSION 
WHERE
       SID IN (
         SELECT SESSION_ID 
		 FROM   DBA_SCHEDULER_RUNNING_JOBS 
		 WHERE  OWNER='SESSHIS'
       );
KILL_SESSHIS_RUNNING_JOBS
------------------------------------------------------------
ALTER SYSTEM KILL SESSION '108,58765' IMMEDIATE;

 

DISCONNECT SESSSION

Disconnect any active SESSHIS sessions

COL DISCONNECT_SESSHIS_SESSIONS FOR A60

SELECT 'ALTER SYSTEM KILL SESSION ' || Q'[']' || SID || ',' || SERIAL# ||  Q'[' IMMEDIATE;]' DISCONNECT_SESSHIS_SESSIONS
FROM V$SESSION WHERE USERNAME='SESSHIS';
DISCONNECT_SESSHIS_SESSIONS
------------------------------------------------------------
ALTER SYSTEM KILL SESSION '108,58767' IMMEDIATE;

 

DROP

Drop the SESSHIS user

SQL> DROP USER SESSHIS CASCADE;

User dropped.

 

TABLESPACE

Before dropping the SESSHIS tablespace ensure no one else uses it

SQL> SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SESSHIS';

no rows selected

 
Dropping the SESSHIS tablespace

-- Query datafile(s) belonging to the SESSHIS tablespace

COL FILE_NAME FOR A60 
SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SESSHIS';

FILE_NAME
------------------------------------------------------------
/d00/oracle/oradata/meridb2/sesshis_001.dbf

-- File exist 

SQL> !ls /d00/oracle/oradata/meridb2/sesshis_001.dbf
/d00/oracle/oradata/meridb2/sesshis_001.dbf

-- SQL> DROP TABLESPACE SESSHIS INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

-- Verify that file(s) does not exist anymore

SQL>  !ls -l /d00/oracle/oradata/meridb2/sesshis_001.dbf
ls: cannot access /d00/oracle/oradata/meridb2/sesshis_001.dbf: No such file or directory

 

VERIFY

Ensure that you did no error and everything were dropped.

SQL> SHO USER
USER is "SYS"


SET PAGES 999
SET LINES 300 
COL OWNER FOR A10 
COL OBJECT_NAME FOR A30 
COL OBJECT_TYPE FOR A15
COL STATUS FOR A10 
COL CREATED FOR A20 

SELECT OWNER,
       OBJECT_NAME,
       OBJECT_TYPE,
       STATUS, 
       CREATED
FROM
       DBA_OBJECTS
WHERE
       OWNER='SESSHIS'
ORDER BY OBJECT_TYPE, OBJECT_NAME;

no rows selected

 
That’s it. All SESSHIS objects have been removed from the database.

 
 

Version  : 14:45 06-Apr-2021
Database : 12c+