Column reduction in oracle

Question

Suppose I have a table with the following definition

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

All values ​​in this dummy.col1 are 7 digits long. Now I want to reduce the length of this column from 9 to 7 using the alter command. Oracle gives me an error so that the columns that need to be changed should be empty to reduce accuracy or scale . Has the meaning.

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

• I cannot delete values ​​in a column.
• I cannot copy values ​​from this column to another, since it has trillions of data.

Answer 1

The size of the column is not related to how the data is physically stored (it is a variable length)

eg. "23" in number (2) will take exactly the same space if it is stored in number (38)

This is just a limit on the maximum number that can be stored in a column, so you can simply add a restriction on the column:

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

if you want this to slightly change VALIDATE to NOVALIDATE , obviously this does not validate the existing data.

Answer 2

Kevin's answer is excellent.

The only way to do this is

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

Are you sure you can’t find an idle one weekend to complete this task?

Answer 3

Decision No. 1

My solution below keeps the original column order.
I found that this is important, especially if there are ready-made SQL expressions from there (middle tier, client tier) that point to your database, which are implicitly SELECTED.

i.e.

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

Here:

Create DDL for
1. A table containing the column that you intend to resize

2. All constraint constraints, indexes, control constraints, triggers that reference this table.
3. All foreign keys of other tables that reference the primary key of this table.

Make sure that each DDL address of the DDL object is autonomous, separate from the CREATE TABLE DDL.

You will 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 only the CREATE TABLE statement, change the name of the table and enter reduce the size of the column you want to change:

 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 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 source table.
Also, delete all foreign keys that reference the primary key tableName pkName.
Don’t worry, you saved the DDL so you can recreate them.
Please note that I delete indexes after copying data from tableName.
I am doing this because maybe one of the indexes will be used in the above SELECT so that the operation completes 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 source table.

 DROP TABLE tableName; 

Rename the new table.

 ALTER TABLE tableName_YYYYMMDD RENAME TO tableName; 

Restore all link objects from previously saved DDL statements.

 /* 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)); 

Decision number 2

Keep the order of the columns, but don't rebuild indexes without the unique use of PK-based that column2 can contain.

 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;