Quick and clean, what’s that?

Consider the following:

CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1=2;

I call that ‘quick and dirty,’ and I imagine you have used this technique many, many times.

But, I wish it would have grabbed column comments, indexes, maybe even the partitions. Partitioning a table after the fact is pretty much a no-go.

So what are we to do?

Well, if you have SQL Developer v4.0.3, you can simply right-click on a table and…

Use as a Template

This grabs the existing table definition and throws it into the create table dialog and lets you give it a new name, and make any changes you’d like.

Step 1: Pick your table
Step 1: Pick your table

Me comments! (today is also National Type Like a Pirate Day)

Typing all of these column comments over again would be a complete and total pain.
Typing all of these column comments over again would be a complete and total pain.

Me partitions!

Ahhhh, yeah I needed those too.
Ahhhh, yeah I needed those too.

But wait a second, you’ll have index, constraint, and partition collisions!

Nope. If you look at the DDL generated, the developers have already thought of this and throw a 1 onto the end of those names. You can of course change these to your needs in the create table dialog…

If you’re feeling greedy and wanted the data too, then you can use a INSERT AS SELECT or use one of SQL Developer’s many data offload/onload data tools like the Cart or the Grid Export features.


I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

1 Comment

  1. Avatar

    Oh, I saw that feature on the packages but missed it on tables. That would certainly come in handy for those global temporary copies I have to create sometimes, thanks for the explanation!

Write A Comment

Follow by Email