| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: alter table move long
Some time ago, I posted a request for a script to simulate an "ALTER TABLE
MOVE" on a table with a LONG column.
I have included below a script that I wrote to perform such a function. It will generate *yet another* DDL file (script) with the complete DDL required to drop & rebuild the table and import the rows (the script itself makes no invasive changes to the DB). Export and import are used with the INDEXFILE option to obtain the table and index DDL, and then sqlplus is used to extract:
The script then attaches an import with the SHOW=Y option, commented out, as a reference. The Oracle 7 version of INDEXFILE does not include constraints, so you will have to dig them out of the SHOW=Y comments.
The DDL file produced by the script does include a DROP TABLE, so any foreign key constraints involving the target table must be disabled (I have not included logic for this procedure).
(ASCII) Tabs are used in a few places in the script; it is important to preserve them and the strings using tabstops have been noted (be careful if you cut and paste the script through tools that convert tabs to spaces).
Notes:
Please let me know if any of you make improvements to the script, although I am not interested in changes which break compatibility with Oracle 7.
/ Charles J. Fisher | "A fanatic is one who can't change his / / cfisher_at_rhadmin.org | mind and won't change the subject." / / http://rhadmin.org | -- Winston Churchill / ---------------------------------------------------------------------------
ATAB.sh:
if [ $# != 3 ]
then
print "Usage: $0 table user passord" print "(creates 'TABLE.sql' script to drop, create & import table)" exit
TAB=$(echo ${1} | tr '[a-z]' '[A-Z]')
UID=$(echo ${2} | tr '[a-z]' '[A-Z]')
PWD=$(echo ${3} | tr '[a-z]' '[A-Z]')
exp userid=${UID}/${PWD} file=${TAB}-schema.dmp \
tables=${TAB} rows=n grants=y indexes=y constraints=y
imp userid=${UID}/${PWD} file=${TAB}-schema.dmp \
full=y indexfile=${TAB}.sql
# --Perform various processing on the INDEXFILE script. gawk 'BEGIN {
print "lock table '${TAB}' in exclusive mode;"
print "!exp userid='${UID}'/'${PWD}' file='${TAB}'-data.dmp " \
"tables='${TAB}' rows=y grants=n indexes=n constraints=n " \
"compress=n feedback=100000"
print "drop table '${TAB}';"
finalimp = 0
if($0 ~ /^CONNECT/) next
if($0 ~ /^REM/) sub(/^REM/,"");
if($0 ~ /CREATE TABLE/ && finalimp == 0) finalimp = 1;
print
if(finalimp == 1 && $0 ~ /;/)
{
print "!imp userid='$UID'/'$PWD' file='${TAB}'-data.dmp " \
"tables='${TAB}' ignore=y rows=y grants=n indexes=n " \
"feedback=100000"
finalimp = 2
}
mv ${TAB}.sql.new ${TAB}.sql
# --Extract the grants.
print "connect ${UID}/${PWD}
set pages 0 lin 32767 feedback off verify off heading off
select 'grant '||PRIVILEGE||' on '||TABLE_NAME||' to '||
GRANTEE||' with grant option;'
from dba_tab_privs
where owner='${UID}' and table_name='${TAB}'
and GRANTABLE='YES';
select 'grant '||PRIVILEGE||' on '||TABLE_NAME||' to '||
GRANTEE||';'
from dba_tab_privs
where owner='${UID}' and table_name='${TAB}'
and GRANTABLE='NO';
# --Extract the triggers.
print "connect ${UID}/${PWD}
set lin 32767 feedback off serveroutput on size 1000000
DECLARE
mydesc user_triggers.description%TYPE;
mywhen user_triggers.when_clause%TYPE;
l_cursor integer default dbms_sql.open_cursor;
l_long_val varchar2(2000);
l_long_len number;
l_curpos number;
str1 integer;
str2 integer;
BEGIN
dbms_sql.parse(l_cursor,
'select description, when_clause, trigger_body
from user_triggers
where table_name = ''${TAB}''',
dbms_sql.native);
dbms_sql.define_column(l_cursor, 1, mydesc, 2000);
dbms_sql.define_column(l_cursor, 2, mywhen, 2000);
dbms_sql.define_column_long(l_cursor, 3);
str1 := dbms_sql.execute(l_cursor);
while dbms_sql.fetch_rows(l_cursor) > 0 loop
dbms_sql.column_value(l_cursor, 1, mydesc);
dbms_output.put('create or replace TrIgGer');
l_long_val := mydesc;
l_long_len := length(mydesc);
str1 := 1;
str2 := instr(l_long_val, chr(10), str1);
while str2 != 0 loop
-- NOTE: the quoted string below is a single tab
dbms_output.put_line(' '||
substr(l_long_val, str1, str2 - str1));
str1 := str2 + 1;
str2 := instr(l_long_val, chr(10), str1);
end loop;
dbms_output.put(substr(l_long_val, str1));
dbms_sql.column_value(l_cursor, 2, mywhen);
if mywhen is not null then
dbms_output.put('WHEN (');
l_long_val := mywhen;
l_long_len := length(mywhen);
str1 := 1;
str2 := instr(l_long_val, chr(10), str1);
while str2 != 0 loop
-- NOTE: the quoted string below is a single tab
dbms_output.put_line(' '||
substr(l_long_val, str1, str2 - str1));
str1 := str2 + 1;
str2 := instr(l_long_val, chr(10), str1);
end loop;
dbms_output.put(substr(l_long_val, str1));
-- The following put_line shouldn't be necessary...?
dbms_output.put_line('');
dbms_output.put_line(')');
end if;
l_curpos := 0;
loop
dbms_sql.column_value_long(l_cursor, 3, 250,
l_curpos, l_long_val, l_long_len);
l_curpos := l_curpos + l_long_len;
str1 := 1;
str2 := instr(l_long_val, chr(10), str1);
while str2 != 0 loop
-- NOTE: the quoted string below is a single tab
dbms_output.put_line(' '||
substr(l_long_val, str1, str2 - str1));
str1 := str2 + 1;
str2 := instr(l_long_val, chr(10), str1);
end loop;
-- NOTE: the quoted string below is a single tab
dbms_output.put(' '||substr(l_long_val, str1));
exit when l_long_len = 0;
end loop;
dbms_output.put_line('');
dbms_output.put_line('/');
end loop;
dbms_sql.close_cursor(l_cursor);
| sqlplus -silent /nolog \
| gawk '{
if($0 ~ /^Connected\.$/) next
if($0 ~ /^\t/) sub(/^\t/,"")
if($0 ~ /TrIgGer\t/) sub(/TrIgGer\t/,"trigger ")
print
}
# --Extract the comments.
print "connect ${UID}/${PWD}
set pages 0 lin 5000 feedback off verify off heading off
select 'comment on column '||TABLE_NAME||'.'||COLUMN_NAME||' iS #'||
COMMENTS||'#;'
from dba_col_comments
where owner='${UID}' and table_name='${TAB}';" \
| sqlplus -silent /nolog \
| gawk '
{
if($0 ~ /^Connected\.$/ || $0 ~ /^$/ || $0 ~ /##;$/) next
if($0 ~ /'"'"'/) gsub(/'"'"'/,"'"''"'")
if($0 ~ /iS #/) sub(/iS #/,"is '"'"'")
if($0 ~ /#;$/) sub(/#;$/,"'"'"';")
print
# --Attach an "export show=y" for reference (Oracle 7 needs constraints here). imp userid=${UID}/${PWD} file=${TAB}-schema.dmp show=y full=y 2>&1 \
| gawk '!/^$/ {sub(/^ "/,""); sub(/"$/,""); print "--"$0}' >> ${TAB}.sql
CFTEST.sql:
CREATE TABLE "SIEBEL_AMP"."CFTEST" ("VAR1" NUMBER, "VAR2" LONG,
"DATE_LAST_CHNGD" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
LOGGING STORAGE(INITIAL 1064960 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "SIEBEL_AMP" ;
!imp userid=SIEBEL_AMP/xxx file=CFTEST-data.dmp tables=CFTEST ignore=y rows=y grants=n indexes=n feedback=100000
ALTER TABLE "SIEBEL_AMP"."CFTEST" ADD PRIMARY KEY ("VAR1") USING
INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1064960 NEXT
2097152 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SIEBEL_AMP" ENABLE ;
ALTER TABLE "SIEBEL_AMP"."CFTEST" ADD CONSTRAINT "CFTESTCK" CHECK
(var1 < 500) ENABLE NOVALIDATE ;
ALTER TABLE "SIEBEL_AMP"."CFTEST" ENABLE CONSTRAINT "CFTESTCK" ;
grant SELECT on CFTEST to SALES_UPD_ROLE;
create or replace trigger cftesttrig
before insert on cftest
for each row
begin
:new.date_last_chngd := sysdate; end;
/
comment on column CFTEST.VAR1 is 'this table is completely pointless';
comment on column CFTEST.VAR2 is 'the fact that you are reading this
demonstrates you need to do some serious QA ''right now''';
--Import: Release 8.1.7.2.0 - Production on Thu Nov 13 10:28:56 2003
--(c) Copyright 2000 Oracle Corporation. All rights reserved.
--Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
--With the Partitioning option
--JServer Release 8.1.7.2.0 - 64bit Production
--Export file created by EXPORT:V08.01.07 via conventional path
--import done in US7ASCII character set and US7ASCII NCHAR character set
--. importing SIEBEL_AMP's objects into SIEBEL_AMP
--CREATE TABLE "CFTEST" ("VAR1" NUMBER, "VAR2" LONG, "DATE_LAST_CHNGD" DATE)
-- PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 1064
--960 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS
-- 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SIEBEL_AMP"
--ALTER TABLE "CFTEST" ADD PRIMARY KEY ("VAR1") USING INDEX PCTFREE 10 INITR
--ANS 2 MAXTRANS 255 STORAGE(INITIAL 1064960 NEXT 2097152 MINEXTENTS 1 MAXEXT
--ENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEF
--AULT) TABLESPACE "SIEBEL_AMP" ENABLE
--ALTER TABLE "CFTEST" ADD CONSTRAINT "CFTESTCK" CHECK (var1 < 500) ENABLE NO
--VALIDATE
--COMMENT ON COLUMN "CFTEST"."VAR1" IS 'this table is completely pointless'
--COMMENT ON COLUMN "CFTEST"."VAR2" IS 'the fact that you are reading this
--demonstrates you need to do some serious QA ''right now'''
--CREATE TRIGGER "SIEBEL_AMP".cftesttrig
--before insert on cftest
--for each row
--
--begin
-- :new.date_last_chngd := sysdate;
--end;
--
--ALTER TRIGGER "CFTESTTRIG" ENABLE
--ALTER TABLE "CFTEST" ENABLE CONSTRAINT "CFTESTCK"
--Import terminated successfully without warnings.
---------------------------------------------------------------------------
Received on Thu Nov 13 2003 - 11:54:39 CST
 |
 |