Indexes, primary and secondary keys

Toad for Oracle
1

create table TBS_NUBIA1 as select * from hr.employees;

Hello everyone
I copied the table to another tablespace but the primary and secondary keys are not copied, I also need to copy the indexes.
Could you help me please?

2

The create table ... as select ... command only creates a new table. It does not create any constraints, indexes etc.

If you want to create an exact replica of the table in another schema, then you have a couple of choices in Toad:

  1. Open the schema browser;
  2. Select the current owner in the drop down;
  3. Select "Tables" in the drop down;
  4. Select the desired table in the list of tables;
  5. On the right side panel, select the "Script" tab;
  6. Wait ....
  7. Click the button to copy the script to the editor - it looks like a sheet of paper and a pencil;
  8. Edit the script in the editor to change the schema names, or anything else you want to change.

Alternatively:

  1. Open the Schema Browser;
  2. Select the current owner in the drop down;
  3. Select "Tables" in the drop down;
  4. Find the table in the list, you can filter it, and right-click it;
  5. Select "Create";
  6. Select "Like";
  7. Wait a bit...
  8. Fill in the details you want to change.

HTH

Cheers,
Norm. [TeamT]

1 Like
3

What Norm said.

The only thing that I have to add is that Norm's first set of steps doesn't include table data. So I would do add this in the editor at the end of his steps:

insert into TBS_NUBIA1 select * from hr.employees;
commit;

The 2nd set of steps is good too but doesn't include indexes except the ones that come automatically with constraints.

1 Like
4

Ah, yes, copying the data! Silly me.

Thanks John.
Happy new Year.

Cheers,
Norm. [TeamT]