Oracle alter table space, oracle ALTER TABLE

Oracle alter table space, oracle ALTER TABLE

1. Find out which tables have incorrect tablespaces.

select * from dba_tables where tablespace_name='TDB';

2. Move the tablespace in TDB to TDB2009.

Syntax: alter table table_name move tablespace tablespace_name; alter table tdb2009.ASSOC _ INFO move tablespace TDB2009; alter table tdb2009.BGUSERPOPD move tablespace TDB2009;

3. Change the index to another tablespace.

alter index PK_WS_ACCESS_LOG_DETAIL rebuild online nologging tablespace rpt03;

Change all indexes in one tablespace to another.

select 'alter index ' || index_name || ' move tablespace TDB2009;' from user_indexesselect 'alter index TDB2009.'||index_name||' rebuild online nologging tablespace TDB2009;' from user_indexes

4. Move the tablespace of the lob field to the new tablespace.

Syntax: alter table name move lob (lob field name) store as (new TABLE space name); alter table ws_access_log move lob (req_content) store as (TABLESPACE rpt03 ); alter table ws_access_log move lob (resp_content) store as (TABLESPACE rpt03 );

Remarks: Query

select * from dba_data_files;select * from dba_lobs where tablespace_name='USERS';select * from dba_segments where segment_name in('SYS_LOB0000335629C00004$$','SYS_LOB0000335629C00014$$','SYS_LOB0000335635C00006$$','SYS_LOB0000335635C00008$$');select dbms_metadata.get_ddl('TABLE','WS_ACCESS_LOG','USMM') from dual;

How Does ORACLE Change the tablespace of a table?

Alter table schema. AC01 move tablespace AA;
Rebuild AC01 table, specifying the tablespace AA
After expdp, specify the tablespace conversion REMAP_TABLESPACE = SYSTEM: AA when impdp is executed.


How Does oracle Change the user's default tablespace?

Alter user wy2014 default tablespace mytds01