shrinking a column in oracle

Lets say i have a table with the following definition

create table dummy (col1 number(9) not null)

All the values开发者_Go百科 in this dummy.col1 are 7 digit long. Now i want to reduce the length of this column from 9 - 7 using alter command. Oracle gives me error that column to be modified must be empty to decrease precision or scale. Makes sense.

I want to ask is there any work around to reduce the column size?

• I can't delete the values in the column.
• I can't copy values from this column to another since it has trillions of data.

The column size has no relationship to how the data is physically stored (they are variable length)

e.g. '23' in a number(2) will take exactly the same space if stored in a number(38)

It is purely a constraint on the maximum number that can be stored in the column therefore you could just add a constraint on the column:

ALTER TABLE dummy ADD 
CONSTRAINT c1
CHECK (col1 < 9999999)
ENABLE
VALIDATE;

if you want it to go a little quicker change VALIDATE to NOVALIDATE obviously this will not check the validity of the existing data.

Kevin's answer is excellent.

The only other way to do it is to

rename the existing column,
create a new column with the old name and the new size,
issue an update statement to populate the new field (which you said you cannot do)
and then drop the renamed column.

Are you sure you cannot find some downtime one weekend to perform this task ?

Solution #1

My solution below keeps the original column order.
I found that to be important, especially if there are canned SQL statements out
there (middle tier, client tier) that point back to your database that do implicit
SELECTs.

i.e.

SELECT * 
FROM tableName
WHERE ...; 

INSERT INTO copyTableName(column1,column2,column3,...)  
   SELECT * 
   FROM tableName
   WHERE ...;

Here goes:

Generate the DDLs for
1. The table containing the column you intend to resize
2. All the relationship constraints, indexes, check constraints, triggers that reference that table.
3. All the foreign keys of other tables that reference the primary key of this table.

Make sure each table-referencing-object DDL is stand-alone, separate from the
CREATE TABLE DDL.

You'll have something like

/* 1. The table containing the column you intend to resize */
CREATE TABLE tableName
(
  column1 TYPE(size) [DEFAULT value] [NOT] NULL,
  column2 TYPE(size) [DEFAULT value] [NOT] NULL,
  column3 TYPE(size) [DEFAULT value] [NOT] NULL,
  ...
)
TABLESPACE tsName
[OPTIONS];

/* 2. All the relationship constraints, indexes, check constraints, triggers that reference that table. */
CREATE INDEX indexName ON tableName
(column1)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE INDEX compositeIndexName ON tableName
(column1,column2,...)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE UNIQUE INDEX pkName ON tableName
(column2)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

ALTER TABLE tableName ADD (
  CHECK (column4 IS NOT NULL));

ALTER TABLE tableName ADD (
  CONSTRAINT pkName
 PRIMARY KEY
 (column2)
    USING INDEX 
    TABLESPACE INDX);

ALTER TABLE tableName ADD (
  CONSTRAINT fkName
 FOREIGN KEY (column2) 
 REFERENCES otherTable (column2));

/* 3. All the foreign keys of other tables that reference the primary key of this table. */
ALTER TABLE otherTableName ADD (
  CONSTRAINT otherTableFkName
 FOREIGN KEY (otherTableColumn2) 
 REFERENCES tableName (column1));

Copy out just the CREATE TABLE statement, change the table name and
reduce the size of the column you wish to modify:

CREATE TABLE tableName_YYYYMMDD
(
  column1 TYPE(size)         [DEFAULT value] [NOT] NULL,
  column2 TYPE(reducedSize)  [DEFAULT value] [NOT] NULL,
  column3 TYPE(size)         [DEFAULT value] [NOT] NULL,
  ...
)
TABLESPACE tsName
[OPTIONS];

Insert the data from tableName into tableName_YYYYMMDD:

INSERT /* APPEND */ INTO tableName_YYYYMMDD(
  column1 ,
  column2 ,
  column3 ,
  ...     )
    SELECT
      column1 ,
      column2 ,
      column3 ,
      ...     
    FROM tableName;
COMMIT;

Drop all objects referencing the original table.
Also, drop all foreign keys that reference the tableName primary key pkName.
Don't worry, you've saved the DDL so you'll be able to recreate them.
Notice that I drop indexes after copying the data out of tableName.
I do this because perhaps one of the indexes will be used in the
above SELECT so that operation will complete faster.

DROP INDEX indexName                                        ;
DROP INDEX compositeIndexName                               ;
DROP UNIQUE INDEX pkName                                    ;
ALTER TABLE tableName DROP CONSTRAINT pkName                ;
ALTER TABLE tableName DROP CONSTRAINT fkName                ;
ALTER TABLE otherTableName DROP CONSTRAINT otherTableFkName ;

Drop the original table.

DROP TABLE tableName;

Rename the new table.

ALTER TABLE tableName_YYYYMMDD RENAME TO tableName;

Recreate all referencing objects from the DDL statements you saved before.

/* 2. All the relationship constraints, indexes, check constraints, triggers that reference that table. */
CREATE INDEX indexName ON tableName
(column1)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE INDEX compositeIndexName ON tableName
(column1,column2,...)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

CREATE UNIQUE INDEX pkName ON tableName
(column2)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

ALTER TABLE tableName ADD (
  CHECK (column4 IS NOT NULL));

ALTER TABLE tableName ADD (
  CONSTRAINT pkName
 PRIMARY KEY
 (column2)
    USING INDEX 
    TABLESPACE INDX);

ALTER TABLE tableName ADD (
  CONSTRAINT fkName
 FOREIGN KEY (column2) 
 REFERENCES otherTable (column2));

/* 3. All the foreign keys of other tables that reference the primary key of this table. */
ALTER TABLE otherTableName ADD (
  CONSTRAINT otherTableFkName
 FOREIGN KEY (otherTableColumn2) 
 REFERENCES tableName (column1));

Solution #2

Keep the column order but do not rebuild non-unique-used-by-PK indexes that might contain column2.

ALTER TABLE tableName ADD (column2Copy TYPE(reducedSize));

UPDATE      tableName SET column2Copy = column2;

ALTER TABLE tableName MODIFY (column2 TYPE(size) NULL);

ALTER TABLE tableName DROP CONSTRAINT pkName;

DROP INDEX  pkName;

UPDATE      tableName SET column2 = null;

ALTER TABLE tableName MODIFY (column2 TYPE(reducedSize));

UPDATE      tableName SET column2 = column2Copy;

ALTER TABLE tableName DROP COLUMN column2Copy;

CREATE UNIQUE INDEX pkName ON tableName
(column2)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

ALTER TABLE tableName ADD (
  CONSTRAINT pkName
 PRIMARY KEY
 (column2)
    USING INDEX 
    TABLESPACE INDX);

COMMIT;