This is a reference as much for me as anyone else of 3 recent exports that were done where we wanted schema copies with all of the structure (including plsql and user definition etc) but only some of the data. The trick is to include a generic query clause at the top followed by some specifc queries further down - that way by default everything has the original query applied unless it's overridden.
They are all very similar in technique but often it's useful to see multiple examples.
In all examples there were many tens of tables which we extracted with no rows - with only those with specific queries actually extracting any data.
Example 1
schemas=SCHEMA_NAME
flashback_time=systimestamp
QUERY="where 1=0"
QUERY=SCHEMA_NAME.CATEGORY:"where 1=1"
QUERY=SCHEMA_NAME.CATEGORY_VALUE:"where 1=1"
QUERY=SCHEMA_NAME.CHANGELOG:"where 1=1"
QUERY=SCHEMA_NAME.CONSTRAINED_STRUCTURE:"where 1=1"
QUERY=SCHEMA_NAME.DATA_CLASSIFICATION:"where 1=1"
QUERY=SCHEMA_NAME.FIXED_STRUCTURE_COLUMNS:"where 1=1"
Example 2
schemas=SCHEMA_NAME
flashback_time=systimestamp
QUERY="where 1=0"
QUERY=SCHEMA_NAME.CHANGELOG:"where 1=1"
QUERY=SCHEMA_NAME.EXECUTION_STATUS:"where 1=1"
QUERY=SCHEMA_NAME.HOST_CONFIG_TYPE:"where 1=1"
QUERY=SCHEMA_NAME.MODEL_EXECUTION_RESULT:"where 1=1"
QUERY=SCHEMA_NAME.MODEL_STATUS:"where 1=1"
Example 3
schemas=SCHEMA_NAME
flashback_time=systimestamp
QUERY="where 1=0"
QUERY=SCHEMA_NAME.APPLICATIONACCESS:"where ROLEID in (1,23,24,25)"
QUERY=SCHEMA_NAME.APPLICATIONBUNDLES:"where applicationid in (select applicationid from SCHEMA_NAME.APPLICATIONS where (applicationgroup = 0 OR applicationgroup = 41) and iscurrentversion=1)"
QUERY=SCHEMA_NAME.APPLICATIONGROUPS:"where applicationgroupid=41"
QUERY=SCHEMA_NAME.APPLICATIONPARAMETERS:"where applicationid in (select applicationid from SCHEMA_NAME.applications where (applicationgroup = 0 OR applicationgroup = 41) and iscurrentversion=1)"
QUERY=SCHEMA_NAME.APPLICATIONS:"where (applicationgroup = 0 OR applicationgroup = 41) and iscurrentversion=1"
QUERY=SCHEMA_NAME.GROUPOPERATIONS:"where 1=1"
QUERY=SCHEMA_NAME.GROUPS:"where 1=1"
QUERY=SCHEMA_NAME.OPERATIONS:"where 1=1"
QUERY=SCHEMA_NAME.RESOURCES:"where 1=1"
QUERY=SCHEMA_NAME.ROLEGROUPMEMBERSHIP:"where 1=1"
QUERY=SCHEMA_NAME.ROLES:"where 1=1"
QUERY=SCHEMA_NAME.SERVERAPPLICATIONROLES:"where 1=1"
QUERY=SCHEMA_NAME.SERVERAPPLICATIONS:"where 1=1"
QUERY=SCHEMA_NAME.USERROLES:"where roleid in (1,23,24,25)"
QUERY=SCHEMA_NAME.USERS:"where USERID in (SELECT userid from SCHEMA_NAME.USERROLES where ROLEID in (1, 23, 24, 25))"
Comments
Post a Comment