Home » RDBMS Server » Server Utilities » if i import database using impdp full import its also change character set and nls_lang (oracle database 10.2.0.4.0 (64 bit), windows 7)
if i import database using impdp full import its also change character set and nls_lang [message #647121] Wed, 20 January 2016 01:53 Go to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
hello i want to ask you i create database on my server with this parameter

NLS_LANGUAGE	           GUJARATI
NLS_TERRITORY	            INDIA
NLS_CURRENCY	              Rs
NLS_ISO_CURRENCY	    INDIA
NLS_NUMERIC_CHARACTERS	      .,
NLS_CALENDAR	          GREGORIAN
NLS_DATE_FORMAT	           DD-MM-RR
NLS_DATE_LANGUAGE	   GUJARATI
NLS_CHARACTERSET	   AL32UTF8
NLS_SORT	           BINARY
NLS_TIME_FORMAT	          HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MM-RR HH12:MI:SSXFF AM
NLS_TIME_TZ_FORMAT	HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MM-RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY	Rs
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_COMP	        BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE



after that i import database from full dump take on my another server.after import full database than i check my database parameter they are changed they changed like my another test database.


NLS_LANGUAGE	         AMERICAN
NLS_TERRITORY	         AMERICA
NLS_CURRENCY	          $
NLS_ISO_CURRENCY	 AMERICA
NLS_NUMERIC_CHARACTERS	.,
NLS_CHARACTERSET	 AL32UTF8
NLS_CALENDAR	         GREGORIAN
NLS_DATE_FORMAT	         DD-MON-RR
NLS_DATE_LANGUAGE	AMERICAN
NLS_SORT	        BINARY
NLS_TIME_FORMAT	        HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY	$
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_RDBMS_VERSION	10.2.0.4.0


so i want to ask you if i import database they change my character set and language also just like exported database characterset

[Updated on: Wed, 20 January 2016 01:54]

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647123 is a reply to message #647121] Wed, 20 January 2016 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Your question is not clear.
There are no exports of character set, there are export of data.

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647124 is a reply to message #647123] Wed, 20 January 2016 02:32 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply
i told you before import full dump into my database my nls_database_parameters as below

NLS_LANGUAGE	GUJARATI
NLS_TERRITORY	INDIA
NLS_CURRENCY	Rs
NLS_ISO_CURRENCY	INDIA
NLS_NUMERIC_CHARACTERS	.,
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD-MM-RR
NLS_DATE_LANGUAGE	GUJARATI
NLS_CHARACTERSET	AL32UTF8
NLS_SORT	BINARY
NLS_TIME_FORMAT	HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MM-RR HH12:MI:SSXFF AM
NLS_TIME_TZ_FORMAT	HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MM-RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY	Rs
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE


and after import full dump into my database after import i check my nls_database_parameters they changed as below 

NLS_LANGUAGE	AMERICAN
NLS_TERRITORY	AMERICA
NLS_CURRENCY	$
NLS_ISO_CURRENCY	AMERICA
NLS_NUMERIC_CHARACTERS	.,
NLS_CHARACTERSET	AL32UTF8
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_DATE_LANGUAGE	AMERICAN
NLS_SORT	BINARY
NLS_TIME_FORMAT	HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY	$
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_RDBMS_VERSION	10.2.0.4.0

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647125 is a reply to message #647124] Wed, 20 January 2016 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Import does not change character set as it does not change block size or database name.

[Updated on: Wed, 20 January 2016 02:35]

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647128 is a reply to message #647125] Wed, 20 January 2016 03:51 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
but michel before import full database i query this
select * from nls_database_parameters;


after import i query this same after import and its change my nls_lang and nls_date parameter as like dateabase from i have export full

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647129 is a reply to message #647128] Wed, 20 January 2016 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

How can I believe you when what you said is obviously false: there is no NLS_LANG or NLS_DATE parameters in NLS_DATABASE_PARAMETERS?

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647131 is a reply to message #647128] Wed, 20 January 2016 04:07 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
what is difference between nls_database_parameters and v$nls_parameters

v$nls_parameters its (Dynamic View) and NLS_DATABASE_PARAMETERS its (Static View)

but whats its difference in both

both show me different in my database


select * from nls_database_parameters;

NLS_LANGUAGE	AMERICAN
NLS_TERRITORY	AMERICA
NLS_CURRENCY	$
NLS_ISO_CURRENCY	AMERICA
NLS_NUMERIC_CHARACTERS	.,
NLS_CHARACTERSET	AL32UTF8
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_DATE_LANGUAGE	AMERICAN
NLS_SORT	BINARY
NLS_TIME_FORMAT	HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY	$
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_RDBMS_VERSION	10.2.0.4.0


and 

select * from v$nls_parameters

NLS_LANGUAGE	GUJARATI
NLS_TERRITORY	INDIA
NLS_CURRENCY	Rs
NLS_ISO_CURRENCY	INDIA
NLS_NUMERIC_CHARACTERS	.,
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD-MM-RR
NLS_DATE_LANGUAGE	GUJARATI
NLS_CHARACTERSET	AL32UTF8
NLS_SORT	BINARY
NLS_TIME_FORMAT	HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MM-RR HH12:MI:SSXFF AM
NLS_TIME_TZ_FORMAT	HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MM-RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY	Rs
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647132 is a reply to message #647131] Wed, 20 January 2016 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
what is difference between nls_database_parameters and v$nls_parameters


The documentation tells it, so read it.

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647180 is a reply to message #647132] Thu, 21 January 2016 05:56 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
ok i got it

Quote:
NLS_DATABASE_PARAMETERS gives the permanent NLS parameters of the database where as v%nls_parameters gives the current values of the nls parameters for that session


can you suggest me how can i change nls_database_parameters

i did this but still not change

before change

SQL> select * from nls_database_parameters where parameter= 'NLS_TERRITORY';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------

NLS_TERRITORY
AMERICA


Run this both command to change database NLS_LANGUAGE and NLS_TERRITORY

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
SORCL

SQL> ALTER SYSTEM SET NLS_TERRITORY ='INDIA' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET NLS_LANGUAGE='GUJARATI' SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2066080 bytes
Variable Size             452987232 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14708736 bytes
Database mounted.
Database opened.
SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
SORCL

SQL> select * from nls_database_parameters where parameter= 'NLS_TERRITORY';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------

NLS_TERRITORY
AMERICA


SQL>


SQL> select * from nls_database_parameters where parameter= 'NLS_LANGUAGE';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------

NLS_LANGUAGE
AMERICAN

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647181 is a reply to message #647180] Thu, 21 January 2016 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You cannot change them.
Why do you want to do it? Database NLS_LANGUAGE and NLS_TERRITORY are not used.

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647183 is a reply to message #647181] Thu, 21 January 2016 06:24 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
because on my application server its nls_lang=Gujarati_india.Al32UTF8

so when i display my date its not show me month name only date and year numeric value its show me.

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647186 is a reply to message #647183] Thu, 21 January 2016 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Display format is not a fact of database, it is the fact of CLIENT.
So set your date format as you want in the CLIENT that is your application server.

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647198 is a reply to message #647186] Fri, 22 January 2016 05:22 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply so michel can you help me in this oracle application server
we have Installation Typ Business Intelligence and Forms , Version 10.1.2.0.2

application site registry level nls_lang=Gujarati_India.UTF8
and in default.env file also its NLS_LANG=GUJARATI_INDIA.AL32UTF8

Developer want dd-mm-rrrr format in our Application and at coding level they convert date format in to dd-mm-rrrr

When they creates form in form builder they provide format dd-mm-rrrr and at that time form builder directly connected to database form easy run
But after compilation they put compilation forms at application server level so when we run form through application server we got junk characters in month
Like

23-?????-2015

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647208 is a reply to message #647198] Fri, 22 January 2016 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
Developer want dd-mm-rrrr format in our Application


See your output above, which parameter gives the date format? Set it as you set NLS_LANG!

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647265 is a reply to message #647208] Mon, 25 January 2016 01:03 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply michel

my application site registry level nls_lang=Gujarati_India.UTF8
and in default.env file also its NLS_LANG=GUJARATI_INDIA.AL32UTF8

and i want output of date format in dd-mm-rrrr

Report message to a moderator

Re: if i import database using impdp full import its also change character set and nls_lang [message #647268 is a reply to message #647265] Mon, 25 January 2016 01:15 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Do as I said, twice.
And if you don't try to answer my questions, you will not succeed.

Report message to a moderator

Previous Topic: create flat file using stored procedure
Next Topic: SQLLDR - error loading xml column into xmltype column
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 16:55:49 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.