Skip to Main Content
  • Questions
  • I want to automate this using Ansible

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Lokesh.

Asked: September 20, 2021 - 5:34 am UTC

Last updated: September 23, 2021 - 6:26 am UTC

Version: 10

Viewed 1000+ times

You Asked

Fix Text
Remove any demonstration and sample databases, database applications, objects, and files from the DBMS.

To remove an account and all objects owned by that account (using BI as an example):
DROP USER BI CASCADE;

To remove objects without removing their owner, use the appropriate DROP statement (DROP TABLE, DROP VIEW, etc.).

This is check:

make sure all example schemas are removed
select distinct(username) from dba_users where username in
('BI','HR','OE','PM','IX','SH','SCOTT');

Expected result:

Expected results: no rows returned. If rows are returned, then drop the users.
sqlplus / as sysdba
drop user bi cascade;
drop user hr cascade;
drop user oe cascade;
drop user pm cascade;
drop user is cascade;
drop user sh cascade;
drop user scott cascade;

and Connor said...

How about creating a file that drops if necessary and does not return errors, eg

conn / as sysdba
declare
  procedure du(p_user varchar2) is
  begin
    execute immediate 'drop user '||p_user||' cascade';
  exception
    when others then
      if sqlcode != -01918 then raise; end if;
  end;
hegin
  du('bi');
  du('hr');
  du('oe');
  du('pm');
  du('is');
  du('sh');
  du('scott');
end;
/


Then your ansible is going to be something along the lines of

- hosts: all
 vars:
 oracle_base: /u01/app/oracle
 oracle_home: /u01/app/oracle/product/19.10/dbhome_1
 target_dir: /tmp
 remote_user: oracle
 sudo_user: root
 sudo: false
 tasks: 

- name: copy sqlscript
 copy: src=files/{{ item }} dest={{ target_dir }} owner=oracle group=oinstall mode=0644
 with_items:
 - drop_sample_schemas.sql
 

- name: copy sqlscript
 action: shell unzip -oq {{ patch_dir }}/p6880880_112000_Linux-x86-64.zip -d {{ oracle_home }}  

- name: run sqlscript
 action: shell export ORACLE_HOME={{ oracle_home }}; cd {{ target_dir }}; $ORACLE_HOME/bin/sqlplus /nolog @drop_sample_schemas
 register: error_found
 failed_when: "'ORA-" passed.' in error_found.stdout"



(I stress - I'm no ansible expert)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database