Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.