-
Hello all,
Ok i have a little problem (problem to me). I want to remove a column from a table without removing data from any other columns.
This is little urgent, so any help will be apriciated.
Thanks
-
Since you didn't specify your version, I'll assume your're on Solaris 2.8 and 8.1.7.
You can drop a column by the ALTER TABLE command. See syntax for details.
Jeff Hunter
-
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
In 8.1.7 Oracle RECOMMENDS making the column UNUSABLE first before the DROP. Remember to have a lot of ROLLBACK SEGMENT space available for the DROP. There is a CHECKPOINT option in the DROP syntax, more appropratle would've been a COMMIT=number option. I dropped a column from one table.... 6 GB of rollback later... it was gone.
OCP 8i, 9i DBA
Brisbane Australia
-
Prior to 8i
Create table as select.
Drop original table.
Rename the new table to original.
Create indexes and constraints.
Sanjay
-
Originally posted by grjohnson
In 8.1.7 Oracle RECOMMENDS making the column UNUSABLE first before the DROP. Remember to have a lot of ROLLBACK SEGMENT space available for the DROP. There is a CHECKPOINT option in the DROP syntax, more appropratle would've been a COMMIT=number option. I dropped a column from one table.... 6 GB of rollback later... it was gone.
why do we require a lot of rollback space to issue a drop command.
This operation cannot be rollbacked ????
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Ronnie, I know, but every record is changing therefore redo will be generated for the 'before view' of the row. Even if it can't be recovered. As far as I'm aware, you can't perfom a NOLOGGING on a DROP COLUMN either... which is a right pain in the butt.
OCP 8i, 9i DBA
Brisbane Australia
-
Originally posted by grjohnson
Ronnie, I know, but every record is changing therefore redo will be generated for the 'before view' of the row. Even if it can't be recovered. As far as I'm aware, you can't perfom a NOLOGGING on a DROP COLUMN either... which is a right pain in the butt.
I still believe that Rollback wont be generated as there wont be a before view at all in this case.
Others please correct me if I am wrong.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
-
SQL> create table testaaa ( a number,
2 b number);
Table created.
SQL> alter table testaaa drop column b;
Table altered.
SQL> desc testaaa
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
SQL> rollback;
Rollback complete.
SQL> desc testaaa
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|