www.el-rincon.net ~ Ver tema

Rinconiano Registrado: 08 Jun 2005 Mensajes: 211 Ubicación: Madriz

Hola,

hasta hoy, cuando yo queria cambiar el charset de una base de datos oracle,
lo que hacia era exportar bbdd, recrearla con el nuevo charset y luego importar los datos.

Hoy he visto un script q te pregunta , es guachi. Lo meto aqui para futuras consultas, q al menos a mi, seguro q me hacen falta.

Saludetes,
pedrete.

set echo off
set verify off

rem The data dictionary table that records the database
rem character set is sys.props$
rem
rem SQL> describe sys.props$
rem Name Null? Type
rem ------------------------------- -------- ----
rem NAME NOT NULL VARCHAR2(30)
rem VALUE$ VARCHAR2(2000)
rem COMMENT$ VARCHAR2(2000)

rem For example:
rem
rem SQL> column c1 format a30
rem SQL> select name c1, value$ c1 from sys.props$;

rem C1 C1
rem ------------------------------ ------------------------------
rem DICT.BASE 2
rem NLS_LANGUAGE AMERICAN
rem NLS_TERRITORY AMERICA
rem NLS_CURRENCY $
rem NLS_ISO_CURRENCY AMERICA
rem NLS_NUMERIC_CHARACTERS .,
rem NLS_DATE_FORMAT DD-MON-YY
rem NLS_DATE_LANGUAGE AMERICAN
rem NLS_CHARACTERSET WE8DEC
rem NLS_SORT BINARY
rem GLOBAL_DB_NAME NLSV7.WORLD

rem NLS_CHARACTERSET can be changed by updating its value, for example:

rem update sys.props$
rem set value$ = 'WE8ISO8859P1'
rem Where name = 'NLS_CHARACTERSET';

rem The database has to be shutdown and restarted before the change
rem becomes effective.

rem It is very important to specify the character set name correctly.
rem IMPORTANT NOTE
rem =============
rem If NLS_CHARACTERSET is updated to an invalid value, it will not then
rem be possible to restart the database once it has been shutdown.
rem To recover, it will be necessary to re-create the database, since it
rem cannot be restarted to correct the invalid NLS_CHARACTERSET entry.

rem The character set name should be in uppercase.
rem The new value is not effective until the database has been shutdown and
rem restarted.
rem
rem A suggested procedure is as follows, and can be done by running this
rem script from SQL*Plus when logged into the SYSTEM account.
rem
rem USAGE : SQL> start ch_db.sql <character set>
rem
rem where <character set> is the desired database character set
rem

Prompt First check that the character set name is valid.

set echo on

select convert('a','&1','WE8MSWIN1252') from dual;

set echo off

prompt If this select statement returns error ORA-01482, then the
prompt specified character set name is not valid for this installation.
prompt Abort the procedure now with Control-c

prompt To continue, press return
accept ans CHAR

Prompt Check the current value of database character set.

column c1 format a30
select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

prompt To continue, press return

Prompt Update to new character set

update sys.props$
set value$ = upper('&1')
where name = 'NLS_CHARACTERSET';

set echo off

prompt To continue, press return
accept ans CHAR

Prompt Check the new value of database character set

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

Prompt If the value is updated as required, press return to continue and
Prompt then manually type COMMIT; to commit the change. Then shutdown and
Prompt restart the database.
Prompt
Prompt If the value is not updated as required, press return to continue and
Prompt than manually type ROLLBACK; to prevent the change.

prompt To continue, press return
accept ans CHAR

Yo todavía no uso oracle, pero se agradece Wink

y también que le des vida al foro.... que ya nos vale.

Rinconiano Registrado: 08 Jun 2005 Mensajes: 211 Ubicación: Madriz

Site Admin Registrado: 02 Jun 2005 Mensajes: 814 Ubicación: MAD-ES

algunos más que otros, ya lo sabes... pero todos en general lo somos, si Sad