Including Datafiles

Here is a nice little feature that works in Oracle 10g.

drop tablespace including contents and datafiles.

Oh I love that; all is cleaned up in one easy statement.

Exporting Made A Little Easier

Even though exporting a several tables in a series may not necessarily be a daunting task, it sure can be a tedious one. Therefore the creation of the following script.

A buddy of mine and myself combined two different pieces of our own code to come up with this nice little export script.

The on-demand export script allows you to create a flat file called ondemand.lst and list within it the tables you want to export in the following manner:

owner.tablename
owner.tablename
etc.

The script will loop through this lst file until all entries have been exported.

As an added value, the table being exported will be gzip’ed in memory before being written to disk.

Here is the code for the ondemand_exp.sh script:
Note: Remember to change the

#!/bin/ksh
. /home/sfulke/.profile

curdt=`date +"%Y-%m-%d_%H:%M"`
clear

################################################################
# MODULE: ondemand_exp.sh (ksh)
# DESCRIPTION: Allows user to export tables by listing them in file . Output is
# compressed in memory via gzip.
# PURPOSE: To increase user options when exporting Oracle tables.
# Example: ./ondemand_exp.sh
#Disclaimer: Use is at your sole risk. All info is provided "as is", without any warranty, # whether express or implied, of its accuracy, completeness.
# CHANGE HISTORY:
# Date Who Change Description
# ------------- --------------------- ----------------------------
# 08-NOV-2004 Tony Bryant (abryan) Initial Creation
# 08-NOV-2004 Samual C. Fulkerson Export code supplied
################################################################

exp_log='’
exp_fil=''
exp_cod=''

filename=$exp_cod/ondemand.lst
read_file_line=`wc -l < $filename`
i=1
while [ "$i" -le "$read_file_line" ]
do
table_name=`cat $filename | head -$i | tail -1`

# remove perviously created named pipe
rm $exp_cod/.ondemand.exp.pipe
# create a named pipe
mknod $exp_cod/.ondemand.exp.pipe p
# read the pipe - output to zip file in the background
gzip < $exp_cod/.ondemand.exp.pipe > $exp_fil/exp_$table_name.$curdt.dmp.gz &
# feed the pipe
command="exp userid= file=$exp_cod/.ondemand.exp.pipe log=$exp_log/exp_$table_name._$curdt.log grants=Y index
es=Y rows=Y constraints=Y direct=Y compress=Y tables=$table_name"
$command
wait
i=`expr $i + 1`
done

exit

You can add a mailx statement to email you upon creation it you like. For exporting a large table list you might even wan to add an error checking piece for grep’ing through the log files and emailing you the results to simplify the task somewhat.

Hiding Passwords from Unix

When you execute a sqlplus session from a unix command line as such (sqlplus /) you can see the user name and password by doing a simple ps –ef from the command line. This of course is a serious security violation. One way to avoid this is to allow sqlplus to prompt you for the password.

If you are executing a script (sqlplus / @scriptname.sql) you will be able to do a ps –ef and see it there as well. To avoid this you can imbed a connect string (connect /) into the first line of the sql script you are executing(sqlplus @scritpname.sql).

Another way to do this would be using an OS authenticated user account to execute the sql script (sqlplus / @scriptname.sql).

However, since we use SAS and since SAS does not play well with Oracle’s OS Authentication and requires a username/password you could try it this way:

echo password |sqlplus username @scriptname.sql

Thanks

FlashBacks (UnDroping A Table)

It is a beautiful day outside, the sun is shining and a cool breeze is gently blowing… and then the phone rings. “Opps, I dropped a table! I dropped the big, I need it now to complete the production process table!”

This table was 611 gbs in size. The process that creates it takes many hours (36) to complete as there are many steps and joins that make it up.

So as not to keep you in suspense, I used the flashback feature of Oracle 10R2 to recover it from the “recycle bin”. It took less than 1 second to accomplish the recovery.

Note: This is Oracle 10gR2 (10.2.0.1) on SunOS 10.
This base is not running in archive log mode.

Here is the syntax from an example:

Note: Don't use this next line unless you really want to empty your recycle bin!
11:48:07 SQL> purge recyclebin;
Recyclebin purged.

11:48:21 SQL> show recyclebin
11:48:29 SQL>

11:48:49 SQL> create table test1
11:48:59 2 as
11:49:00 3 select * from dba_tables;
Table created.

11:49:07 SQL> drop table test1;
Table dropped.

11:49:23 SQL> show recyclebin
ORIGNAME_PLUS_SHOW_RECYC OBJECTNAME_PLUS_SHOW_RECYC
-------------------------------- ------------------------------
OBJTYPE_PLUS_SHOW_RECYC DROPTIME_PLUS_SHOW_
------------------------- -------------------
TEST1 BIN$FX3qmRLpXRfgRAADug+/iA==$0
TABLE 2006-06-05:11:49:23

11:49:31 SQL> flashback table test1 to before drop;
Flashback complete.

11:51:47 SQL> show recyclebin;

11:52:34 SQL> select count(1) from test1;
COUNT(1)
--------------------
1,970

It is as simple as that.

Now if you do not want to have a table go to the recycle bin when you drop a table… then:

11:54:10 SQL> drop table test1 purge;

Table dropped.

Elapsed: 00:00:00.82
11:54:30 SQL> show recyclebin;

To purge a user recycle bin:
11:54:32 SQL> purge recyclebin;
Whatever user you are logged in as at the time of this statement execution will have their recycle bin objects purged.

If you want to purge all users recycle bins:
11:55:01:17 SQL> purge dba_recyclebin; (for 9i and 10R1)

11:55:12:22 SQL> connect sys/ as sysdba
Connected
11:55:12:83 SQL> purge dba_recyclebin; (for 10gR2)

Thanks