Batch migration of oracle table store

Scenario: When marine encounters A problem, it is required to move A user's table from tablespace A to tablespace B. The number of user tables is more than 1000.
Analysis 1: it can be determined that the DDL statement alter table table_name remove tablespace tablespace_name is used for moving tablespaces in a single table;
Next, you need to check the dba_tables view to find the table name on table space A, and save the value as A variable and pass it to the for loop;
Finally, execute dynamic SQL statements. It is impossible to directly execute DDL statements in PL/SQL. Therefore, execute immediate must be used to execute dynamic SQL statements;

Analysis 2: Of course, you can also piece together an SQL script, and then execute the script to achieve this effect, but the execution efficiency is definitely inferior to the former

SQL> set echo off SQL> set feedback off SQL> set heading off SQL> spool/home/oracle/move. SQL SQL> select 'alter table hr. '| table_name | 'move tablespace TBS_APPLE; '2 from dba_tables where owner = 'hr' and tablespace_name = 'example'; [oracle @ orcl ~] $ Cat move. SQL alter table hr. REGIONS move tablespace TBS_APPLE; alter table hr. LOCATIONS move tablespace TBS_APPLE; alter table hr. when ments move tablespace TBS_APPLE; alter table hr. JOBS move tablespace TBS_APPLE; alter table hr. EMPLOYEES move tablespace TBS_APPLE; alter table hr. JOB_HISTORY move tablespace TBS_APPLE; simulate related scenarios
Step 1: Create A tablespace and find the table owned by the user on Table A. Here, the user uses HR as an example. The tablespace uses example as an example.

SQL> create tablespace tbs_apple datafile '/u01/app/oracle/oradata/orcl/tbs_apple01.dbf '2 size 10 M autoextend on next 10 M maxsize 1G 3 extent management local segment space management auto; SQL> select table_name from dba_tables where owner = 'hr' and tablespace_name = 'example '; TABLE_NAME ------------------------------ REGIONS LOCATIONS JOBS EMPLOYEES JOB_HISTORY 6 rows selected. step 2: Use Variables

SQL> declare 2 v_1 varchar2 (200); 3 begin 4 select table_name into v_1 from dba_tables where owner = 'hr 'and tablespace_name = 'example '; 5 begin 6 for I in v_1 7 loop 8 execute immediate 'alter table hr. '| v_1 | 'move tablespace example'; 9 end loop; 10 end; 11 end; 12/for I in v_1 * ERROR at line 6: ORA-06550: line 6, column 17: PLS-00456: item 'v _ 1' is not a cursor ORA-06550: line 6, column 8: PL/SQL: The above Statement ignored error indicates that in PL/SQL, it is impossible to directly convert the variable into a hash and put it into a for loop. You need to use a cursor. This can be easily implemented in shell scripts!

Step 3: Use a cursor
Search for downstream targets from Baidu first:
Cursor is a data buffer provided by the system for users to store the execution results of SQL statements. Each cursor area has a name. You can use SQL statements to obtain records from the cursor one by one and assign them to the primary variables for further processing in the primary language. In databases, cursor is a very important concept. A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records.

SQL> declare 2 v_1 varchar2 (200); 3 cursor c_1 is 4 select table_name from dba_tables where owner = 'hr 'and tablespace_name = 'example'; 5 begin 6 open c_1; 7 fetch c_1 into v_1; 8 while c_1 % found 9 loop 10 execute immediate 'alter table hr. '| v_1 | 'move tablespace tbs_apple'; 11 fetch c_1 into v_1; 12 end loop; 13 close c_1; 14 * end; 15/PL/SQL procedure successfully completed. step 4: Verify the result

SQL> select table_name from dba_tables where owner = 'hr' and tablespace_name = 'tbs _ apple'; TABLE_NAME ---------------------------- REGIONS LOCATIONS JOBS EMPLOYEES JOB_HISTORY 6 rows selected. note: After the user's table is migrated, check whether the index in the table needs to be migrated. Also, modify the default permanent tablespace as needed, otherwise, the new table is stored in the old tablespace. Other objects owned by the user, such as views, triggers, processes, and packages, are stored in the data dictionary, migration is not required!

Author: yueda tianchong"