Some tables are partitioned, so if the tables are partitioned, then you need to move both partitions and subpartitions also. ALTER TABLE MEHMETSALIH.TEST_SUM_COUNTRY_T1 move tablespace DEFAULT_TBS parallel 8 ONLINE ĪLTER TABLE MEHMETSALIH.TEST_SUM_COUNTRY_T2 move tablespace DEFAULT_TBS parallel 8 ONLINE ĪLTER TABLE MEHMETSALIH.TEST_SUM_DEVICE_FULL_INFO_03092019 move tablespace DEFAULT_TBS parallel 8 ONLINE ĪLTER TABLE MEHMETSALIH.TEST_SUBS_PACK_HIST move tablespace DEFAULT_TBS parallel 8 ONLINE You can use the following script to move tables. select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'new_tablespace_name parallel 8 ' from dba_tables where tablespace_name='DATA' Ībove SQL script generates table move script as follows. If your tables are very big, you can use PARALLELand ONLINE option and move tables parallel and ONLINE like following. Use above sql query result and execute them to move tables from one tablespace to new tablespace. select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'new_tablespace_name parallel 8 ' from dba_tables where tablespace_name='DATA' If your tables are very big, you can use parallel option and move tables parallel like following.
If you execute above scripts, tables will be moved to new tablespace. ALTER TABLE ADMUSER.USEROBS move tablespace NEW_TBS_NAME ĪLTER TABLE ADMUSER.UMEASURE move tablespace NEW_TBS_NAME ĪLTER TABLE ADMUSER.UACCESS move tablespace NEW_TBS_NAME ĪLTER TABLE ADMUSER.UDFTYPE move tablespace NEW_TBS_NAME ĪLTER TABLE ADMUSER.ACTVTYPE move tablespace NEW_TBS_NAME ĪLTER TABLE ADMUSER.ACTVCODE move tablespace NEW_TBS_NAME select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'NEW_TBS_NAME ' from dba_tables where tablespace_name='PMDB_DAT1' Ībove SQL script generated following move scripts. You can move lots of tables to the new tablespace with using generate move scripts. ALTER TABLE MEHMET.SALIH MOVE TABLESPACE NEW_TABLESPACE_NAME You can move any table to new tablespace in Oracle with following command.
#Moving an extfs partion how to
Press the enter key again to get to the last sector Last sector, +sectors or +size (899072-31457279, default 31457279):Ĭreated a new partition 3 of type 'Linux' and of size 14.6 GiB.I will explain How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle in Oracle.Įspecially you want to drop any tablespace so you need to discharge or move all tables to the new tablespace. A disk can have only four partitions as primary partitions Select (default p): pĮnter the partition number which is 3 or press enter to leave at default which is 3 Partition number (3,4, default 3):įirst sector (899072-31457279, default 899072): P primary (2 primary, 0 extended, 2 free)Į extended (container for logical partitions)Įnter the letter “p” to make it a primary partition, yours might be secondary depending on the number of partitions you have. NOW, ENTER THE LETTER “n” TO RECREATE THE PARTITION TO YOUR DESIRED SIZE Command (m for help): n dev/sda3 899072 14569471 13670400 6.5G 83 LinuxĮnter the letter “d”to delete a partition Command (m for help): dĮnter the partition number 3 or press enter to leave it at default which is 3 Partition 3 has been deleted. I/O size (minimum/optimal): 512 bytes / 512 bytesĭevice Boot Start End Sectors Size Id Type Sector size (logical/physical): 512 bytes / 512 bytes Initialize /dev/sda using the fdisk utility ~]# fdisk /dev/sdaĬhanges will remain in memory only, until you decide to write them.īe careful before using the write command.Įnter the letter “p” to print all the partitions on sda Command (m for help): pĭisk /dev/sda: 15 GiB, 16106127360 bytes, 31457280 sectors