View source for Auto Reset Oracle Database to Actual

Create a script to reset a demo database;

<pre>
vim /home/oracle/resetdemodatabase.sh
</pre>

Write following code in resetdemodatabase.sh file;

<pre>
sqlplus system/password@smarthcmdemo  @drop_user.sql
impdp system/password@smarthcmdemo remap_schema=SMARTHCM_DEMO:SMARTHCM_DEMO remap_tablespace=SMARTHCM:SMARTHCM directory=data_demo
dumpfile=smarthcmdemo.dmp logfile=smarthcmdemo.log Transform=oid:n
sqlplus system/password@smarthcmdemo  @recompile_objects.sql
</pre>

Create a script to drop user;

<pre>
vim /home/oracle/drop_user.sql
</pre>

Write following code in drop_user.sql file;

<pre>
SET SERVEROUTPUT ON
DECLARE
V_CHCK_USER NUMBER;
BEGIN

  FOR CUR IN (SELECT '''' || S.SID || ', ' || S.SERIAL# || '''' SNO
                FROM V$SESSION S
               WHERE S.USERNAME = 'SMARTHCM_DEMO') LOOP
    DBMS_OUTPUT.PUT_LINE('Disconnecting all users from SmartHCM_DEMO user');
    EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ' || CUR.SNO ||
                      ' IMMEDIATE';

  END LOOP;

SELECT COUNT(1) INTO V_CHCK_USER
FROM dba_users WHERE username like 'SMARTHCM_DEMO';

IF V_CHCK_USER = 1 THEN
  DBMS_OUTPUT.PUT_LINE('Droping the SmartHCM_DEMO please wait for a while.....');
  EXECUTE IMMEDIATE 'DROP USER SMARTHCM_DEMO CASCADE';
END IF;

END;
/
exit
</pre>

Create a script to compile invalid objects;

<pre>
vim /home/oracle/recompile_objects.sql
</pre>

Write following code in recompile_objects.sql file;

<pre>
SET SERVEROUTPUT ON
BEGIN dbms_output.put_line('Compiling invalid objects. Please wait...'); END;
EXEC DBMS_UTILITY.compile_schema(schema => 'SMARTHCM_DEMO');
exit
</pre>

Create a cron job;

<pre>
crontab -e
</pre>

Insert following schedule in cron job;

<pre>
0 3 * * * /home/oracle/resetdemodatabase.sh
</pre>

Understanding Cron Job scheduler;

<pre>
Crontab Format:
Minute | Hour | Day | Month | DayOfWeek | user | ThingToDo

'user' is the account name for the 'ThingToDo' to be run under. We are not using 'user', it will run by default user.
'ThingToDo' is as per any command line syntax. Typically, it invokes a script.
'Day' and 'Date' are easy to understand, but very difficult to read in their raw form
</pre>

Thus, our cron job will execute the script oraclebackup.sh on 03:00 hours daily.