Is there a way to add a new column to a table while the table isbeing used for queries???

#1

I require the ability to add a new clob field to a database table
object on Oracle 10g SQL server.
Can I just do an
alter table MyTable add column mynewfield clob;

How do I insure that users can continue to make queries on the
table???

I would be greatful if someone could point me to some documentation on
this subject of high availability in a huge partitioned data warehouse
type environment.

Thank you in advance!

#2

On Jan 12, 1:01*pm, jack_go... (AT) yahoo (DOT) com wrote:

Quote:

I require the ability to add a new clob field to a database table
object on Oracle 10g SQL server.
Can I just do an
alter table *MyTable add column mynewfield clob;

How do I insure that users can continue to make queries on the
table???

I would be greatful if someone could point me to some documentation on
this subject of high availability in a huge partitioned data warehouse
type environment.

Thank you in advance!

The users can continue to query the table but have you really given
thought as to what you might break by adding this column 'on the
fly'? You mention this is a DW environment which can contain
materialized views, some of which may be defined by the dreaded
'select *' queries. Adding a column to a table without first checking
what other objects reference that table (MVs, procedures, functions,
packages, views) could cause problems later; it's not just the ad-hoc
queries you need to concern yourself with.

David Fitzjarrell

#3

On Jan 12, 12:01*pm, jack_go... (AT) yahoo (DOT) com wrote:

Quote:

I require the ability to add a new clob field to a database table
object on Oracle 10g SQL server.
Can I just do an
alter table *MyTable add column mynewfield clob;

How do I insure that users can continue to make queries on the
table???

I would be greatful if someone could point me to some documentation on
this subject of high availability in a huge partitioned data warehouse
type environment.

Thank you in advance!

In addition to what David said, see
http://orainternals.wordpress.com/20...llowed-by-ddl/

jg
--
@home.com is bogus.
http://tapcis.master.com/texis/maste...tabases/Oracle

#4

jack_gold2 (AT) yahoo (DOT) com, 12.01.2012 21:01:

Quote:

I require the ability to add a new clob field to a database table
object on Oracle 10g SQL server.
Can I just do an
alter table MyTable add column mynewfield clob;

How do I insure that users can continue to make queries on the
table???

I would be greatful if someone could point me to some documentation on
this subject of high availability in a huge partitioned data warehouse
type environment.

I think that's what dbms_redefinition is supposed to solve.

http://docs.oracle.com/cd/B19306_01/...htm#ADMIN01514
http://docs.oracle.com/cd/B19306_01/...8/d_redefi.htm

#5

On Jan 12, 11:59*pm, Harry Tuttle <OTPXDAJCS... (AT) spammotel (DOT) com> wrote:

Quote:

jack_go... (AT) yahoo (DOT) com, 12.01.2012 21:01:

I require the ability to add a new clob field to a database table
object on Oracle 10g SQL server.
Can I just do an
alter table *MyTable add column mynewfield clob;

How do I insure that users can continue to make queries on the
table???

I would be greatful if someone could point me to some documentation on
this subject of high availability in a huge partitioned data warehouse
type environment.

I think that's what dbms_redefinition is supposed to solve.

http://docs.oracle.com/cd/B19306_01/...8/d_redefi.htm

Yes, except for the more than double space required makes it a bit
much for a huge dw environment. Thanks for pointing it out, it should
be considered anyways, because "it depends." The OP did not specify
the Oracle version sufficiently.

jg
--
@home.com is bogus.
http://www.utsandiego.com/news/2012/...ay-see-stores/

#6

If there are no long running queries then the OP should be able to issue the alter table to add the lob column while the table is in use without having to resort to running dbms_redefinition.

I would be more worried about developers who coded select * but then again under our rules select * is not allowed and any resulting error would be a developer issue. After all the request to modify the table structure should have come from a developer and been approved by the developer management chain.

HTH -- Mark D Powell --

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Is there a way to add a new column to a table while the table isbeing used for queries???

comp.databases.oracle.misc comp.databases.oracle.misc

Is there a way to add a new column to a table while the table isbeing used for queries??? - 01-12-2012 , 02:01 PM

Re: Is there a way to add a new column to a table while the table isbeing used for queries??? - 01-12-2012 , 02:22 PM

Re: Is there a way to add a new column to a table while the table isbeing used for queries??? - 01-12-2012 , 04:17 PM

Re: Is there a way to add a new column to a table while the tableis being used for queries??? - 01-13-2012 , 01:59 AM

Re: Is there a way to add a new column to a table while the table isbeing used for queries??? - 01-13-2012 , 11:35 AM

Re: Is there a way to add a new column to a table while the table isbeing used for queries??? - 01-13-2012 , 11:41 AM