Primary Keys Issue on Remote Tables

[shsh_shah]

have this query where i can check what are the primary keys on remote table.

select column_name from all_cons_columns where table_name='EMPLOYEES' and owner='BRENDAN';
COLUMNS_NAME
*************************
EMPLOYEE_ID

Now Question is

1. When we do copy tables does that also copy Primary keys because when i run the same query after copying tables it does not show any rows e,g,

select column_name from all_cons_columns where table_name='EMPLOYEES_COPY' and owner='TEST'

2. When we do copy does that also copy constraints?

Any help will be really appreciated.

[slimdave]

It woulddepend how you "copy". Export/Import will be default, though it has options not to. So how did you do the copy?

[shsh_shah]

By doing
Create Table <newTableName> as select * from
<oldTableName> [where 1 = 2 -- optional]
Now need to copy constraints but i cannot edit remote tables or i cannot apply Triggers or views on those tables. So any other way around

[slimdave]

You can create a table, but you can't create triggers or constraints on it, or create views?

[shsh_shah]

Sorry look like u took me wrong...
I can only view the contents of that schema and can make copies of his schema structure thats all.

[WilliamR]

Now Question is

1. When we do copy tables does that also copy Primary keys
2. When we do copy does that also copy constraints?
No, and no.

[shsh_shah]

hmmm....William you just said no and no...But can you give me idea how to do it..Help from any bosy will be really appreciated

[WilliamR]

Try

DBMS_METADATA.GET_DDL (http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414)@remotedb(objecttype, name);

e.g.

DBMS_METADATA.GET_DDL@remotedb('TABLE','EMPLOYEES');

This is a function that you can use in either a plain SQL query or in PL/SQL. It will return the SQL to create the table, including constraints.