Well holiday is now over (hence the lack of blog posts for a while) and it's back to the normal day to day work.
An interesting post though to shake off the post holiday blues (inspired by an oracle forums question).
The basic problem was that they wanted to take a full schema export but the dictionary table containing the sequences (SEQ$) has a block corruption and the job keeps failing.
How do i therefore extract the schema and bypass the sequence issue?
Easy enough you think just use exclude=sequence and its job done.
However this is oracle 8 (yes people still use these versions....) and you only have exp which cannot get clever about how you filter what is extracted..... or can it?
I'm doing my demo here on 11.2 as i personally don't have v8 any more but the principle is exactly the same in v8.
First up lets create a demo schema with a sequence to use for testing
SYS@DEMODB>create user demo identified by demo;
User created.
SYS@DEMODB>grant connect,resource,create sequence to demo;
Grant succeeded.
SYS@DEMODB>conn demo/demo
Connected.
DEMO@DEMODB>create sequence demoseq;
Sequence created.
DEMO@DEMODB>
So thats now ready to go - if i extract a schema level export now it happily extracts the seqence
exp demo/demo
Export: Release 11.2.0.3.0 - Production on Thu Sep 3 08:08:06 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp >
(2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DEMO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DEMO
About to export DEMO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DEMO's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
If i now use show=y to list the dumpfile contents we can see the schema ddl
imp demo/demo show=y
Import: Release 11.2.0.3.0 - Production on Thu Sep 3 08:09:02 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing DEMO's objects into DEMO
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'DEMODB', inst_scn=>'8944079698514');"
"COMMIT; END;"
"CREATE SEQUENCE "DEMOSEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 "
"INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE"
Import terminated successfully without warnings.
So how do we not extract this - well this is where the trick comes in. All of the objects that exp extracts are listed in views called EXUxxxxx - these are just views of the data dictionary to give it the information it needs.
We can manipulate these to filter what it does and mimic what datapump can do natively. In fact by modifying these views directly we can pretty much do everything datapump does..... (though of course messing around like this is not advisable...)
So for sequences i look in the $ORACLE_HOME/rdbms/admin directory and open up the catexp.sql file to check what the sequence view is - turns out it's EXU8SEQ.
The current definition is this
CREATE OR REPLACE VIEW exu8seq (
owner, ownerid, name, objid, curval, minval, maxval, incr,
cache, cycle, order$, audt) AS
SELECT u.name, u.user#, o.name, o.obj#, s.highwater, s.minvalue,
s.maxvalue, s.increment$, s.cache, s.cycle#, s.order$, s.audit$
FROM sys.exu81obj o, sys.user$ u, sys.seq$ s
WHERE o.obj# = s.obj# AND
o.owner# = u.user#
/
Let's add a line to that so no sequences are present in the view
CREATE OR REPLACE VIEW exu8seq (
owner, ownerid, name, objid, curval, minval, maxval, incr,
cache, cycle, order$, audt) AS
SELECT u.name, u.user#, o.name, o.obj#, s.highwater, s.minvalue,
s.maxvalue, s.increment$, s.cache, s.cycle#, s.order$, s.audit$
FROM sys.exu81obj o, sys.user$ u, sys.seq$ s
WHERE o.obj# = s.obj# AND
o.owner# = u.user#
and 1=0 -- added to bypass sequences in export due to seq$ corruption
/
We now run that in
SYS@DEMODB>CREATE OR REPLACE VIEW exu8seq (
2 owner, ownerid, name, objid, curval, minval, maxval, incr,
3 cache, cycle, order$, audt) AS
4 SELECT u.name, u.user#, o.name, o.obj#, s.highwater, s.minvalue,
5 s.maxvalue, s.increment$, s.cache, s.cycle#, s.order$, s.audit$
6 FROM sys.exu81obj o, sys.user$ u, sys.seq$ s
WHERE o.obj# = s.obj# AND
o.owner# = u.user#
7 8 9 and 1=0 -- added to bypass sequences in export due to seq$ corruption
10 /
View created.
And now we repeat the exp/imp process
exp demo/demo
Export: Release 11.2.0.3.0 - Production on Thu Sep 3 08:10:51 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp >
(2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DEMO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DEMO
About to export DEMO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DEMO's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
imp demo/demo show=y
Import: Release 11.2.0.3.0 - Production on Thu Sep 3 08:11:19 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing DEMO's objects into DEMO
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'DEMODB', inst_scn=>'8944079698736');"
"COMMIT; END;"
Import terminated successfully without warnings.
This could be a very useful technique for people on old versions (and to be honest i wish i'd written this post about 20 years ago......). Datapump however can do all of this for you natively and is far superior in other ways too.
This should help a few people though i think.
Comments
Post a Comment