daily pastebin goal
25%
SHARE
TWEET

Change table and index ownership

ExaGridDba Jul 12th, 2016 (edited) 90 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. [oracle@stormking cdb12102 table]$ sqlplus /nolog @ conn.pdbm.u.sql
  2.  
  3. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 3 21:44:22 2017
  4.  
  5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
  6.  
  7. Connected.
  8. SQL> @ change.owner.sql
  9. SQL> drop user olduser cascade;
  10.  
  11. User dropped.
  12.  
  13. SQL> drop user exchuser cascade;
  14.  
  15. User dropped.
  16.  
  17. SQL> drop user newuser cascade;
  18.  
  19. User dropped.
  20.  
  21. SQL> create user olduser identified by pw default tablespace users quota unlimited on users;
  22.  
  23. User created.
  24.  
  25. SQL> create table olduser.lost ( f varchar2(30), l varchar2(30) );
  26.  
  27. Table created.
  28.  
  29. SQL> insert into olduser.lost ( f, l ) values ( 'Sherlock', 'Holmes' );
  30.  
  31. 1 row created.
  32.  
  33. SQL> commit;
  34.  
  35. Commit complete.
  36.  
  37. SQL> alter table olduser.lost
  38.   2  add constraint lost_pk primary key ( f, l );
  39.  
  40. Table altered.
  41.  
  42. SQL>
  43. SQL> create user exchuser identified by pw default tablespace users quota unlimited on users
  44.   2  ;
  45.  
  46. User created.
  47.  
  48. SQL> create table exchuser.exch
  49.   2  partition by range ( l )
  50.   3  ( partition m values less than ( maxvalue ) )
  51.   4  as select * from olduser.lost where 1 = 0;
  52.  
  53. Table created.
  54.  
  55. SQL>
  56. SQL> alter table exchuser.exch
  57.   2  add constraint tbls_pk primary key ( f, l ) using index local;
  58.  
  59. Table altered.
  60.  
  61. SQL> alter table exchuser.exch
  62.   2  exchange partition m with table olduser.lost including indexes;
  63.  
  64. Table altered.
  65.  
  66. SQL>
  67. SQL> create user newuser identified by pw default tablespace users quota unlimited on users
  68.   2  ;
  69.  
  70. User created.
  71.  
  72. SQL> create table newuser.found
  73.   2  ( j varchar2(30), k varchar2(30) );
  74.  
  75. Table created.
  76.  
  77. SQL> alter table newuser.found
  78.   2  add constraint found_PK primary key ( j, k );
  79.  
  80. Table altered.
  81.  
  82. SQL> alter table exchuser.exch
  83.   2  exchange partition m with table newuser.found including indexes;
  84.  
  85. Table altered.
  86.  
  87. SQL> select j, k from newuser.found;
  88.  
  89. J                              K
  90. ------------------------------ ------------------------------
  91. Sherlock                       Holmes
  92.  
  93. SQL>
  94. SQL> select owner, index_name, table_owner, table_name, num_rows, status
  95.   2  from dba_indexes where table_owner = 'NEWUSER' and table_name = 'FOUND';
  96.  
  97. OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                       NUM_ROWS STATUS
  98. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- --------
  99. NEWUSER                        FOUND_PK                       NEWUSER                        FOUND                                   1 VALID
  100.  
  101. SQL>
  102. SQL> quit
  103. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  104. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  105. and Real Application Testing options
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top