Execute an SQL script file in SQLPlus

To execute a script file in SQLPlus, type @ and then the file name.

SQL > @{file}

e.g if your file name is script.sql

then

SQL > @script.sql

If script is not in current directory you can specify the path

SQL > @{path}{file}

e.g

SQL >@/opt/oracle/script.sql

Oracle database backup and restore

Here is command to take backup from the oracle database:

exp userid=system/{system_passwd}@{schema} owner={schema_owner} file={backup_file}.dmp buffer=102400 statistics=none grants=n log={backup_log_file}.log

Where

system_passwd is password for system user

schema is database schema name

schema_owner is user name for the database

backup_file is backup file name

backup_log_file is log file name for backup logs

To restore database from the backup (dmp file) use following command

imp system/{system_passwd}@{schema} file={backup_file}.dmp log={import_log_file}.log fromuser={existing_user} touser={new_user}


Where
system_passwd is password for system user

schema is database schema name

backup_file is backup file name

import_log_file is log file name where all the logs will be written

existing_user is database user name from the dmp file

new_user is database user name which is being restored.

Creating DBLink in Oracle

Login to database execute below command:

create public database link {link_name} connect to {user_name} identified by using '{connect_descriptor}'

Where

1. link_name is the name of the database link

2. user_name is the username of the database to get connected

3. connect_descriptor is the tns entry ($OARCLE_HOME/network/admin/tnsname.ora)