Non-system schema Exports in Oracle 9i

Shell Script:

oracle on Server-Name:/admin/home/oracle> cat non_system_schemas.sh

# /bin/ksh

exp userid=\'/ as sysdba\' parfile=/admin/home/oracle/non_system_schemas.par

PAR file:

oracle on Server-Name:/admin/home/oracle> cat non_system_schemas.par

file=/backup2/non_system_schemas.dmp log=/backup2/non_system_schemas.log owner=schema1,

schema2,

schema3,

schema4,

schema5,

schema6,

schema7,

schema8,

schema9,

schema10

Shell script Execution:

oracle on Server-Name:/admin/home/oracle>nohup /admin/home/oracle/non_system_schemas.sh &

[1]     7410

oracle on Server-Name:/admin/home/oracle>Sending output to nohup.out

oracle on Server-Name:/admin/home/oracle>jobs

[1] +  Running                 nohup /admin/home/oracle/non_system_schemas.sh &

oracle on Server-Name:/backup2>jobs

'[1] +  Running                 nohup /admin/home/oracle/non_system_schemas.sh &

Export terminated successfully without warnings.

^C[1] +  Done                    nohup /admin/home/oracle/non_system_schemas.sh &

List of system's schemas

SQL> select username,created from dba_users where USERNAME IN ('SYS','SYSTEM','OUTLN','ANONYMOUS','CTXSYS','DBSNMP','LBACSYS','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','WKSYS','WMSYS','XDB');

USERNAME                       CREATED

------------------------------ ---------

DBSNMP                         12-JUN-04

OUTLN                          30-JUL-99

SYSTEM                         30-JUL-99

SYS                            30-JUL-99

Set Commands

     /*  
        Multiple line comments
        Can go between these delimiters

     */ 

SET TERM      OFF 
-- TERM = ON will display on terminal screen (OFF = show in LOG only)

If we set TERM OFF output will not be returned to the standard output when we execute a script, It will be logged in to the spool file , If it set to ON then will be returned to standard output screen as well as it logged in to the spool file.

SET ECHO      ON 
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.

SET TRIMSPOOL ON 
-- TRIMSPOOL = ON will remove trailing spaces from spooled output

SET TRIMOUT   ON 

-- TRIMOUT = ON will remove trailing spaces from output

SET HEADING   OFF 
-- HEADING = OFF will hide column headings

SET FEEDBACK  OFF 
-- FEEDBACK = ON will count rows returned

SET PAUSE     OFF 
-- PAUSE = ON .. press return at end of each page

SET PAGESIZE  0   
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)

e.g.

SQL> set pages 0
SQL> select tablespace_name,current_users,round (USED_BLOCKS*8/1024/1024,2) "GB in Use",
round(FREE_BLOCKS*8/1024/1024,2) "Free GB",round(MAX_SIZE/1024,2) "Msize GB",
round(MAX_USED_BLOCKS*8/1024/1024,2) "Max used GB"
from v$sort_segment;  2    3    4
TEMP                                      460       1.88      37.54      39.42       39.42

In the above e.g.By setting pagesize to 0, It supress the headings.

SET LINESIZE  80 
-- LINESIZE = width of page (80 is typical)

SET VERIFY    OFF 
-- VERIFY = ON will show before and after substitution variables

-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG

--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;

SPOOL OFF