Oracle Tools, Commands, and Procedures

There are thousands of ways and methods to meet your requirement by useing Oracle Server..and Oracle is keep growing up and will never stop..
Many of engineer regardless of their specific domain of software development, they are eager to catch up a latest techinology and want to be a master of the technology..
You,, if you get tired of catching up, just read this article about Oracle... I just introduce necesarry tools, built-in procedures, and softwares that are all part of Oracle here.
All those are currently used in big E-commerce web system maintenance project.
Not sure those are the best way of handling a problem but certainly can resolve it.


Contents:

1. Create Database Link
2. Create Synonym
3. Original Export and Import Utilities
4. Create Tablespace
5. Create User
   

#DATABASE LINK#
CREATE DATABASE LINK {link_name}
CONNECT TO {user} IDENTIFIED BY {password}
USING '{connect string}';

CONNECT TO clause -- lets you enable a connection to the remote database.
You can specify this clause and the dblink_authentication clause only if you are creating a shared database link.

user IDENTIFIED BY password -- Specify the username and password used to connect to the remote database
using a fixed user database link.
If you omit this clause, the database link uses the username and password of each user who is
connected to the database. This is called a connected user database link.

USING 'connect string' -- Specify the service name of a remote database.
If you specify only the database name, then Oracle Database implicitly appends
the database domain to the connect string to create a complete service name.
Therefore, if the database domain of the remote database is different from that of the current database,
then you must specify the complete service name.

#CREATE SYNONYM#
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm#SQLRF01401)
CREATE OR REPLACE SYNONYM VOIUSER.TR_VOI_SHOP_SYN_STOCK FOR CHANNEL.TR_VOI_SHOP_SYN_STOCK;

OR REPLACE - Specify OR REPLACE to re-create the synonym if it already exists.
Use this clause to change the definition of an existing synonym without first dropping it.

schema - Specify the schema to contain the synonym.
If you omit schema, then Oracle Database creates the synonym in your own schema.
You cannot specify a schema for the synonym if you have specified PUBLIC.

#19 Original Export and Import#
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#SUTIL001)

(1)export command example

#user-mode export example:
exp {user}/{password} file={export file name with extension} owner={owner schema name} log={log file name}

#table-mode export example:
exp {user}/{password} file={export file name with extension} tables=('TABLE_A','TABLE_B',

'TABLE_C') log={log file name}

(file) -- Specifies the names of the export dump files.
The default extension is .dmp, but you can specify any extension.
Because Export supports multiple export files, you can specify multiple filenames to be used.

(log) -- default nono.Specifies a filename to receive informational and error messages.
If you specify this parameter, messages are logged in the log file and displayed to the terminal display.
#where is the log file placed on Oracle running host??

(owner) -- default none.Indicates that the export is a user-mode export and lists the users
whose objects will be exported.
If the user initiating the export is the database administrator (DBA), multiple users can be listed.
User-mode exports can be used to back up one or more database users.
For example, a DBA may want to back up the tables of deleted users for a period of time.
User mode is also appropriate for users who want to back up their own data or
who want to move objects from one owner to another.
#What is Export session in User Mode?
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2558)

(tables) -- default none.Specifies that the export is a table-mode export and lists the table names and partition and
subpartition names to export. You can specify the following when you specify the name of the table:
*schemaname specifies the name of the user's schema from which to export the table or partition.
The schema names ORDSYS, MDSYS, CTXSYS, LBACSYS, and ORDPLUGINS are reserved by Export.
*tablename specifies the name of the table or tables to be exported.
Table-level export lets you export entire partitioned or nonpartitioned tables.
If a table in the list is partitioned and you do not specify a partition name,
all its partitions and subpartitions are exported.The table name can contain any number of '%' pattern
matching characters, which can each match zero or more characters in the table name against the table objects
in the database. All the tables in the relevant schema that match the specified pattern are selected for export,
as if the respective table names were explicitly specified in the parameter.
*partition_name indicates that the export is a partition-level Export.
Partition-level Export lets you export one or more specified partitions or subpartitions within a table.

The syntax you use to specify the preceding is in the form:

schemaname.tablename:partition_name
schemaname.tablename:subpartition_name

If you use tablename:partition_name, the specified table must be partitioned,
and partition_name must be the name of one of its partitions or subpartitions.
If the specified table is not partitioned,
the partition_name is ignored and the entire table is exported.

(statistics) -- default ESTIMATE.Specifies the type of database optimizer
statistics to generate when the exported data is imported.
Options are ESTIMATE, COMPUTE, and NONE. See the Import parameter STATISTICS and Importing Statistics.

(filesize) -- default is OS dependent.Export supports writing to multiple export files,
and Import can read from multiple export files. If, on export, you specify a value (byte limit) for
the Export FILESIZE parameter, Export will write only the number of bytes you specify
to each dump file. On import, you must use the Import parameter FILESIZE to tell
Import the maximum dump file size you specified on export.


(2)import command example

#imp {user}/{password} full=y file=webext1.dmp ignore=y commit=y indexes=n constraints=n log={log file name}

Import parameters
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#SUTIL001)

(full) -- Default: y. Specifies whether to import the entire export dump file.

(file) -- Default: expdat.dmp.Specifies the names of the export files to import. The default extension is .dmp.
Because Export supports multiple export files (see the following description of the FILESIZE parameter),
you may need to specify multiple filenames to be imported.

(ignore) -- Default: n.Specifies how object creation errors should be handled.
If you accept the default, IGNORE=n, Import logs or displays object creation errors before continuing.
If you specify IGNORE=y, Import overlooks object creation errors when it attempts to create database objects,
and continues without reporting the errors.Note that only object creation errors are ignored; other errors,
such as operating system, database, and SQL errors, are not ignored and may cause processing to stop.

(commit) -- Default: n.Specifies whether Import should commit after each array insert.
By default, Import commits only after loading each table, and Import performs a rollback when an error occurs,
before continuing with the next object.

(indexes) -- Default: y.Specifies whether or not to import indexes. System-generated indexes such as LOB indexes,
OID indexes, or unique constraint indexes are re-created by Import regardless of the setting of this parameter.
You can postpone all user-generated index creation until after Import completes, by specifying INDEXES=n.

(constraints) -- Default: y.Specifies whether or not table constraints are to be imported.
The default is to import constraints.
If you do not want constraints to be imported, you must set the parameter value to n.
Note that primary key constraints for index-organized tables (IOTs) and object tables are always imported.

(log) -- Default: none.Specifies a file to receive informational and error messages.
If you specify a log file,
the Import utility writes all information to the log in addition to the terminal display.

some tips examples:
1) Import an exported file to as different user.
#imp --- fromuser={old_user_name} touser={new_user_name}

(fromuser) -- Default: none.A comma-delimited list of schemas to import.
This parameter is relevant only to users with the IMP_FULL_DATABASE role.
The parameter enables you to import a subset of schemas from an export file containing multiple schemas
(for example, a full export dump file or a multischema, user-mode export dump file).
Schema names that appear inside function-based indexes, functions, procedures,
triggers, type bodies, views, and so on, are not affected by FROMUSER or TOUSER processing.
Only the name of the object is affected. After the import has completed, items in any TOUSER schema
should be manually checked for references to old (FROMUSER) schemas, and corrected if necessary.
You will typically use FROMUSER in conjunction with the Import parameter TOUSER, which you use
to specify a list of usernames whose schemas will be targets for import (see TOUSER).
The user that you specify with TOUSER must exist in the target database prior to
the import operation; otherwise an error is returned.

(touser) -- Default: none.Specifies a list of user names whose schemas will be targets for Import.
The user names must exist prior to the import operation; otherwise an error is returned.
The IMP_FULL_DATABASE role is required to use this parameter.
To import to a different schema than the one that originally contained the object, specify TOUSER.

(indexfile) -- Default: none.Specifies a file to receive index-creation statements.
When this parameter is specified, index-creation statements for the requested mode are extracted and written
to the specified file, rather than used to create indexes in the database. No database objects are imported.
If the Import parameter CONSTRAINTS is set to y, Import also writes table constraints to the index file.

#Create tablespace#

(logging_clause) -- (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses005.htm#i999782)
The logging_clause lets you specify whether creation of a database object will be
logged in the redo log file (LOGGING) or not (NOLOGGING).

#create user#
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#SQLRF01503)

(Quota clause) -- Use the QUOTA clause to specify the maximum amount of space the user can allocate in the tablespace.
A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces.
UNLIMITED lets the user allocate space in the tablespace without bound.

(Account clause) -- Specify ACCOUNT LOCK to lock the user's account and disable access.
Specify ACCOUNT UNLOCK to unlock the user's account and enable access to the account.