alter table move long
Charles J. Fisher
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":
-----
#!/bin/sh
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
}' $1.sql > $1.sql.new
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 /
/ cfi...@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_AUTO_UPD_FLG" DEFAULT
NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_DATE" DEFAULT NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_DEFER_FLG" DEFAULT
'R') ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_DOCK_REQ_FLG" DEFAULT
NULL) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_DOCK_STAT_FLG" 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) ;
ALTER TABLE "SIEBEL"."S_UPG_KIT" MODIFY ("FILE_SRC_TYPE" DEFAULT
NULL) ;
CREATE UNIQUE INDEX "SIEBEL"."S_UPG_KIT_U1" ON "S_UPG_KIT" ("NAME" ,
"CONFLICT_ID" ) PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768)
TABLESPACE "SEA_INDEX32K" LOGGING ;
CREATE UNIQUE INDEX "SIEBEL"."S_UPG_KIT_P1" ON "S_UPG_KIT" ("ROW_ID" )
PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768) TABLESPACE
"SEA_INDEX32K" LOGGING ;
Import: Release 8.1.7.2.0 - Production on Thu Oct 23 13:12:53 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'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 DELETE ON "S_UPG_KIT" TO PUBLIC;
GRANT INDEX 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;
CREATE UNIQUE INDEX "S_UPG_KIT_P1" ON "S_UPG_KIT" ("ROW_ID" ) PCTFREE 5 IN;
ITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768) TABLESPACE "SEA_INDEX32K" LOGG;
ING;
Import terminated successfully without warnings.
Charles J. Fisher
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:
- grants
- comments
- triggers (will mangle whitespace to a small extent)
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:
- Be warned that export and import include passwords as command line parameters
that may be visible in a UNIX process list (ps -ef). My script also
makes these passwords visible (for a short time). If you do not trust every
local UNIX user on your system with the password that you are using to
extract, then you must take steps to conceal the password. The unsupported
"hide.c" can be obtained from metalink for this purpose.
(Oracle tools are *so* well-designed - NOT!)
- GNU awk is used in the script to stabilize the awk version on all
platforms. Solaris "nawk" or HP-UX "awk" may work equally well, YMMV.
You might even be able to get this to run on NT with cygwin.
- You will probably want to modify the DDL file produced by the script
(i.e. to change tablespaces, storage parameters, etc.).
- You may want to separate the CREATE INDEX DDL and create some or all of
them at a later time (why anybody ever uses "imp indexes=y" is beyond me).
Remember that UNIQUE constraints cannot be enforced without an index.
- An example run of the script is also included below.
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 /
/ cfi...@rhadmin.org | mind and won't change the subject." /
/ http://rhadmin.org | -- Winston Churchill /
---------------------------------------------------------------------------
ATAB.sh:
---------------------------------------------------------------------------
#!/bin/ksh
if [ $# != 3 ]
then
print "Usage: $0 table user passord"
print "(creates 'TABLE.sql' script to drop, create & import table)"
exit
fi
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
}
}' ${TAB}.sql > ${TAB}.sql.new
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';
" | sqlplus -silent /nolog | gawk '!/^Connected\.$/' >> ${TAB}.sql
# --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);
END;
/
" \
| sqlplus -silent /nolog \
| gawk '{
if($0 ~ /^Connected\.$/) next
if($0 ~ /^\t/) sub(/^\t/,"")
if($0 ~ /TrIgGer\t/) sub(/TrIgGer\t/,"trigger ")
print
}
' >> ${TAB}.sql
# --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
}' >> ${TAB}.sql
# --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:
---------------------------------------------------------------------------
lock table CFTEST in exclusive mode;
!exp userid=SIEBEL_AMP/xxx file=CFTEST-data.dmp tables=CFTEST rows=y grants=n indexes=n constraints=n compress=n feedback=100000
drop table CFTEST;
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.
---------------------------------------------------------------------------