Oracle EXPDP commands

The expdp (Data Pump Export) utility is used to export data and metadata from an Oracle database. It provides several command-line options to customize the export process. Below are some common expdp commands and their explanations:

Basic Syntax:
 

expdp [username/password] DIRECTORY=directory_name DUMPFILE=dumpfile_name LOGFILE=logfile_name [options]

Common Expdp Commands:
Export a Full Database:
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log

FULL=Y: Exports the entire database.
Export a Specific Schema:
expdp system/password SCHEMAS=schema_name DIRECTORY=dpump_dir DUMPFILE=schema.dmp LOGFILE=schema.log

SCHEMAS=schema_name: Exports a specific schema.
Export Specific Tables:
expdp system/password TABLES=table1,table2 DIRECTORY=dpump_dir DUMPFILE=tables.dmp LOGFILE=tables.log

TABLES=table1,table2: Exports specific tables.
Export a Specific Table with Data and Metadata:

expdp system/password TABLES=table_name DIRECTORY=dpump_dir DUMPFILE=table.dmp LOGFILE=table.log

TABLES=table_name: Exports a specific table.
Export with Compression:
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log COMPRESSION=ALL

COMPRESSION=ALL: Compresses all data during export.
Export with Data Filtering (e.g., Export Data from a Specific Date):
expdp system/password TABLES=table_name DIRECTORY=dpump_dir DUMPFILE=table.dmp LOGFILE=table.log QUERY=table_name:"WHERE created_date > TO_DATE('2024-01-01', 'YYYY-MM-DD')"
QUERY=table_name:"WHERE condition": Filters rows based on a condition.
Export Metadata Only:
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=metadata.dmp LOGFILE=metadata.log CONTENT=METADATA_ONLY
CONTENT=METADATA_ONLY: Exports only metadata (no data).
Export Data Only:
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=data_only.dmp LOGFILE=data_only.log CONTENT=DATA_ONLY
CONTENT=DATA_ONLY: Exports only data (no metadata).
Export a Database with a Specific Date Format:
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log PARALLEL=4
PARALLEL=4: Uses 4 parallel threads for faster export.
Export with a Job Name:

expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log JOB_NAME=export_full_db
JOB_NAME=export_full_db: Assigns a name to the export job.

Additional Parameters:
CONTENT: Specifies whether to export metadata only (METADATA_ONLY), data only (DATA_ONLY), or both (ALL).
EXCLUDE: Excludes specific objects or object types from the export. Example: EXCLUDE=TABLE:"='table_name'".
INCLUDE: Includes specific objects or object types in the export. Example: INCLUDE=TABLE:"IN ('table1', 'table2')".
REMAP_SCHEMA: Remaps schema names. Example: REMAP_SCHEMA=old_schema:new_schema.
REMAP_TABLESPACE: Remaps tablespace names. Example: REMAP_TABLESPACE=old_tablespace:new_tablespace.

Directory Object:
Before running expdp, ensure that the DIRECTORY object exists in the database and points to a valid filesystem directory where the dump files will be written.
 
 
CREATE OR REPLACE DIRECTORY dpump_dir AS '/path/to/directory';

Example Execution:
To execute an expdp command, open a command prompt or terminal and run the appropriate expdp command based on your requirements. Ensure you have the necessary privileges and that the Oracle environment variables (ORACLE_HOME and PATH) are set correctly.


Conclusion:
The expdp utility offers powerful options for exporting data and metadata from Oracle databases. By using the appropriate parameters and options, you can tailor the export process to meet specific needs and optimize performance.

Identify and Terminate Sessions in oracle

 

First, you have to identify the session to be killed with alter system kill session.

Step-by-Step Instructions to Identify and Terminate a Session:

Invoke SQL*Plus:

First, open SQL*Plus to begin the process.

Query V$SESSION to Identify the Session:

Use the following query to retrieve the session details. This will list all active sessions with their respective SID, SERIAL#, STATUS, SCHEMANAME, and PROGRAM.

SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session;

The SID (Session Identifier) and SERIAL# values of the Oracle session to be killed can then be identified from this output.

Execute the ALTER SYSTEM Command:

Substitute the identified SID and SERIAL# values and issue the alter system kill session command.

ALTER SYSTEM KILL SESSION 'sid,serial#';

Handling Sessions Marked for Kill:

Sometimes, Oracle is not able to kill the session immediately with the alter system kill session command alone. Upon issuing the command, the session may be 'marked for kill' and will be terminated as soon as possible.

Forcing Session Termination:

In the case where a session is 'marked for kill' and not terminated immediately, you can force the termination by adding the immediate keyword:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Verify Termination:

To ensure that the session has been terminated, re-query the V$SESSION dynamic performance view:

SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session WHERE SID = 'sid' AND SERIAL# = 'serial#';

The value of the STATUS column will be 'ACTIVE' when the session is making a SQL call and 'INACTIVE' if it is not.

Confirm PMON Cleanup:

After the Process Monitor (PMON) has cleaned up the session, the row will be removed from V$SESSION. Re-query to confirm:

SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session WHERE SID = 'sid' AND SERIAL# = 'serial#';

Handling RAC Environments:

In a Real Application Clusters (RAC) environment, you can optionally specify the INST_ID, which is shown when querying the GV$SESSION view. This allows you to kill a session on a different RAC node.

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

By following these detailed steps, you can efficiently identify and terminate a session using SQL*Plus, ensuring minimal disruption and maintaining database integrity.