| 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 -> alter table move long
As most of you might know, "ALTER TABLE MOVE" cannot be executed against a
table that contains a LONG column. I have many tables of this type that I
would like to move to a new tablespace.
I have already moved about 75 empty tables by relying upon the simple script below, which uses the SHOW and INDEXFILE options to the import utility. The SQL produced by my script still requires a bit of "massaging":
exp userid=siebel/xxx tables=$1 rows=n grants=y indexes=y constraints=y imp userid=siebel/xxx full=y indexfile=$1.sql imp userid=siebel/xxx show=y full=y >> $1.sql 2>&1
awk 'BEGIN { print "drop table '$1';"}
{
if ($0 ~ /^REM/) sub(/^REM/,"");
if ($0 ~ /SEA_DATA/) sub(/SEA_DATA/,"SEA_DATA32K");
if ($0 ~ /^ \"/)
{
sub(/^ "/,"")
sub(/.$/,";")
}
if ($0 ~ /^CONNECT/) $0=""
print
mv $1.sql.new $1.sql
At this point, I need to be able to move tables that actually contain data
(without throwing the data away as I have above) by adding separate
exp/imp commands with ROWS=Y into the generated script (and perhaps a
LOCK TABLE as well).
I still maintain several 7.3 and 8.0 databases which do not support ALTER TABLE MOVE. While I do not need extra functionality in my immediate application, extracting the trigger_body from user_triggers (and ignoring the tab/linefeed corrupted version produced by SHOW=Y) would be nice in the future. Actually, I don't want to rely upon anything produced by SHOW=Y; the grants would be nice to extract, too.
Before I go to all of this work, has someone done it already? And no, I don't want to buy any 3rd party tools.
Attached is an example of the output of my script.
/ Charles J. Fisher | Enlightenment: the realization that your 100 / / cfisher_at_rhadmin.org | line Perl script can be reimplemented as a 5 / / http://rhadmin.org | line shell script. / --------------------------------------------------------------------------
drop table S_UPG_KIT;
CREATE TABLE "SIEBEL"."S_UPG_KIT" ("ROW_ID" VARCHAR2(15) NOT NULL ENABLE, "CREATED" DATE NOT NULL ENABLE, "CREATED_BY" VARCHAR2(15) NOT NULL ENABLE, "LAST_UPD" DATE NOT NULL ENABLE, "LAST_UPD_BY" VARCHAR2(15) NOT NULL ENABLE, "MODIFICATION_NUM" NUMBER(10, 0) NOT NULL ENABLE, "CONFLICT_ID" VARCHAR2(15) NOT NULL ENABLE, "NAME" VARCHAR2(50) NOT NULL ENABLE, "STATUS" VARCHAR2(30) NOT NULL ENABLE,
"TITLE" VARCHAR2(50) NOT NULL ENABLE, "COMMENTS" VARCHAR2(250), "COMPILED_INFO" LONG, "FILE_AUTO_UPD_FLG" CHAR(1), "FILE_DATE" DATE, "FILE_DEFER_FLG" CHAR(1), "FILE_DOCK_REQ_FLG" CHAR(1), "FILE_DOCK_STAT_FLG" CHAR(1), "FILE_EXT" VARCHAR2(10), "FILE_NAME"VARCHAR2(200), "FILE_REV_NUM" VARCHAR2(15), "FILE_SIZE" NUMBER(10, 0), "FILE_SRC_PATH" VARCHAR2(255), "FILE_SRC_TYPE" VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 49152 NEXT 49152 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SEA_DATA32K" ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("CREATED" DEFAULT sysdate ) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("LAST_UPD" DEFAULT sysdate ) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("MODIFICATION_NUM" DEFAULT 0
) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("CONFLICT_ID" DEFAULT '0' ) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("COMMENTS" DEFAULT NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("COMPILED_INFO" DEFAULT
NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_EXT" DEFAULT NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_NAME" DEFAULT NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_REV_NUM" DEFAULT NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_SIZE" DEFAULT NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_SRC_PATH" DEFAULT
NULL) ;
(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's objects into SIEBEL
CREATE TABLE "S_UPG_KIT" ("ROW_ID" VARCHAR2(15) NOT NULL ENABLE, "CREATED" ;
DATE NOT NULL ENABLE, "CREATED_BY" VARCHAR2(15) NOT NULL ENABLE, "LAST_UPD";
DATE NOT NULL ENABLE, "LAST_UPD_BY" VARCHAR2(15) NOT NULL ENABLE, "MODIFIC;
ATION_NUM" NUMBER(10, 0) NOT NULL ENABLE, "CONFLICT_ID" VARCHAR2(15) NOT NU;
LL ENABLE, "NAME" VARCHAR2(50) NOT NULL ENABLE, "STATUS" VARCHAR2(30) NOT N;
ULL ENABLE, "TITLE" VARCHAR2(50) NOT NULL ENABLE, "COMMENTS" VARCHAR2(250),;
"COMPILED_INFO" LONG, "FILE_AUTO_UPD_FLG" CHAR(1), "FILE_DATE" DATE, "FILE;
_DEFER_FLG" CHAR(1), "FILE_DOCK_REQ_FLG" CHAR(1), "FILE_DOCK_STAT_FLG" CHAR;
(1), "FILE_EXT" VARCHAR2(10), "FILE_NAME" VARCHAR2(200), "FILE_REV_NUM" VAR;
CHAR2(15), "FILE_SIZE" NUMBER(10, 0), "FILE_SRC_PATH" VARCHAR2(255), "FILE_;
SRC_TYPE" VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGG;
ING STORAGE(INITIAL 49152 NEXT 49152 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREAS;
E 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SEA_DAT;
A";
ALTER TABLE "S_UPG_KIT" MODIFY ("CREATED" DEFAULT sysdate );
ALTER TABLE "S_UPG_KIT" MODIFY ("LAST_UPD" DEFAULT sysdate );
ALTER TABLE "S_UPG_KIT" MODIFY ("MODIFICATION_NUM" DEFAULT 0 );
ALTER TABLE "S_UPG_KIT" MODIFY ("CONFLICT_ID" DEFAULT '0' );
ALTER TABLE "S_UPG_KIT" MODIFY ("COMMENTS" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("COMPILED_INFO" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_AUTO_UPD_FLG" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_DATE" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_DEFER_FLG" DEFAULT 'R');
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_DOCK_REQ_FLG" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_DOCK_STAT_FLG" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_EXT" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_NAME" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_REV_NUM" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_SIZE" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_SRC_PATH" DEFAULT NULL);
ALTER TABLE "S_UPG_KIT" MODIFY ("FILE_SRC_TYPE" DEFAULT NULL);
GRANT ALTER ON "S_UPG_KIT" TO PUBLIC;
GRANT INSERT ON "S_UPG_KIT" TO PUBLIC; GRANT SELECT ON "S_UPG_KIT" TO PUBLIC; GRANT UPDATE ON "S_UPG_KIT" TO PUBLIC; GRANT REFERENCES ON "S_UPG_KIT" TO PUBLIC; GRANT SELECT ON "S_UPG_KIT" TO "SIEBEL_QRY_ROLE"; GRANT DELETE ON "S_UPG_KIT" TO "SIEBEL_UPD_ROLE"; GRANT INSERT ON "S_UPG_KIT" TO "SIEBEL_UPD_ROLE"; GRANT SELECT ON "S_UPG_KIT" TO "SIEBEL_UPD_ROLE"; GRANT UPDATE ON "S_UPG_KIT" TO "SIEBEL_UPD_ROLE"; GRANT DELETE ON "S_UPG_KIT" TO "SSE_ROLE"; GRANT INSERT ON "S_UPG_KIT" TO "SSE_ROLE"; GRANT SELECT ON "S_UPG_KIT" TO "SSE_ROLE"; GRANT UPDATE ON "S_UPG_KIT" TO "SSE_ROLE"; GRANT DELETE ON "S_UPG_KIT" TO "SIEBEL_AMP"; GRANT INSERT ON "S_UPG_KIT" TO "SIEBEL_AMP"; GRANT SELECT ON "S_UPG_KIT" TO "SIEBEL_AMP";GRANT UPDATE ON "S_UPG_KIT" TO "SIEBEL_AMP"; CREATE UNIQUE INDEX "S_UPG_KIT_U1" ON "S_UPG_KIT" ("NAME" , "CONFLICT_ID" ); PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768) TABLESPACE "SEA_; INDEX32K" LOGGING;
 |
 |