Exporting Database Contents

This section provides an overview of exporting database contents and discusses how to:

  • Set the NLS_LANG variable.

  • Export database contents using Oracle Data Pump.

  • Export database contents using the Oracle Export utility.

Setting the NLS_LANG Variable

Before running the export process, verify that the character set specified by NLS_LANG is set to match the character set of the database. NLS_LANG is an environment variable on Unix and a registry setting under HKEY_LOCAL_MACHINE\Software\Oracle on Microsoft Windows.

To verify the current character set of the Oracle database, sign in the Oracle SQL utility of your choice as the database administrator. Then, run the following SQL statement:

SELECT VALUE FROM SYS.V_$NLS_PARAMETERS
WHERE PARAMETER ='NLS_CHARACTERSET';

For example, if the database character set is WE8ISO8859P1, NLS_LANG should be set to AMERICAN_AMERICA.WE8ISO8859P1, or another language combination with WE8ISO8859P1 as the character set.

Creating and Running the Export Preparation Script for Oracle Data Pump

  • Create a directory for the export file such as c:\temp\dmpdir

  • In a command prompt, set your Oracle SID 

    set ORACLE_SID = <SID>

  • In SQL*Plus run three commands to specify your directory and grant permissions to the username you will use to run the export.

  • Sign into the Oracle SQL utility of your choice as the database administrator: 

    sqlplus / as sysdba

  • Grant EXP_FULL_DATABASE to username;

  • Check for invalid objects with this command

    select object_name from dba_objects where status='INVALID';

    If any rows are returned, you may be able to fix them by running dbmsdb.sql and/or utlrp.sql. There should be no invalid rows before proceeding.

  • Quit sql plus.

The data export will specify two schemas: PS,and the ownerid specified in the PSSTATUS.OWNERID table.On the command line,run the expdp command:

expdp username/password@SID dumpfile=filename 
schemas=OWNERID,ps

For instance:

 expdp system/manager@HR  dumpfile=exp.dmp schemas=SYSADM,ps

At the end of the export, look for the final line such as :”Job 'OWNERID'.'SYS_EXPORT_SCHEMA_01' successfully completed.

The export dump file will appear in the dpdump directory under the admin\SID directory. To change the output directory refer to Oracle documentation on the directory parameter of expdp.

In case of errors, you can rerun and add the parameter trace =1FF0F00, to generate trace files.

Note:

Ensure that the file system on which you will be creating the export file has sufficient space available to accommodate all the data in the PeopleSoft database. A good estimate of the space needed is to calculate the amount of space currently taken by tables owned by the PeopleSoft owner ID and add 20 percent. You can get this data from querying the DBA_FREE_SPACE and DBA_DATA_FILES catalog views. Remember that index data is not stored in export files, only the index definition.

The export (.dmp) file created by this process will be referred to as the database export in this document.