Re: Is there a SQL command to change table ownership?

Hagen,

There is no direct command to do so however methods are

Method A:

1. As SYS grant all on the table(s) to yourself.
2. As yourself give the following SQl command: CREATE table <table_name> as SELECT * from sys.<table_name>.
3. As SYS drop the table(s). ( Might want to check if it worked before).

Method B:

1. As sys give yourself the exp_full_database sys privilege, or make yourself a DBA.
2. Export the tables.
3. Import the tables as yourself, use fromuser and touser clauses for import.

NOTE: the reason you can not select or describe the tables is because you

      have not created synonyms ( or you must name the user prior to the 
      table name i.e SELECT * from sys.<table_name>). To create  a synonym
      the command is Create synonym <synonym_name for sys.<table_name; 
          ( generally the synonym_name = table_name).

Have fun with Oracle, and please do not use sys account to create any objects.

Vikram Goel

Sr. DBA

MOTOROLA - Byonton Beach, FL

email: vgoel_at_emi.net

Hagen Finley <hagen_at_violet.berkeley.edu> wrote:
> Befogged by the enthusiam of a Oracle neo-phyte I have created three
>tables as 'SYS'. Now I have come to appreciate reasons why one would seek to
>avoid that. In particular, I have found that when I access the
>Oracle database via Power Objects logging in as 'SYS' I pull up every
>administrative table, index, view, etc. in the database which is muddlesome
>at best. I am sure working in a table space as 'SYS' also carries with it
>risks that most DBA's would consider unacceptable.
> Consequently, I would like to transfer the ownership of these tables to a
>less threatening user (me). I realize one approach to this problem would be
>to grant object priviledges to the tables for user:hagen but 1) I am having
>a little trouble with that (I have attempted to do that with the Object
>Manager and although the Object Manager says user:hagen has been granted the
>full gamut of priviledges associated with the tables, SQL*Plus tells
>uesr:hagen those tables don't exist), and 2), I don't know, but I am
>concerned that even after those priviledges have been granted, those tables
>won't show up when I log in as hagen using Power Objects.
> Therefore, I have been routing the hard soil of my SQL literature, but
>have yet to unearth any information which deals with altering the ownership
>of a table.
>
>
Received on Thu Nov 02 1995 - 00:00:00 CET