It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.
Oracle put locks on tables when any DML/DDL operations performing. Generally, some internal processes accesses the data and locks the table. So if you want to forcibly unlock the table you can follow the below steps.
- Get the Object ID from the dba_objects table. All of your tables will be assigned a unique id and stores a reference in dba_objects table. This table is useful in finding the basic information about each object stored on your Oracle instance, and not only tables.
Here is the SQL statement to get the object id of the locked table.
SELECT object_id FROM dba_objects WHERE object_name='YOUR TABLE NAME'; - Get the SID for the corresponding Object ID we have obtained from the step 1. You can get the SID from v$lock table. Here is the query.
SELECT sid FROM v$lock WHERE id1=OBJECT ID FROM STEP1 - Note down all sid values from step 2. Get the sid, serial number pairs from the v$session table using the SID obtained from the step 2. Here is the query.
SELECT sid, serial# from v$session where sid in (COMMA SEPARATED LIST OF SIDs FROM STEP2.) - Note down the information from step 3. Now we have to kill the sessions that locked the object we require. Below is the query to do that.
ALTER SYSTEM KILL SESSION (SID,SERIAL#) pair values from step 3
e.g. ALTER SYSTEM KILL SESSION '231','23454'
Oracle prvides these very important tools IMP and EXP, which we can use to import and export schemas, tables or whole database from remote database to our local database respectively. We can use this tool to schedule the import/exports.
To do schedule the import/export jobs, just write a batch file and schedule it using AT command available in WINDOWS. Similarly in LINUX/UNIX also we can do similarly by writing a shell script and schedule using any of the scheduler available in Linux/Unix.
IMP and EXP are used in four ways.
Parameters for IMP:
To do schedule the import/export jobs, just write a batch file and schedule it using AT command available in WINDOWS. Similarly in LINUX/UNIX also we can do similarly by writing a shell script and schedule using any of the scheduler available in Linux/Unix.
IMP and EXP are used in four ways.
- Tables: To import/export perticular table we specify
- Owner: To import/Export all the objects owned by given user
- Tablespaces: To export/import tablespace
- Full: To import/export full database
Parameters for IMP:
- fromuser: to import objects of given user
- touser: to which user objects should import
- show: this parameter makes the command to show which are imported and does not perform actual import. This may used for testing purpose.
Note: the structure of touser and fromuser schemas should be same in order to import command perform successfully without errors.
Parameters for EXP:
- full: to specify the full export of database should be made
- tablespaces: specifies the tablespaces are to be export
- tables: this is for table export
- owner: to export shema of the user
- query: to restrict the exported rows using where clause