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