Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mansi.

Asked: April 20, 2017 - 1:25 pm UTC

Last updated: April 21, 2017 - 2:53 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Hi Team,

I have had gone through one question in interview,I could not find answer to that. If you can help me with that.

Let say,there is one table employee with millions of record and we want to increase the column size address_col(10) to address_col(20). How can I take backup of employee table,if below query is running since 4-5 hours and still running.

create employee_backup
as
select * from employee

Is there any way we can take backup of perticuler column. Or what approach is suggested.


and Connor said...

"Let say,there is one table employee with millions of record and we want to increase the column size address_col(10) to address_col(20)"

Doing this command will take *no time*, because no data needs to change.

SQL> create table t ( address varchar2(10));

Table created.

SQL> insert /*+ APPEND */ into t
  2  select rownum
  3  from ( select 1 from dual connect by level <= 1000 ),
  4  ( select 1 from dual connect by level <= 10000 )
  5  /

10000000 rows created.

SQL> set timing on
SQL> alter table t modify address varchar2(50);

Table altered.

Elapsed: 00:00:00.05


but anyway. If you need to backup a column, you can take a copy of the primary key columns, and the column needed, eg

create table backup_my_table
as select pk_col1, pk_col2, my_column
from my_table;

You need the primary key columns so you could map the data back to the original table if you needed to.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks so much

Mansi Raval, April 21, 2017 - 10:17 am UTC

Thank you Connor.

This was use full.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions