Oracle Logbook

November 15, 2007

Cookbook for rename owner

Filed under: Uncategorized — heliodias @ 5:39 am

First i must advice that i didn´t recommend that nobody do this on a production environment. 

All you need to do is make a simple update on dictionary. 

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER=’HELIO’;

  COUNT(*)                                                                     
———-                                                                     
         1                                                                     

SQL> SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER=’HELIO’;

OBJECT_NAME         OBJECT_TYPE                                                               
—————-    ——————-                                                            
T1                  TABLE                                                                          
                                                                               

SQL> SELECT OWNER# FROM SYS.OBJ$ WHERE NAME=’T1′;

    OWNER#                                                                     
———-                                                                     
        37                                                                     

SQL> SELECT OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’T1′;

OWNER                                                                          
——————————                                                 
HELIO                                                                          

SQL> UPDATE SYS.OBJ$ SET OWNER#=10 WHERE NAME=’T1′;

1 linha atualizada.
SQL> SELECT OWNER# FROM OBJ$ WHERE NAME=’OBJ$’;

    OWNER#                                                                     
———-                                                                     
         0                                                                     

SQL> UPDATE SYS.OBJ$ SET OWNER#=0 WHERE NAME=’T1′;

1 linha atualizada.

SQL> SELECT OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’T1′;

OWNER                                                                          
——————————                                                 
SYS                                                                            

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER=’HELIO’;

  COUNT(*)                                                                     
———-                                                                     
         0                                                                     

SQL> COMMIT;

Commit concluído.

SQL> SHUTDOWN IMMEDIATE;
Banco de dados fechado.
Banco de dados desmontado.
Instância ORACLE desativada.
SQL> STARTUP OPEN
Instância ORACLE iniciada.

Total System Global Area  146800640 bytes                                      
Fixed Size                  1286220 bytes                                      
Variable Size              92278708 bytes                                      
Database Buffers           50331648 bytes                                      
Redo Buffers                2904064 bytes                                      
Banco de dados montado.
Banco de dados aberto.
SQL> SELECT OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’T1′;

OWNER                                                                          
——————————                                                 
SYS   

Advertisements

3 Comments »

  1. My point of view is simple:
    You have a 100GB TEST database , and 10 minutes before an important presentation you realize that you have imported the data on wrong Owner,
    What you gone do?
    1. Cry
    2. Say to everybody come some another day, and put in danger some contract
    Or
    3. Become unsuported on you TEST Database, and solve the problem??

    Regards
    Helio Dias
    http://heliodias.com

    Comment by heliodias — November 15, 2007 @ 9:43 am | Reply

  2. create user properuser identified by properuser;
    grant connect to properuser;

    BEGIN
    dbms_output.enable(1000000);

    FOR x IN ( SELECT owner,
    object_name,
    DECODE(object_type, ‘TABLE’ , ‘select, insert, update, delete’,
    ‘SEQUENCE’, ‘select’,
    ‘VIEW’, ‘select’,
    ‘execute’) AS privs,
    DECODE (owner, ‘wronguser’, ‘properuser’) AS app_user
    FROM dba_objects
    WHERE object_type IN (‘TABLE’, ‘PACKAGE’, ‘PROCEDURE’,
    ‘FUNCTION’, ‘SEQUENCE’, ‘VIEW’)
    AND owner IN (‘wronguser’ ))
    LOOP
    BEGIN
    EXECUTE IMMEDIATE ‘grant ‘ || x.privs || ‘ on ‘ || x.owner ||
    ‘.’ || x.object_name || ‘ to ‘ || x.app_user ;

    EXECUTE IMMEDIATE ‘create or replace synonym ‘|| x.app_user||’.’||x.object_name||
    ‘ for ‘ ||x.owner||’.’||x.object_name ;

    EXCEPTION
    WHEN others THEN
    dbms_output.put_line(‘Bad owner = ‘||x.owner||’; Bad app_user=’||x.app_user||
    ‘; Bad object_name=’||x.object_name);
    END;
    END LOOP;
    END;
    /

    That will solve your problem and it IS supported.

    Comment by Real DBA — November 15, 2007 @ 10:06 pm | Reply

  3. Oh, The Real DBA was so bad.

    Comment by Mr. Dba — March 4, 2008 @ 2:36 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: