Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So, I'm trying to export an oracle database(10.2) from EE to SE over a networklink. It all works fine except that I'm hitting a limit. The database has 1335 schemas and it seems that I can't transfers these in one go. Not with the schemas parameter at least. Currently I'm doing a full=n with schemas=(the schemas to export) as I'm not really sure what a full=y exclude=schemas:() will transfer. Seeing that I want a clean database.

I tried using cmdline impdp and a par file. But got the same error in both:

When trying 1000 schemas: 
ORA-39001: invalid argument value 
ORA-39071: Value for SCHEMAS is badly formed.

When trying 250: 
ORA-39006: internal error 
ORA-39065: unexpected master process exception in DISPATCH 
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-39097: Data Pump job encountered unexpected error -6502

And when using 25 schemas it works fine.

The syntax is exactly the same, being built dynamicly. As we can see it appears that the SCHEMAS variable has a length wich oracle can't handle.

Has anyone of you tried the same thing? Would you recommend going the easy way with full=y? Are you confident it doesn't transfer EE history or data.

Update: An example of EE data imported when using the full=y on SE

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-00001: unique constraint (SYS.WRI$_ALERT_THRESHOLD_PK) violated
Failing sql is:
BEGIN
dbms_server_alert.set_threshold(9000,dbms_server_alert.operator_GE,'85',dbms_server_alert.operator_GE,'97',1,1,NULL,dbms_server_alert.object_type_TABLESPACE,'');COMMIT; END;

Which is AWR which is an enterprise feature.

Another is

ORA-29357: object AUTO_TASK_CONSUMER_GROUP already exists
Failing sql is:
BEGIN
dbms_resource_manager.create_consumer_group('AUTO_TASK_CONSUMER_GROUP','System maintenance task consumer group','ROUND-ROBIN');COMMIT; END;

which I believe is the resource manager and in it's turn enterprise feature.

share|improve this question

1 Answer 1

Apparently a full=y is allowed.
Converting An Enterprise Edition Database To Standard Edition (Doc ID 139642.1)

The only way to properly convert from an Enterprise Edition back to a
Standard Edition is through an Export/Import operation. This way you will
get rid of all Enterprise Edition specific Data Dictionary objects, as the
SYS schema objects are not exported. The Standard Edition EXP utility is
preferred to be used to export the data.

So apparently all the EE DD objects are not transfered. Followed by dropping or excluding while exporting the EE schemas.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.