Working with Oracle database supported application, which we want to internationalize, and then ‘charset’ is crucial factor to provide Unicode storage ability.
If we are going to internationalize a new project then we can start by creating a Unicode supported database.
For example, we can select AL32UTF8 charset while creating the database.
If we are going to support internationalization on an existing database then, we need to change the charset of database.
There are two ways of changing charset of a database.
Either you can directly alter the charset of existing database or by performing full import export.
First you need to know the current charset of database
<pre>
SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ;
</pre>
This query will tell you current charset of your database.
Other Oracle database NLS parameters can be known by following query
<pre>
SELECT * FROM NLS_DATABASE_PARAMETERS;
</pre>
To alter charset of database directly, the target charset should be a strict superset of current database charset.
You can alter charset with following steps:
1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
2. Do a full backup of the database, because the CSALTER script cannot be rolled back.
3. Start up the database.
4. Run the Database Character Set Scanner utility.
<pre>
CSSCAN /AS SYSDBA FULL=Y…
</pre>
5. Run the CSALTER script.
<pre>
@@CSALTER.PLB
SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;
STARTUP;
</pre>
The other way is quite safer and recommended.
Following are the steps to export and import data in oracle 11g R2:
To export database:
1. Start SqlDeveloper.
2. Create connections for source and destination. (Note: Use system user)
3. From View menu select DBA. DBA pane will appear.
4. Now in DBA pane, add connections.
5. Click and expand the source connection, then right-click on ‘Data Pump’, and then click on ‘Data Pump Export Wizard.’ Export Wizard will start.
6. In ‘Data Pump Export Wizard,’ select Schema option in Types to be Exported in ‘Data Pump’ and click on ‘Next.’
7. Select(double click) a schema that you want to export and click ‘Next.’
8. Skip the step Filter and click ‘Next.’
9. Now, click on ‘More,’ now select Type as ‘ALL OBJECTS’ and click on ‘Lookup.’ All the objects are displayed.
10. From the interface select and add objects to include for export and click ‘Next.’ To include all objects just click on down double-arrow button.
11. Specify the name for log file or just leave the default and click ‘Next.’
12. In ‘Choose Output Files’ section, specify the File Names (For example exportdata.dmp), choose an appropriate option if file already exists, and click ‘Next.’
13. Now leave the default settings and click ‘Next.’
14. In ‘Summary’ section, verify whether the desired schema is included, and finally click ‘Finish.’ You will see this job and its status in ‘Data Pump’ > ‘Export Jobs’ of DBA pane.
15. Now, copy this file from the source machine to the destination machine.
For importing database:
1. Start SqlDeveloper and open ‘Data Pump Import Wizard.’
2. In Wizard, select Connection where you want to import(destination system).
3. Select Schema and specify file Name. In this case, file is exportdata.dmp and then click ‘Next.’
4. Now, include the schema that you want to import from ‘Available Source Schemas’ to ‘Selected Source Schemas’ and click ‘Next.’
5. Skip Remapping step by clicking ‘Next.’
6. In Options step, select appropriate options for ‘Action On Table’ if Table Exists and click ‘Next.’
7. Click ‘Next’ and finally Click ‘Finish.’
Your database will be imported as per selected options and schemas.
