SET ECHO ON TERM ON;
REM
REM $Header: 215187.1 sqdusr.sql 11.2.9.8 2009/08/31 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqdusr.sql
REM
REM DESCRIPTION
REM   This script drops the SQLTXPLAIN user
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected INTERNAL (SYS) as
REM      SYSDBA
REM
REM PARAMETERS
REM   1. None
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
REM   3. Execute script sqdusr.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus /nolog
REM   SQL> connect / as sysdba
REM   SQL> start sqdusr.sql
REM
REM NOTES
REM   1. This script is executed automatically by sqdrop.sql
REM
SET ECHO OFF TERM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
BEGIN
  IF USER != 'SYS' THEN
    RAISE_APPLICATION_ERROR(-20100, 'Drop script failed - sqdusr.sql should be executed connected as SYS, not as '||USER);
  END IF;
END;
/
WHENEVER SQLERROR CONTINUE;
SET ECHO ON;

PRO
PRO  Drop SQLT$ directories
PRO

DROP DIRECTORY SQLT$UDUMP;
DROP DIRECTORY SQLT$STAGE;

PRO
PRO  Drop user SQLTXPLAIN
PRO

COL prior_default_tablespace NOPRINT NEW_VALUE prior_default_tablespace FORMAT A1000;
COL prior_temporary_tablespace NOPRINT NEW_VALUE prior_temporary_tablespace FORMAT A1000;
SELECT 'UNKNOWN' prior_default_tablespace,
       'UNKNOWN' prior_temporary_tablespace
  FROM dual;
SELECT default_tablespace prior_default_tablespace,
       temporary_tablespace prior_temporary_tablespace
  FROM dba_users
 WHERE username = 'SQLTXPLAIN';

PRO
PRO PRIOR_DEFAULT_TABLESPACE: &&prior_default_tablespace
PRO PRIOR_TEMPORARY_TABLESPACE: &&prior_temporary_tablespace
PRO

REM This CREATE USER command may fail and that is OK.
COL random_number NOPRINT NEW_VALUE random_number FORMAT A10;
SELECT TO_CHAR(NVL(ABS(MOD(TRUNC(SYS.DBMS_RANDOM.RANDOM), 100000)), 0)) random_number FROM DUAL;
CREATE USER sqltxplain IDENTIFIED BY "Dummy_Password:&&random_number";

SET TERM ON;

WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM If this DROP USER command fails that means a session is connected with this user.
DROP USER sqltxplain CASCADE;
WHENEVER SQLERROR CONTINUE;

SET ECHO OFF;
PRO SQDUSR completed.
