View is not required
Surya Prakash, December 03, 2002 - 4:24 am UTC
How can i alter a physical definition of database table.
Actually we create dynamic Insert statement where last first columns are reserved so only option left are to add new additional table in between (in my example after code and date and before actiondate)
December 03, 2002 - 7:19 am UTC
actually a view is required -- no idea why you say "view is not required".
what the heck is "where last first" -- last first??
Use the view, short of dropping and recreating the table from scratch that is your only option -- period.
Your method of this "dynamic insert" sounds very suspicious and fragile -- this simple exercise here is showing it's fragileness. You might rethink that logic.
But -- all else aside -- the view is required, unless you want to rebuild the table which you said "i don't want to rebuild the table".
"View is not required"
Tony Andrews, December 03, 2002 - 7:42 am UTC
> Actually we create dynamic Insert statement where last first columns are reserved
I take this to mean something like:
v_sql := 'INSERT INTO t VALUES ('
|| v_code || ',' || v_date
|| <<other values go here>>
|| ',' || v_action_date ')';
... in which case a solution would be to correct the insert statement to:
v_sql := 'INSERT INTO t( code, date'
|| <<other column names go here>>
|| ',action_date) '
|| VALUES ('
|| v_code || ',' || v_date
|| <<other values go here>>
|| ',' || v_action_date ')';
Or maybe this is bad third-party code and so cannot be fixed?
She, December 03, 2002 - 10:04 am UTC
How would I modify the length of a varchar column in a table, without dropping the table...
December 03, 2002 - 10:27 am UTC
you can make it BIGGER
alter table t modify c varchar2(BIGGER)
you cannot make it "smaller", but you can add a check constraint to make it so that only the smaller field can be put into there.
Andrew, December 03, 2002 - 12:56 pm UTC
Well I guess it IS possible to effectively juggle column orders by adding tmp columns, populating them, and finally dropping the unwanted ones. Similar for re-sizing a column smaller (move the data out, resize, move it back again). By the time you've worked out all the DDL and DML, it would have been quicker to just rebuild though.
Being a fan of having a clean schema without "patches" to work around limitations, I'd personally prefer rebuilding the tables.
December 03, 2002 - 1:30 pm UTC
views man -- this is what views are all about.
it would take about 5 seconds with a view -- no muss, no fuss.
Never will I understand the FUD surrounding the use of views.
An easy way to add a column wherever you want !! just a logic
Kumar, December 03, 2002 - 1:24 pm UTC
If your table is not a huge then I would use this below logic, this helps if your database is 8i and latter only. See below, what is your table structure call this table as t1
t1 ( Your table existing structure)
Now you need to add column "newcolumn" after "status". See the math below to achieve this:-
alter table t1 add(newcolumn <datatype> ,actiondate_TEMP <whatever datatype>)
update t1 set actiondate_TEMP = actiondate
commit ( no need actually this commit)
alter table t1 drop(actiondate)
alter table t1 add(actiondate <whatever datatype>)
update t1 set actiondate = actiodate_TEMP
alter table t1 drop(actiodate_TEMP)
now you run the command desc t1 you can see the structure of your table as :-
Note:- I mentioned these update statements to take care the data if any exists.
December 03, 2002 - 1:39 pm UTC
You win my first ever Rube Goldberg Award. Outstanding...
"The purpose of a Rube Goldberg Machine is to build the most complicated machine possible to perform a simple everyday task."
Surya Prakash, December 03, 2002 - 2:02 pm UTC
Thanks Kumar. I got my answer.
December 03, 2002 - 2:22 pm UTC
you are kidding -- you won't recreate the table -- but you'll use the "curly shuffle" to totally scramble it and make it as bad as possible.
wow, amazing. amazing what hoops people will go through when the answer is staring them -- literally staring them -- in the face. totally amazing.
you'll write, rewrite, write, rewrite and totally scramble the table but you won't use a view, you won't just recreate the table as select -- both of which (in order) are infinitely superior options. Ok.....
An option is an option but no measure like superior n inferior -- choice is yours
kumar, December 03, 2002 - 2:51 pm UTC
Well I did not present this option to achieve any award nor to show how sperior my option. What I mentioned is another option ( that is why I wrote another way) and those may fit or not fit based on the criteria. For eg:- How about sometimes your create re create may not be the choices for small kind of environments like users are not allowed to do create, drop rename such previleges and the table had some foreign key constraints on other keys. However they may be allowed to alter table then this could be an easy option.
I am sure to agree with your the bullet proof option that you have presented. I am neither interested to highlight mine nor inferior the other methods of options. I Just spread or throw another way in the thread.
Oracle 8i did not had undo tablespace even then many use 8i as their production. It's an option whether we can use or not? choice is yours. I know this time perhaps you are ready to throw another award to me.
December 03, 2002 - 2:58 pm UTC
I think Andrew, who put the comment right above yours -- before you put it up here, hit the proverbial nail on the head.
possible = y
advisible = n
Reducing the varchar2 datatype column size
Pablo Rovedo, December 03, 2002 - 4:06 pm UTC
just a comment regarding modifying varchar2 columns ...
from 9i SQL Reference:
"You can reduce the size of a column s datatype as long as the change does not require data to be modified. Oracle scans existing data and returns an error if data exists that exceeds the new length limit."
December 03, 2002 - 4:28 pm UTC
cool -- stealth feature!! that one totally slipped by me, thanks so much:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x varchar2(25) );
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'x' );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t modify x varchar2(5);
alter table t modify x varchar2(5)
ERROR at line 1:
ORA-01441: column to be modified must be empty to decrease column length
ops$tkyte@ORA9I.WORLD> create table t ( x varchar2(25) );
ops$tkyte@ORA9I.WORLD> insert into t values ( 'x' );
1 row created.
ops$tkyte@ORA9I.WORLD> alter table t modify x varchar2(5);
adding a column
Jayant Kumar, January 09, 2005 - 10:45 pm UTC
it really helped me. Thanks.
changing column's datatype
atul, May 19, 2005 - 4:27 am UTC
Could we change columns datatype in 9i,without renaming etc.. of a table with data
ie from varchar2 to number
May 19, 2005 - 8:02 am UTC
sure, IF the column is empty.
else no. it would take a
a) add new column
b) update new using old column
c) drop old column
d) rename new to old
Add column to Partition Table - Oracle 9i
kpanchan, December 19, 2006 - 2:17 pm UTC
We have partitioned table. We need add new column to this table.
Is it possible in Oracle9i Release 220.127.116.11.0?
Solution was given by taking backup of the current table and create new table with additional column.
Copy data from old table to new table.
Reason for this approach was told, it is not possible to add new column to partition table in Oracle 9i.
could you please give share, do I have any other option?
Thanks very much for your time to answer.
December 19, 2006 - 3:50 pm UTC
ops$tkyte%ORA9IR2> CREATE TABLE t
3 dt date,
4 x int,
5 y varchar2(30)
7 PARTITION BY RANGE (dt)
8 -- subpartition by hash(x)
10 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
11 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
12 PARTITION junk VALUES LESS THAN (MAXVALUE)
ops$tkyte%ORA9IR2> alter table t add num number;
works in 8i too - always good to "try things"
Please ignore my earlier post.
kpanchan, December 19, 2006 - 2:46 pm UTC
How Alter table modify works?
Purvesh, October 12, 2010 - 6:20 am UTC
I want to understand how and Alter Table ABC Modify column Default 'N/A' works?
When we execute it, is it that the column is dropped and re-build? Or is it that it only alters the width of columns and adds the newly specified Default parameters? Do, correct my understanding. :)
In which case of Alter Table, would a column be dropped and re-build (Other than for the DROP option)?
October 12, 2010 - 8:03 am UTC
Modifying a column to have a default will only affect rows that come AFTER the alter - it doesn't touch existing data.
therefore, the table undergoes "nothing", no drop, no rebuild, nothing.
ops$tkyte%ORA10GR2> create table t ( x varchar2(30), y int );
ops$tkyte%ORA10GR2> insert into t (y) values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> insert into t (x,y) values ( 'hello world', 2 );
1 row created.
ops$tkyte%ORA10GR2> alter table t modify x default 'N/A';
ops$tkyte%ORA10GR2> insert into t (y) values ( 3 );
1 row created.
ops$tkyte%ORA10GR2> insert into t (x,y) values ( 'hello world', 4 );
1 row created.
ops$tkyte%ORA10GR2> select * from t;
hello world 2
hello world 4
How to copy a table to another schema
A reader, October 12, 2010 - 8:48 am UTC
I have two schema. Now I have to copy the structure of the table from Schema A to Schema B. The table may or may not be present in Schema B. There can be situations like:
1. Table is present in Schema B but not in Schema A
2. Table is present in both schemas but differ in column structure.
3. Same table with same columns is present in both schema.
I have to copy these without taking the database down. Can we do it through TOAD? I saw there is a option to CREATE TABLE in ANOTHER SCHEMA but I am not sure about it.
It would be great if you suggest me the ways to copy the tables with/without data in live database..
October 12, 2010 - 9:19 am UTC
ask the people that make toad questions about toad please. I do not use toad myself.
I don't know how to copy a table from A to B if (1) is true.
I don't know what it means to copy a table form A to B is (2) is true - what would you expect to have happen???
(3) is trivial, log into B and issue "delete from table; insert into table select * from a.table;"
Abhisek, October 12, 2010 - 12:38 pm UTC
I am sorry if I was unclear. I want to simply copy one schema to another like we do with exp/imp utility.
The three were conditions about existence of objects. I am not concerned for data. I need Objects in Schema B are same as Schema B.
I could use COPY command from sqlplus to copy data.But I am not sure what happens with this COPY command if you have tables in both schema but different strucure,.
To make it simple, lets consider a prod alike schema needs to be created.
October 12, 2010 - 12:57 pm UTC
how can you call
"2. Table is present in both schemas but differ in column structure."
a simple copy ??? the structures are different - that is hugely complex all of a sudden.
... I need Objects in Schema B are same as Schema B. ...
er? not sure what you mean.
... But I am not sure what
happens with this COPY command if you have tables in both schema but different
laughing out loud - I'm not sure what should happen in your mind when this occurs. How do you do that? What is the process.
... To make it simple, lets consider a prod alike schema needs to be created.
a prod alike? or do you mean a "prod like schema"
I would say that data pump or export import would be the path of least resistance - either one can remap schema names - but you would start with an EMPTY schema.
This is not very clear at all - especially when you throw in #2.
If you just want a copy of a schema - table by table, column by column - exp/imp is the way to go using fromuser touser, or datapump - depending on your version.
ABhisek, October 12, 2010 - 1:41 pm UTC
Glad I was a reason for your laugh for one sec.. ;p
OK let me make my stand clear. I want to copy all the objects from Schema B to Schema A. I do not have empty schema. There are objects in Schema A but still not 100 % similar to schema B.
May be entire table is missing or may be some columns are missing. So, I have to make these two schemas similar.. May not be in terms of data but object definitions.
Now I hope I am clear. Thanks for the help.
October 12, 2010 - 2:02 pm UTC
you want to have a set of creates and alters created for you to make schema "A" look like schema "B"
(you do not want to copy all of the objects by the way, that would imply you wanted to COPY the objects - not synchronize the physical schemas of two things)
I hate this - you would, in a proper environment, have the "patch scripts" you would pull out of source code control that take your schema from version 1 to version 2. Having to "diff" two schemas to make them the same means "someone missed the boat big time".
sql developer has such a capability: http://docs.oracle.com/docs/cd/E15846_01/doc.21/e15222/dialogs.htm#sthref599
ABhisek, October 12, 2010 - 5:03 pm UTC
SO finally we are on same page.. I have all the source code in a subversion but I am not sure if we have to disable all the contraints before I create the table to maintain the primary or foriegn key relation..
I am sorry but this is the first time I am trying this type of operation.. So I need some understanding before I do it.
Please suggest the best way considering I have all codes in Subversion.
Thanks a lot for the help.
October 12, 2010 - 5:33 pm UTC
Tell me - how did production and this environment get to "differ" - where are THOSE scripts - those scripts that changed the one environment should have been
a) checked in
c) available for you to now check out and run yourself.
Same query I have
A reader, October 12, 2010 - 5:42 pm UTC
To add to the previous question, I have seen a Server 1 which was supposed to be for initial development work and then saved to subversion. But people started in Server 2 and now after 1+ years I see a huge mismatch.
So now how can we make a schema to be compliant with what is present in repository. Please suggest.
October 12, 2010 - 6:40 pm UTC
see my answer above - I provided a name (sql developer) and a link (to feature of sql developer).
Alter table add column
Abhishek Das, November 07, 2012 - 12:12 am UTC
Why does oracle doesn't provide this feature of adding column in between two columns?
We have this feature in other database systems with commands like "alter table t1 add new_column number after/before a1".
I have read your posts and know we can achieve this using view, but out of curiosity I just wanted to know what is the reason that Oracle doesn't provide this functionality.
Also Can we still use this option of creating view if we have triggers on the table?
November 07, 2012 - 7:44 am UTC
triggers have no impact on views
we just don't have it - that is all, no reason. to me it isn't a big deal because select * is something you only have in demo's anyway, the order of columns in the table doesn't even guarantee what select * will resolve to, it is just an empirical observation that it seems to be the order of columns in the create....
there are cases
Sokrates, November 07, 2012 - 8:07 am UTC
November 07, 2012 - 11:30 am UTC
ah, but that is apples and banana's
the before/after 'trick' is purely a metadata thing, it doesn't really put the data before/after - just makes that column "appear" there.
you have to rebuild the entire table to put a column "in the middle" of a table - and we can do that, dbms_redefinition will do it, create table as select will do that.
there is a big difference between the syntactic sugar that puts column X between A and B in metadata
and actually having column X between A and B physically in the database block.
alter table add col
Onkar, November 14, 2012 - 10:46 pm UTC
One of the developer ask me this:
I wanted to add a column in an existing table using select stmt. What I mean is:
SQL:SCOTT@tp11g>create table test as select ename as name ,job from emp;
Now I want to add sal column with data from emp to test using select stmt. I doubt we can do it but not sure. is it possible?
November 19, 2012 - 8:50 am UTC
it is not possible, no