- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- adding columns to many tables - ORACLE
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 06:26 AM
тАО03-03-2005 06:26 AM
We have a schema "srb" that has several tables in it that we need to add columns in all the tables in a schema.
example
schema SRB
TABLE A
TABLE B
TABLE C
...
TABLE ZZZ
COLUMNS TO ADD IN EACH
NAME VARCHAR2(100)
ADDRESS VARCHAR2(100)
PHONE NUMBER
I know I can add one at a time but, We have a lot of tables and need to do this without doing it manually.
Any suggestions?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 06:40 AM
тАО03-03-2005 06:40 AM
Re: adding columns to many tables - ORACLE
1. write select that generates "alter table" statements, something like:
select "alter table " || table_name ...
from dict
wher ....
spool it into some file and then execute it.
2. write pl/sql procedure that executes alter table.
But I prefer the first way.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 08:57 AM
тАО03-03-2005 08:57 AM
Re: adding columns to many tables - ORACLE
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 09:49 AM
тАО03-03-2005 09:49 AM
Re: adding columns to many tables - ORACLE
I've been an Oracle DBA for many years and believe, the first way that Alex told you is the fast and best way to do this.
Also, you can make some modifications in the script generated by select..., puting some prompt├В┬┤s or dbms_output.put_line... to say what your are doing, like, "alter table xxx add column xxx number;" and spooling all output of this script to verify that everything was done.
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 11:55 AM
тАО03-03-2005 11:55 AM
Re: adding columns to many tables - ORACLE
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 12:03 PM
тАО03-03-2005 12:03 PM
Re: adding columns to many tables - ORACLE
---------------------------------
select 'alter table ' || table_name from srb_flash
where table_name is not null;
2
'ALTERTABLE'||TABLE_NAME
--------------------------------------------------------------------------------
alter table CUSTOMER
alter table DEPT
alter table EMPLOYEE
alter table NEWTBL
alter table SRB_FLASH
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 12:07 PM
тАО03-03-2005 12:07 PM
SolutionYes creating a script with the alter table statements will be an easy solution. Here is how you do it.
SQL> set heading off
SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';
This statement will prepare alter table statements for all the tables in the schema SRB and the output will look like this :-
Alter table TABLE_A add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);
Befor you execute the above SQL statement spool to a alter_tab.sql file. Like this
SQL> set heading off
SQL>spool alter_tab.sql
SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';
SQL> spool off
SQL>set heading on
Now you will find the sql file alter_tab.sql created in your directory. Now to execute the created alter_tab.sql do this.
SQL>spool alter_tab_output.log
SQL>@alter_tab.sql
SQL>spool off
Now check the alter_tab_output.log file for any errors.
Indira A
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 12:18 PM
тАО03-03-2005 12:18 PM
Re: adding columns to many tables - ORACLE
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2005 12:20 PM
тАО03-03-2005 12:20 PM
Re: adding columns to many tables - ORACLE
Stephen