Virtual machine to build test environment solution
The script involved in the picture is at the bottom of the picture.
[script 1].
Select df.tablespace_name Tablespace name
Totalspace "Total Space M"
Freespace "residual space M"
Round ((1-freespace / totalspace) * 100,2) "usage%"
From (select tablespace_name, round (sum (bytes) / 1024 / 1024) totalspace
From dba_data_files
Group by tablespace_name) df
(select tablespace_name, round (sum (bytes) / 1024 / 1024) freespace
From dba_free_space
Group by tablespace_name) fs
Where df.tablespace_name = fs.tablespace_name
Order by df.tablespace_name
[script 2].
Create tablespace test_space datafile'e:\ APP\ ADMINISTRATOR\ ORADATA\ DBSERVER\ test_space.dbf'size 3G
[script 3].
-- the following generation scripts (adjust the scripts to be generated as needed)
SQL > set heading off
SQL > set echo off
SQL > set feedback off
SQL > set termout on
SQL > spool d:\ move_test.sql
-- Table to be moved
Select DISTINCT 'alter table' | segment_name | | 'move tablespace test_space;' from dba_extents where segment_type='TABLE' and file_id=37
-- Index to be moved
Select DISTINCT 'alter index' | | owner | |'. | | segment_name | | 'rebuild tablespace test_space;'
From dba_extents
Where segment_type = 'INDEX'
And file_id = 37
-- move partition tables
SQL > select DISTINCT 'alter table' | | segment_name | | 'move partition' | | partition_name | | 'tablespace test_space;' from dba_extents where segment_type='TABLE PARTITION' and file_id=18
-- Mobile Partition Index
SQL > select DISTINCT 'alter index' | | segment_name | | 'rebuild partition' | | partition_name | | 'tablespace test_space;' from dba_extents where segment_type='INDEX PARTITION' and file_id=18
SQL > spool off
-- ps: then execute move_test.sql, making sure that test_space has enough space to hold the data.
[script 4].
-- the following generation scripts (adjust the scripts to be generated as needed)
SQL > set heading off
SQL > set echo off
SQL > set feedback off
SQL > set termout on
SQL > spool d:\ move_test2.sql
-- Table
Select DISTINCT 'alter table' | | segment_name | | 'move tablespace TSP_ORDADM;' from dba_extents where segment_type='TABLE' and file_id=43
-Index
Select DISTINCT 'alter index' | | owner | |'. | | segment_name | | 'rebuild tablespace TSP_ORDADM;'
From dba_extents
Where segment_type = 'INDEX'
And file_id = 43
SQL > spool off
[script 5].
-- move to transit tablespace TEST_SPACE
ALTER TABLE LAB.LAB_IMAGE_RESULT MOVE TABLESPACE TEST_SPACE LOB (IMAGE) STORE AS (TABLESPACE TEST_SPACE)
-- move back to the original tablespace TSP_LAB
ALTER TABLE LAB.LAB_IMAGE_RESULT MOVE TABLESPACE TSP_LAB LOB (IMAGE) STORE AS (TABLESPACE TSP_LAB)
[script 6].
-1. You can help copy in the sqlplus tool
-- create an object first
Select dbms_metadata.get_ddl ('TABLE','TEST1','TEST') from dual
-- 2. Can be inserted through dblink
-- test/oracle@king "king" connection string locally in the form of a network login
-- then insert the data
COPY FROM test/oracle@king INSERT test.TEST1 (SERIES_NO,TABLE_CODE,TABLE_NAME,MANAGER_CLASS,REMARK) USING SELECT * FROM comm.FEE_TABLE_DICT@tsj-- fetch source tsj is dblink
After the 3.TEST.TEST1 table synchronizes data in the specified tablespace
The subsequent alter table...rename to..., then drop the old table on the source tablespace (after the tablespace resize, do a reverse operation).
Dbtiger
Good luck!
2018pr 3pr 30 in Sanya, Hainan
