Create PowerShell script for Oracle DB automation

Question

I want to create Powershell script for oracle database automation having some following steps regarding automation would be mention:-

Drop the Schema in DB (Drop user username cascade)

Restore clean DB (impdp from dumpfiles)

Run configuration task (script)

Impdp statistics from another environment into DB

Restore/Apply stats from another environment (dbms_stats.import_schema_statistics)

Backup stats after step 4 (dbms_stats.gather_schema_statistics)

Please help me to create sample powershell script in which all the steps should be mentioned.

Dmitry Demin · Accepted Answer · 2018-12-04 05:35:52Z

1

Example script for drop user with tablespace tablespace_username including contents and datafiles usage ./drop_user.ps1 username

param(
[string]$name_user = ""
)

Write-Host "NAME_USER: $name_user"

##!!!!! ORA-01940: cannot drop a user that is currently connected
## USER must disconnect before drop user!!!

#Dropping Users
#Dropping users is very straightforward and is accomplished with the drop user command. The
#only parameters are the username to be dropped and the cascade option; any objects owned by
#the user must be explicitly dropped or moved to another schema if the cascade option is not used.

$username_dba = "system"
$password_dba = "manager"
$tnsalias_db = "test-ecdu"
$full_name_user=$name_user   
#
$user_tablespace = "tablespace_"
$default_tablespace_user = $user_tablespace+$name_user
#
Write-Host "USER default_tablespace: $default_tablespace_user"


#Make SQL DROP TABLESPACE
# The database also automatically drops from the operating system any Oracle-managed datafiles and tempfiles in the tablespace. 
#Other datafiles and tempfiles are not removed from the operating system unless you specify INCLUDING CONTENTS AND DATAFILES.
$sqlQuery_drop_tablespace = @"
set NewPage none
set heading off
set feedback off
WHENEVER SQLERROR continue
DROP TABLESPACE  $default_tablespace_user INCLUDING CONTENTS AND DATAFILES
;
"@

#Degug display "SQL Tablespace: $sqlQuery_drop_tablespace"
#Specify CASCADE to drop all objects in the user's schema before dropping the user. 
#You must specify this clause to drop a user whose schema contains any objects.
Write-Host "SQL Drop Tablespace: $sqlQuery_drop_tablespace"
#
$sqlQuery_drop_user = 
@"

DROP USER $full_name_user CASCADE 
;
exit
"@


$sqlQuery = $sqlQuery_drop_tablespace + $sqlQuery_drop_user

# Degug display  $sqlQuery = $sqlQuery_drop_tablespace + $sqlQuery_drop_user
Write-Host $sqlQuery   
$sqlOutput = $sqlQuery | sqlplus -silent $username_dba/$password_dba@$tnsalias_db

# Degug display result SQL
Write-Host  $sqlOutput

answered Dec 4, 2018 at 5:35

Dmitry Demin

2,1232 gold badges19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Md Wasi Over a year ago

Thanks for help me. But you are writing script for 1st step only not all. For all the steps what I have to write in the script. Please help me.

Dmitry Demin Over a year ago

Well, when I have enough free time, I will help you.

Md Wasi Over a year ago

Yeah sure. Thanks for help me, remaining process will follow as is it that mention in script. only i have to add SQL query.

Collectives™ on Stack Overflow

Create PowerShell script for Oracle DB automation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related