Shell Script–Applying a command or a SQL script to all databases in the same host

Hello everyone,

This is my first post on the oracle blogs section!

I really hope the posts I will publish here help some of you to have a more dynamic and comfortable work.

This time I'm publishing a simple KSH script that can allow you to apply a single command or a SQL script to all running databases in a single host.

Now let's explain each part:

• Functions
  • Utility Functions: refer to this post to check the utility functions I normally use.
    • You'll see debugPrint and msgPrint in most of my shell scripts. Those are used to give a nice format to the message I'm sending to the console/log.
  • Crawl
    • This is the main function that receives the command/sql file to be run in every instance.
• Main Algorythm
  • for DBNAME in $(ps -ef | grep pmon | cut -d"_" -s -f3 | grep -v ASM)
    • Here, we cycle through all the running instances in the host. Looking for pmon processes and getting only the INSTANCE name from it.
  • crawl "@${2}"
    • In the SQL script mode see how the argument passed to the crawl function is appended to the @ sign, so that we don't have to check for it when running inside the function.
      
  • crawl "${2}"
    • In the command mode we pass the argument inside double quotes so that it is considered a single argument by the function while running.

With some light modifications, we can get this same schema to work with any of the command line utilities from Oracle. But that's a future project of mine. I will try to add support for RMAN in the next few months as my work allows me to.

Hope that some of you find this post useful.

Shell script–Utility functions

Hello fellow DBAs, Oracle and Linux enthusiasts.

Today I'm going to share the script I use to "source" utility functions to the shell scripts I commonly use for my daily DBA tasks.

Let's add some explanation:

• continueQuestion
  • This function is a simple "Continue? (y/n)" question. As this is used a lot in interactive scripts, seemed like a good idea for me to add it to the utility functions script.
• debugPrint
  • For debugging, it adds the timestamp prior to the message. Allows you to keep track of what, where and when something is happening.
• msgPrint
  • Allows you to format in several ways the output that you want to send to console/log file.

I hope you find this useful and makes your job better!

Execute Immediate and Dynamic SQL

When working with PL/SQL you may find that you would like to iteratively execute a query with different parameters.  Well, got you good news.  You can!

There’s an instruction that will make those ideas come true.

EXECUTE IMMEDIATE is an Oracle implementation that allows the developer to prepare a statement and then execute it from inside the PL/SQL block, defining also input and/or output parameters.

More so, this instruction will generate some tracing info that can be very useful when trying to model the business logic in the pl/sql program.

In its most basic form, it’s used like this:

EXECUTE IMMEDIATE <sql_query>;

This <sql_query> can be as simple or as complex as we want.  It can even be another pl/sql block.  So, you can use a variable to store the statement, let’s say v_smnt and then execute it with:

EXECUTE IMMEDIATE

v_smnt;

Even more, you can use bind variables or parameters with the simple addition of the USING and RETURNING INTO keywords followed by the pl/sql environment variables you want to use.

All these features make this command very powerful.  It also keep some tracking info in memory that you can access through some special variables, like SQL%ROWCOUNT.

Now that you know what would you be looking for, you can visit the next few links for more info on this command and its uses.

Oracle Docs - Command Diagram and elements explanation

Oracle Docs - Dynamic SQL explanation and some examples

And, of course, you can make questions here and I’ll answer as soon as I can.

How to: I need an empty Database – Part 2

In the first part of his post I showed the way to use IMP/EXP utilities from Oracle to get the DDL of a single schema or an entire database.  Some people don’t fully trust these utilities or just dislike the command line and prefer to work over procedures.  This post will give you another option to get a clean database using the information of the data dictionary on any DBMS or the user_<whatever> tables or the all_<whatever> tables from Oracle.
So, here we go.

Oracle

In particular, for the Oracle DBMS, I’ll use a package.  Let’s call it SCHEMA_CLEANER.  Now, we have to state what do we need?  For any database (that respects the relations between tables and data within those tables), the problems when you have to delete some data begin with the foreign keys being violated.
This indicates that the first thing to do is to DISABLE all those constraints so that we can delete all data in the schema without having to take core of the order we’re deleting that data.  But of course, we have later to take the time to enable all those constraints again, because we want to work with a consistent schema, and leaving the constraints disabled will lead almost for sure to inconsistent data.
Following, this we got 2 specifications for our package: DISABLE_FKS and ENABLE_FKS.
Finally, we need the procedure that will do the bulk delete.  So that’s one more specification for the package, let’s say: DELETE_DATA.
For his example I’ll be running the package procedures from the schema we want to clean.  Nevertheless, this code can be easily modified to be run as a DBA from the SYSTEM schema passing the schema to clean up as a parameter.
In this code, then I’ll use the user_<whatever> tables to get the info I need.  The mod would be to use the all_<whatever> tables and filter through the OWNER field in each table.
So, here is the code:

CREATE OR REPLACE PACKAGE SCHEMA_CLEANER AS

PROCEDURE DISABLE_FKS;
PROCEDURE ENABLE_FKS;
PROCEDURE DELETE_DATA;
PROCEDURE CLEAN_SCHEMA;

END SCHEMA_CLEANER;
/


CREATE OR REPLACE PACKAGE BODY SCHEMA_CLEANER AS

PROCEDURE DISABLE_FKS AS
ctable user_tables.table_name%TYPE;
fkey user_constraints.constraint_name%TYPE;

CURSOR tableList IS
SELECT table_name
FROM user_tables;

CURSOR FKList IS
SELECT constraint_name
FROM user_constraints
WHERE table_name = ctable
AND constraint_name LIKE '%FK%';

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('About to disable foreign keys...');
OPEN tableList;

FETCH tableList INTO ctable;

WHILE tableList%FOUND
LOOP
dbms_output.put_line('Table found: ' || ctable);
OPEN FKList;
FETCH FKList INTO fkey;
WHILE FKList%FOUND
LOOP
EXECUTE IMMEDIATE ('ALTER TABLE ' || ctable || ' DISABLE CONSTRAINT ' || fkey);
dbms_output.put_line('Foreign Key ' || fkey || ' disabled.');
FETCH FKList INTO fkey;
END LOOP;
CLOSE FKList;
FETCH tableList INTO ctable;
END LOOP;    
CLOSE tableList;
dbms_output.put_line('Foreign keys disabled.');
END DISABLE_FKS;

PROCEDURE ENABLE_FKS AS
ctable user_tables.table_name%TYPE;
fkey user_constraints.constraint_name%TYPE;

CURSOR tableList IS
SELECT table_name
FROM user_tables;

CURSOR FKList IS
SELECT constraint_name
FROM user_constraints
WHERE table_name = ctable
AND constraint_name LIKE '%FK%';

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('About to enable foreign keys...');
OPEN tableList;

FETCH tableList INTO ctable;

WHILE tableList%FOUND
LOOP
dbms_output.put_line('Table found: ' || ctable);
OPEN FKList;
FETCH FKList INTO fkey;
WHILE FKList%FOUND
LOOP
dbms_output.put_line('Foreign Key found: ' || fkey);
EXECUTE IMMEDIATE ('ALTER TABLE ' || ctable || ' ENABLE CONSTRAINT ' || fkey);
dbms_output.put_line('Foreign Key ' || fkey || ' enabled.');
FETCH FKList INTO fkey;
END LOOP;
CLOSE FKList;
FETCH tableList INTO ctable;
END LOOP;    
CLOSE tableList;
dbms_output.put_line('Foreign keys enabled.');
END ENABLE_FKS;

PROCEDURE DELETE_DATA AS
thetable user_tables.table_name%TYPE;

CURSOR ctable IS
SELECT table_name
FROM user_tables;

BEGIN
dbms_output.enable(1000000);
dbms_output.put_line('Starting data deletion process...');
OPEN ctable;
FETCH ctable INTO thetable;
dbms_output.put_line('Deleting, please wait...');
WHILE ctable%FOUND LOOP
dbms_output.put_line('Table found: '|| thetable);
dbms_output.put_line('Deleting data in '|| thetable);
EXECUTE IMMEDIATE ('DELETE FROM ' || thetable);
dbms_output.put_line(thetable || ' is empty.');
FETCH ctable INTO thetable;
END LOOP;
CLOSE ctable;
dbms_output.put_line('Data deletion process finished.');
END DELETE_DATA;

PROCEDURE CLEAN_SCHEMA AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SCHEMA_CLEANER.DISABLE_FKS;
SCHEMA_CLEANER.DROP_DYNAMIC_TABLES;
SCHEMA_CLEANER.DELETE_DATA;
SCHEMA_CLEANER.ENABLE_FKS;
END CLEAN_DB;

END SCHEMA_CLEANER;

/

How to: I need an empty Database! – Part 1

In your daily work, for various reasons you may need an empty database.  May be for testing purposes, for testing bulk data load or to identify changes over tables caused by an application that is working over the database.

Whichever the case may be, you need a clean database and not always got the time (or the option) to start following relational constraints backwards.

Here’s an option to get an empty database out from a fully working online one.

Import / Export  Utilities (Oracle only)

For the task at hand, using the IMP/EXP utilities can be quite helpful and fast. Both of them provide the option to import/export the full database, only a schema and with or without data.

If you don’t have any .DMP file or just want to create a new one, you can use the following command to export the database DDL.

EXP SYSTEM/mypswd FILE=fulldb.dmp FULL=Y ROWS=N

Or as well you can make a parameters file containing this information

FILE=fulldb.dmp
FULL=Y
ROWS=N
LOG=export.log

and then use it as a parameter itself as follows

EXP SYSTEM/mypswd PARFILE=myparams.dat

Now, if you do have a DMP generated, let’s say, by a backup policy, then you can just use the IMP utility to import the DDL mostly the same way as before with just some little changes.

Let’s state that when someone says “I need an empty database” it really means (usually) “I need an empty schema”

So, you must specify (to avoid loosing data dictionary and other important data) the parameters FROMUSER and TOUSER.  This will tell the utility which schema we want to import, as follows

IMP SYSTEM/mypswd FILE=fulldb.dmp FROMUSER=my_schema TOUSER=another_schema

In the next part, I’ll post some procedures that can be used to clean any schema of the database.  For Oracle, they can be packaged, for other DBMS you just can run the stored procedures.

As always, hope this helps…  And comments are welcome!