WCSADMIN.COM

• Bijus: http://www.bijoos.com/oracle/
• DBA Tools http://www.dbatoolz.com/scripts_desc.htm
• Oracle 9i Documentation http://otn.oracle.com/pls/db92/db92.homepage
• Oracle Error Messages http://otn.oracle.com/pls/db92/db92.error_search?remark=homepage&prefill=ORA-
• Ask Tom http://asktom.oracle.com/
• Oracle Documentation Download link: http://otn.oracle.com/documentation/index.html
• Oracle 9i Complete documentation list http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage
• Oracel JDBC Code Templates http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/htdocs/templates.htm
• Oracle FAQ http://www.orafaq.com

1. Connecting to a Database
I use two versions of this command when connecting to a database using a shell prompt or a dos prompt:
Command: sqlplus {userId}@{tnsName}
Example: sqlplus wcsadmin@ora92
When you run this command, you will be asked to enter a password to continue.
Alternatively you can enter the password as part of the command as follows:
Command: sqlplus {userId}/{password}@{tnsname}
Example: sqlplus wcsadmin/password@ora92

2. Backup Database
I use export command to back up a database. To see various options available for this command use the help command as follows:
Command: exp HELP=Y
As you would notice from the output of the above command, export can be used to backup(export) a single, multiple or all tables.
Command: exp {userId}/{password}@tnsname FILE={PathToDumpFile} LOG={PathToLogFile}
Example: exp wcsadmin/password@ora92 FILE=/opt/OraWCSDumps/wcs20070101.dmp LOG=/opt/OraWCSDumps/wcs20070101.log
Like in the case of the sqlplus command, if you do not specify the password, you will be prompted for one.

3. Restore Database
I use import command to restore a database backup that is backed up using the export command. To see various options available for this command use the help command as follows:
Command: imp HELP=Y
As you would notice from the output of the above command, import offer several options.
Command: imp {userId}/{password}@tnsname FILE={PathToDumpFile} LOG={PathToLogFile}
Example: imp wcsadmin/password@ora92 FILE=/opt/OraWCSDumps/wcs20070101.dmp LOG=/opt/OraWCSDumps/wcs20070101.log
Like in the case of the sqlplus command, if you do not specify the password, you will be prompted for one.

4. Run SQL
Once you are in sqlplus, you can run sql from a file on the file system.
Command: @{PathToFileName}
Example: @C:\sql\FindOffers.sql

5. Substitution Variables
If you have to run a sql statement repeatedly but with various values for a column in the where clause, you would use substitution variables. Here is an example:
select interfacename, classname from cmdreg where interfacename like ‘%&InterfaceName%’ and storeent_id = &Store
When you run the above sql, you will be prompted to enter values for substitution variables &InterfaceName and &Store. Once you enter the values, you will get the output of your sql statement. At the sql prompt you can run this command again by simply using / to run the previous statement. You will again be prompted to enter a value for &InterfaceName and &Store. You can use these substitution variables any number of times in a sql statement and at any place in the statement including for table and column names