connect target /
RMAN> restore controlfile from 'Z:\backup\LICS_14329_20240701';
RMAN> alter database mount;
RMAN> catalog start with 'Z:\backup';

alter database datafile 14 offline;


recover database using backup controlfile;
recover database using backup controlfile until cancel;
alter database clear unarchived logfile group 2 ;
alter system SET DB_RECOVERY_FILE_DEST_SIZE = 50G SCOPE=BOTH SID='*';
alter database open resetlogs;




Alter system set db_recovery_file_dest=' ' scope=both;

select count(*) from dba_tables where tablespace_name = 'DMSPACE';

        COUNT(*)
----------------
             367

TABLESPACE_NAME                AUT      MAX_TS_SIZE  MAX_TS_PCT_USED     CURR_TS_SIZE     USED_TS_SIZE      TS_PCT_USED     FREE_TS_SIZE      TS_PCT_FREE
------------------------------ --- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
DMSPACE                        YES           356348               81           312293           290389               93            21904                7

CREATE TABLESPACE DMSPACE_tmp
  DATAFILE 'e:\oracle\lisp\DMSPACE_00.dbf'
  SIZE 1G AUTOEXTEND ON MAXSIZE 30G;


ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_01.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_02.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_03.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_04.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_05.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_06.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_07.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_08.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_09.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_10.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_11.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;
ALTER TABLESPACE DMSPACE_tmp ADD DATAFILE 'e:\oracle\lisp\DMSPACE_12.dbf'   SIZE 1G AUTOEXTEND ON MAXSIZE 30G;

ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_00.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_00.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_01.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_01.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_02.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_02.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_03.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_03.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_04.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_04.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_05.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_05.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_06.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_06.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_07.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_07.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_08.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_08.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_09.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_09.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_10.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_10.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_11.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_11.dbf';
ALTER DATABASE MOVE DATAFILE 'e:\oracle\lisp\DMSPACE_12.dbf' TO 'D:\oracle\oradata\LICS\DATAFILE\DMSPACE_12.dbf';




spool Z:\oracle\scripts\tables.sql  
select 'alter table ' || owner || '.' ||table_name ||' move tablespace DMSPACE_tmp;' from dba_tables where tablespace_name = 'DMSPACE';

spool Z:\oracle\scripts\move.log  

@Z:\oracle\scripts\move.sql 

spool off

1) Connect to SQLPlus as sys as sysdba:

SQL> conn sys as sysdba

2) Execute the following script:

SQL> alter system archive log stop;

3) Shutdown the database:

SQL> shutdown immediate

4) Startup in exclusive mount mode. 

SQL> startup mount restrict

5) Disable the archive log mode, and open the database.

SQL> alter database noarchivelog;
SQL> alter database open;

https://stackoverflow.com/questions/51562647/how-to-move-schema-objects-to-a-different-tablespace

exp userid=system/bisnode file=Z:\backup\dumfile.dmp log=logfile.txt tables=LIS_TEST.PLAN_TABLE,LIS.PLAN_TABLE consistent=y buffer=1024000

SQL> host exp userid=system/bisnode file=Z:\backup\dumfile.dmp log=logfile.txt tables=LIS_TEST.PLAN_TABLE,LIS.PLAN_TABLE consistent=y buffer=1024000

Export: Release 11.2.0.3.0 - Production on Wed Jul 3 10:37:51 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to LIS_TEST
. . exporting table                     PLAN_TABLE          0 rows exported
EXP-00091: Exporting questionable statistics.
Current user changed to LIS
. . exporting table                     PLAN_TABLE          0 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

SQL>

USERNAME
------------------------------
lis_test
DM_QUARTZ_PROCESS
MIGRATION_USER
EZPUBLISH
DELIVERY
DMSOLID
LIS_TEST
LIS
select 'ALTER USER ' || username || ' DEFAULT tablespace dmspace_tmp;' from dba_users where DEFAULT_TABLESPACE = 'DMSPACE';

ALTER USER LIS DEFAULT tablespace dmspace_tmp;
ALTER USER DM_QUARTZ_PROCESS DEFAULT tablespace dmspace_tmp;
ALTER USER MIGRATION_USER DEFAULT tablespace dmspace_tmp;
ALTER USER EZPUBLISH DEFAULT tablespace dmspace_tmp;
ALTER USER DELIVERY DEFAULT tablespace dmspace_tmp;
ALTER USER DMSOLID DEFAULT tablespace dmspace_tmp;
ALTER USER LIS_TEST DEFAULT tablespace dmspace_tmp;
ALTER USER LIS DEFAULT tablespace dmspace_tmp;

select OWNER, segment_name, sum(blocks) from dba_segments where tablespace_name = 'DMSPACE' group by owner, segment_name;

PURGE DBA_RECYCLEBIN



SQL> SELECT ts# "TSN" FROM v$datafile WHERE file#=14;

no rows selected

SQL> SELECT tablespace_name FROM dba_data_files WHERE file_id=14;

no rows selected

SQL> SELECT * FROM dba_extents WHERE file_id=14;

no rows selected

SQL>

TABLESPACE tablespace


ALTER TABLE lis.TBLSCORERESULT storage TABLESPACE tablespace;

select dbms_metadata.get_ddl('TABLE', 'TBLSCORERESULT', 'LIS') from dual;
from user_tables;

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_TBLSCORERESULT','LIS') FROM dual;

CREATE UNIQUE INDEX "LIS"."PK_TBLSCORERESULT" ON "LIS"."TBLSCORERESULT" ("NSCORERESULTID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DMSPACE1";
  ALTER INDEX "LIS"."PK_TBLSCORERESULT"  UNUSABLE;


TABLESPACE_NAME
ALTER INDEX lis.PK_TBLSCORERESULT UNUSABLE
/

DROP INDEX lis.PK_TBLSCORERESULT
/
alter index lis.PK_TBLSCORERESULT rebuild tablespace DMSPACE1;

select owner, index_name from all_indexes where TABLESPACE_NAME='_$deleted$6$0'

ALTER TABLE <TABLE_NAME> MOVE TABLESPACE TEST_TBL;
ALTER INDEX lis.PK_TBLSCORERESULT REBUILD TABLESPACE DMSPACE1;
EXEC DBMS_STATS.gather_schema_stats('LIS', estimate_percent => 25, cascade => TRUE);


/


-----------------

set pages 999
set lines 400

spool e:\oracle\tables.sql

select 'alter table ' || do.owner || '.' || do.object_name ||
       ' move tablespace DMSPACE_tmp;' as cmd_to_invoke 
from dba_objects do, dba_segments ds, dba_tables dt
where do.owner=ds.owner and do.owner=dt.owner
  and do.object_name = ds.segment_name and do.object_name=dt.table_name
  and dt.iot_name is null and do.object_type='TABLE'
  and ds.tablespace_name = 'DMSPACE';

spool e:\oracle\index1.sql

select 'alter index ' || do.owner || '.' || do.object_name || ' rebuild tablespace DMSPACE_tmp;' as cmd
from dba_objects do, dba_segments ds, dba_indexes di
where do.owner=ds.owner and do.owner=di.owner
  and do.object_type = 'INDEX'
  and do.object_name = ds.segment_name and do.object_name = di.index_name 
  and ds.tablespace_name='DMSPACE';

spool g:\oracle\lob.sql
select distinct 'alter table ' || dtc.owner || '.' || dtc.table_name || 
   ' move lob(' || column_name || ')' ||
   ' store as (tablespace DMSPACE_tmp);' as cmd_to_invoke
from dba_tab_columns dtc,
     dba_tables dt,
     dba_indexes di
where dt.owner=dtc.owner and dt.owner=di.owner
  and dt.table_name=di.table_name and dt.table_name=dtc.table_name
  and dtc.data_type like '%LOB%'
  and di.index_name in 
     (select segment_name from dba_segments inner 
      where inner.owner=dt.owner and tablespace_name='DMSPACE') 
  ;

CREATE OR REPLACE VIEW DBA_TABLESPACE_OBJECTS AS
SELECT OWNER, TABLE_NAME "OBJECT_NAME", 'TABLE' "OBJECT_TYPE", SEGMENT_CREATED, TABLESPACE_NAME FROM DBA_TABLES
UNION
SELECT OWNER, INDEX_NAME "OBJECT_NAME", 'INDEX' "OBJECT_TYPE", SEGMENT_CREATED, TABLESPACE_NAME FROM DBA_INDEXES
UNION
SELECT OWNER, SEGMENT_NAME "OBJECT_NAME", 'LOB' "OBJECT_TYPE", SEGMENT_CREATED, TABLESPACE_NAME FROM DBA_LOBS
UNION
SELECT TABLE_OWNER, TABLE_NAME||'.'||PARTITION_NAME "OBJECT_NAME", 'TABLE PARTITION' "OBJECT_TYPE", SEGMENT_CREATED, TABLESPACE_NAME FROM DBA_TAB_PARTITIONS
UNION
SELECT INDEX_OWNER, INDEX_NAME||'.'||PARTITION_NAME "OBJECT_NAME", 'INDEX PARTITION' "OBJECT_TYPE", SEGMENT_CREATED, TABLESPACE_NAME FROM DBA_IND_PARTITIONS
UNION
SELECT TABLE_OWNER, TABLE_NAME||'.'||LOB_PARTITION_NAME "OBJECT_NAME", 'LOB PARTITION' "OBJECT_TYPE", SEGMENT_CREATED, TABLESPACE_NAME FROM DBA_LOB_PARTITIONS;

spool Z:\oracle\scripts\index1.sql
select 'alter index ' || OWNER || '.' || OBJECT_NAME || ' rebuild tablespace DMSPACE_tmp;' from sys.dba_tablespace_objects where tablespace_name = 'DMSPACE';