Skip to Main Content
  • Questions

  • Export from a Enterprise Edition (Partitioned table) into Oracle Standard edition (non-partitioned table)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 20, 2018 - 3:34 pm UTC

Last updated: March 22, 2018 - 2:16 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

I have an Oracle database Enterprise Edition which has partitioned tables. I need to export the full schema from the Enterprise edition with partitioning and import it into a Standard edition database. Please suggest of this is possible.

I am even fine with workarounds or tweaks on the same.

Best Regards,
~Manas

and Connor said...

Yes. All you need to do is pre-create the tables in your standard edition version, and import with ignore=y (old export) or table_exists_action=append (datapump)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I need the tables only ...

A reader, March 21, 2018 - 12:44 pm UTC

hi,

I just need the tables, I am okay if we can skip the data. So a way like if I say partitioning = No and it would remove the partition clause from the tables.

Best Regards!!
Connor McDonald
March 22, 2018 - 2:16 am UTC

Ah....so its just DDL you are after.

So options are:

DEPARTITION (see Paul's comment) combined with METADATA_ONLY in DataPump.

You can also use DBMS_METADATA.GET_DDL (search this site for plenty of examples) and add in a transformation


begin
 dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PARTITIONING',false);
end;
/

DEPARTITION

Paul, March 21, 2018 - 7:10 pm UTC

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/datapump-import-utility.html#GUID-4521C3CD-EE17-4836-9760-D11FC21CBB7F

There are lots of options for datapump import to just get the schema or the schema/data
One of the clauses also relates directly to Partitioning.

[PARTITION_OPTIONS = {NONE | DEPARTITION | EXCHANGE | MERGE}]

I don't have an instance to play with :( but MERGE might be what you are looking for for dealing with the partition structure. That plus

"A value of merge combines all partitions and subpartitions into one table."

If you use that with the
SQLFILE=[directory_object:]file_name
that should create a script with all the DDL that would have been executed to complete the import, hopefully including the PARTITION MERGE



Connor McDonald
March 22, 2018 - 2:16 am UTC

Nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database