Oracle alter table modify column from NVARCHAR2 to VARCHAR2

oracle

Having a column FIRSTNAME NVARCHAR2(30) converted from VARCHAR2(30) with

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- worked!

I tried to restore the original. However

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- fails!

gives an Ora-01439 error, that the values must be empty.

First introducing a new column FIRSTNAME2 VARCHAR2(30), copying and renaming did not succeed, maybe due to failing Oracle experience?

Best Answer

Please try this, its working for me. Hope it helps you.

CREATE TABLE TEST_1
(
FIRSTNAME VARCHAR2(30)
);

INSERT INTO TEST_1(FIRSTNAME)
VALUES('NARENDRA');

COMMIT;

ALTER TABLE TEST_1
MODIFY FIRSTNAME  NVARCHAR2(30);

--Gives Error while modifying
/*ALTER TABLE TEST_1  MODIFY FIRSTNAME  VARCHAR2(30);8*/

ALTER TABLE TEST_1
ADD (FIRSTNAME2 VARCHAR2(30)
);


UPDATE TEST_1
SET FIRSTNAME2= substr(FIRSTNAME,1,30)

COMMIT; 

ALTER TABLE TEST_1
DROP COLUMN FIRSTNAME;  

ALTER TABLE TEST_1
RENAME COLUMN FIRSTNAME2 TO FIRSTNAME;

Related Solutions

Does an Oracle nvarchar2 take twice as much space as varchar2

Not exactly. Oracle accommodates both byte semantics and character semantics for VARCHAR. The default for character types is byte semantics; a column defined as VARCHAR2(10) holds 10 bytes, which might not be the same as 10 characters. The default for new columns is controlled by the parameter NLS_LENGTH_SEMANTICS.

But in a UTF-8 database, you can define a column as VARCHAR2(10 CHAR), which will hold 10 characters. Even if five of them are single-byte characters and five of them are multi-byte characters, all 10 will fit.

NCHAR and NVARCHAR2 always use character semantics. A column of NVARCHAR(20) will hold 20 characters, but it might take 60 bytes to hold all of them.

NVARCHAR2 is limited to 4000 bytes, which is 4000 single-byte characters, but only a third that many multi-byte characters. (Assuming a maximum of three bytes per character.)

Disk space is much less expensive than it used to be. But you still have to get data off the disk for it to be useful. More bytes, more time; more time, more expense. IME, cost of disk space doesn't matter only to people who don't have to pay for it.

More details in Oracle Database Concepts.

Why won’t Oracle alter the size of a column that is used for sub partitioning

The solution is dbms_redefinition package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.

What you have to do is:

Create the interim table withe correct column definition
Run redefinition
Drop the old table

Use dbms_redefinition in the following way:

-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);   
END;
/

-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE 
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE', 
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');

If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:

Execute privilege to DBMS_REDEFINITION
Create any table
Alter any table
Drop any table
Lock any table
Select any table

Tables with the following characteristics cannot be redefined online:

[9.0.1]Tables with no primary keys
Tables that have materialized view logs defined on them
[9i] Tables that are materialized view container tables and AQ tables
[10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
The overflow table of an IOT table
Tables with fine-grained access control (row-level security)
Tables with BFILE columns
Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
Tables in the SYS and SYSTEM schema
Temporary tables

Other restrictions:

A subset of rows in the table
Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
If new columns are being added with no column mappings, then they must not be declared NOT NULL until the redefinition is complete.
There cannot be any referential constraints between the table being redefined and the interim table.
Table redefinition cannot be done NOLOGGING.
[10g] For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties.
You cannot convert a nested table to a VARRAY.

Best Answer

Related Solutions

Does an Oracle nvarchar2 take twice as much space as varchar2

Why won’t Oracle alter the size of a column that is used for sub partitioning

Related Question