You can't change the owner of a table.
You can create a new table that is owned by NEW_USER
, copy the data from the old table to the new table, drop the foreign key constraints that reference the old table, and create new foreign key constraints that reference the new table. But that's realistically going to require a downtime window since the tables need to be static while this copy is going on.
There are various ways to create a new table that is a copy of the old table. Personally, I'd use your favorite PL/SQL IDE (i.e. SQL Developer, TOAD, etc.) to generate the DDL for Old_User.Table_A
and then manually edit the DDL to create New_User.Table_A
. You could also do an export & import with the FROMUSER
and TOUSER
parameters. Or you can use the DBMS_METADATA
package to get the DDL
SELECT dbms_metadata.get_ddl( 'TABLE', 'TABLE_A', 'OLD_USER' )
FROM dual
and edit that.
Once you've created the new table, you can copy the data over
INSERT INTO new_user.table_a( <<list of columns>> )
SELECT <<list of columns>>
FROM old_user.table_a
Then, you'll need to go through each child table dropping the old foreign key constraint and creating the new constraint, i.e.
ALTER TABLE child_of_a
DROP CONSTRAINT fk_constraint_name;
ALTER TABLE child_of_a
ADD CONSTRAINT fk_constraint_name FOREIGN KEY (name_of_column_in_child)
REFERENCES new_user.table_a( name_of_column_in_parent )
If you have enough child tables, you could potentially write a script that dynamically generates that DDL.
Once that's done, you can drop the old table and let the applications start modifying the data again.
It depends on the GUI but they all should have a way of selecting the SYSDBA
privilege. Toad has it appear as a drop-down box called 'Connect as:' as does Oracle Enterprise Manager. For SQL Developer you use the 'Role' box when creating a new connection.
As far as / as sysdba
is concerned it just means that operating system authentication is being used so no password is needed. You can only use it if you are logged in as the oracle user or a member of the dba group (or for Windows, I believe as an administrator).
When you grant a user the SYSDBA
privilege it only means they have the option to log in with the SYSDBA
privilege and not that they will have dba privileges when they log in normally. The same is true for the SYS
user: you can log in as SYS
without the SYSDBA
privilege and so lack the ability to shutdown the database, for example.
Best Answer
Oracle data dictionary views that have the
ALL_
prefix provide information about objects that your user has access to. Views that have theDBA_
prefix provide information about all objects in the database. And views that have theUSER_
prefix provide information about objects that you own.In this case,
MYDBUSER
does not and can not have privileges on the indexes owned byMYDBOWNER
because there are no privileges one can grant on individual indexes so it makes sense thatMYDBUSER
would not see the indexes inALL_OBJECTS
. IfMYDBUSER
were granted access toDBA_OBJECTS
orDBA_INDEXES
(either individually or via theSELECT ANY DICTIONARY
privilege), you could queryDBA_OBJECTS
(orDBA_INDEXES
) to get a list of all the indexes owned byMYDBOWNER
.