Dani's IT Blog

…moving from Oracle to Software Development

PL/SQL Basics for the DBA. Part I (… or how to start having fun working as a DBA)

leave a comment »

This is my first blog post. I will write a series about “PL/SQL Basics for the DBA” and about other topics related to Oracle. A part of my posts will be in English and German, for instance i will publish the complete series “PL/SQL Basics for the DBA” in German as well. So if your mother tongue is German you should look out for the German version.

[German] Wie schon auf Englisch gesagt, werde ich einige meiner Artikel auch auf Deutsch veröffentlichen. Wer Deutsch als Muttersprache hat, dem empfehle ich, falls vorhanden die deutsche Version eines Artikels zu lesen. Die ganze Serie “PL/SQL Basics für den DBA” werde ich auch auf Deutsch veröffentlichen. [/German]

As a DBA you often need to do jobs like:

  • grant read access on all objects of a schema to a different database user
  • drop all tables of a schema
  • move all indexes of a schema from one tablespace to another

Often I see DBA’s trying to do this job using a graphical utility like Toad or SqlDeveloper and a text editor. But it can be done a lot easier by using the features which SQL and Oracle provide.

We can not only select information which is stored in database tables, we can also select fixed strings.

SQL> SELECT 'hello world!' FROM dual;

hello world!

We can combine fix strings and data from a table to generate any desired statement.

SQL> SELECT 'The signs O and '|| dummy ||' are needed to play TicTacToe' FROM dual;

The signs O and X are needed to play TicTacToe

How does this help us to do our job? Let’s go back to the need to “drop all tables of a schema”. The information about all tables of a schema can be found in the USER_TABLES view (and also in the views ALL_TABLES and DBA_TABLES).


SQL> SELECT ‘DROP TABLE “‘|| table_name ||'”;’ FROM user_tables;

2 rows selected.

To test my code in your TEST database you can use the following statements to create your own test schema.

CREATE TABLE t1 (x number);
CREATE TABLE t2 (x number);
SELECT table_name FROM user_tables;

Now we can simply copy&paste and execute the DDL commands. If copy&paste is no option, or not wanted, we can write the generated commands to a file and execute this file afterwards. I used prompt to write the SPOOL commands to the script, to log the changes made by the script. For the following example, create a script “create_drop_tables.sql” and execute it. If you directly execute the statements out of sqlplus, you will have unwanted entry’s in the generated file.


set heading off
set feedback off
spool drop_user_tables.sql
prompt spool drop_tables.log
SELECT 'DROP TABLE "'|| table_name ||'";' FROM user_tables;
prompt spool off
spool off

Written by danirey

June 6, 2009 at 12:52

Posted in English, Oracle, PL/SQL Basics

Tagged with , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: