9i Export/Import Parameters

USERID:   username/password
BUFFER:   size of data buffer
FILE:   output file (EXPDAT.DMP)
COMPRESS:   import into one extent (Y)
GRANTS:   export grants (Y)
INDEXES:   export indexes(Y)
ROWS:   export data rows (Y)
CONSTRAINTS:   export table constraints (Y)
CONSISTENT:   cross-table consistency (N)
LOG:   log file of screen output (None)
STATISTICS:   analyze objects (ESTIMATE)
DIRECT:   Bypass the SQLcommand processing layer (N) (new in Oracle8)
FEEDBACK:   Show a process meter (a dot) every X rows exported (0 – Xvalue)
HELP:   Shows help listing MLS MLS_LABEL_FORMAT Used with secure Oracle; we won't cover
these.
FULL:   export entire file (N)
OWNER:   list of owner usernames
TABLES:   list of table names
RECORDLENGTH:  length of IO record
INCTYPE:  incremental export type
RECORD:   track incr. export (Y)
PARFILE:   parameter file name

How to get parent/child of a table in Oracle

This query gives list of children tables of a given table:


SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&tab');


And this one gives list of parent tables of a given table:

SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('&tab');

Chmod commands

The UNIX chmod command is used to change the execution permissions of a UNIX file. The  chmod stands for change mode. The chmod command is based on the permissions and the chmod permissions can be assigned either by number or by a letter value.

Owner	Group	World	Meaning
7	7	7	Read + Write + execute
6	6	6	Write + execute
5	5	5	Read + execute
4	4	4	read only
2	2	2	write only
1	1	1	Execute only

Owner (u)	Group (g)	World(o)	Meaning
rwx	rwx	rwx	Read + Write + execute
rw	rw	rw	Read + Write
rx	rx	rx	Read + execute
wx	wx	wx	Write + execute
r	r	r	Read only
w	w	w	Write Only
x	x	x	Execute only

                 

Suspend & Resume a Database

                The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets us back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The suspend command is not specific to an instance. In an Oracle Real Application Clusters environment, when we issue the suspend command on one system, internal locking mechanisms propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, if someone starts a new instance another instance is being suspended, the new instance will not be suspended.

Using the ALTER SYSTEM RESUME statement to resume normal database operations. The SUSPEND and RESUME commands can be issued from different instances. For example, if instances 1, 2, and 3 are running, and we issue an ALTER SYSTEM SUSPEND statement from instance 1, then we can issue a RESUME statement from instance 1, 2, or 3 with the same effect. The suspend/resume feature is useful in systems that allow us to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If we use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then we can use the suspend/resume feature to facilitate the split. 

The suspend/resume feature is not a suitable substitute for normal shutdown operations, because copies of a suspended database can contain uncommitted updates.

The following statements illustrate suspend and resume usage. The V$INSTANCE view is queried to confirm database status.

     SQL>    alter system suspend;
               System altered.

     SQL>   select database_status from V$instance;
  

             DATABASE_STATUS
               ------------------------
                 SUSPENDED

       SQL>  alter system resume ;
                System altered.

      SQL> select database_status from v$instance ;
        

             DATABASE_STATUS
             -------------------------
              ACTIVE

Oracle Release Dates

Version	Release Date
Oracle 6	1988
Oracle 7	1992
Oracle 8	1997
Oracle 8i	1998
Oracle 9i	2001
Oracle 10gR1	2003
Oracle 10gR2	July 2005
Oracle 11gR1	Aug 2007
Oracle 11gR2	Sep 2009

RMAN Backup Views

The following Oracle dynamic views can be used to obtain RMAN information stored in the control file:

•  V$ARCHIVED_LOG:    show which archives have been created, backed up, and      cleared in the database
    
•  V$BACKUP_CORRUPTION:    show which blocks have been found to be corrupt during a backup of a backup set             

• V$BACKUP_DATAFILE:   useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also help you find the number of corrupt blocks in the datafile 

•    V$BACKUP_DEVICE:   display information about supported backup devices. DISK is  not returned because it is always available

•  V$BACKUP_FILES:   display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPYcommands

•  V$BACKUP_PIECE:    show backup pieces created for backup sets 

• V$BACKUP_REDOLOG:   show archived logs stored in backup sets 

•  V$BACKUP_SET :    show backup sets that have been created 

•  V$BACKUP_SPFILE:   display information about server parameter files in backup sets 

• V$COPY_CORRUPTION:     show which blocks have been found to be corrupt during an image copy 

• V$DATABASE_BLOCK_CORRUPTION:    list database blocks marked as corrupt during the most recent RMAN backup 

• V$RMAN_CONFIGURATION:    list information about RMAN persistent configuration settings 

• V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE:     proxy settings for RMAN

• V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO:    backup performance statistics