I have a user named SDE and the tablespace for this users tables are in SDE_TBS, I would like to move all of these tables to tablespace SDEBUS_DT. For indexes I would like to move to SDEBUS_IX. How would I accomplish this? I have reference this thread, but it does not address my question completely.
Oracle 19c – How to Move Tables and Indexes to New Tablespaces
oracletablespaces
Related Question
- Oracle – Performance Benefits of Segregating Database Objects into Different Tablespaces
- Oracle – How to Directly Query Other User Tables
- How to reduce sysaux tablespace size with Audit Trail Unified enabled
- Oracle Permissions – Schema Grants and Tablespaces
- Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)
- Oracle – Default Quota on Tablespace for Users
Best Answer
What you want to do easily (assuming you have the appropriate permissions) is to invoke the PL/SQL procedure DBMS_REDEFINITION.REDEF_TABLE which you can use to change things such as tablespaces for data and for indexes. Here is a snippet of code for moving a table named
MY_DATA_TABLE
. To do several, you would need to invoke it once per table.But before you begin, just check to see if your
SDE
owner has permissions on the tablespacesSDEBUS_DT
andSDEBUS_IX
first. Also, if those commands are invoked by theSDE
user, that user will needCREATE TABLE
andCREATE MVIEW
privileges.Now if you want to loop through several tables, run a SQL like the following, which will display the commands to copy/paste. Yes, you could do the following in a PL/SQL loop, but I don't do that often enough to remember which of the 3 or 4 ways to do that, without looking it up!