Migrate Tables and Indexes from Tablespace_01 to Tablespace_02

=================================
General
=================================
Sometimes it is needed to move segments from one Tablespace to another.

Usage:

sqlplus system/password@igt @main_tables_flow.sql
=================================
Move Tables
=================================
main_tables_flow.sql
set_params.sql
chk_sessions.sql
tbs_objects_stats.sql
cre_new_table_tbs.sql
move_tables.sql
drop_old_table_tbs.sql

=================================
Move Indexes
=================================

main_indexes_flow.sql

set_params.sql

chk_sessions.sql
tbs_objects_stats.sql
cre_new_index_tbs.sql
move_indexes.sql
drop_old_index_tbs.sql

=================================
Code for Tables
=================================

main_tables_flow.sql
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_table_tbs.sql
@./move_tables.sql
@./drop_old_table_tbs.sql


=================================
Common Code
=================================
----------------------------------

set_params.sql
----------------------------------

define table_from_tbs=IGT_TABLE4
define table_to_tbs=IGT_TABLE2

define index_from_tbs=IGT_INDEX4
define index_to_tbs=IGT_INDEX2

define table_to_datafile=/oracle_db/db1/db_igt/ora_igt_table_02.dbf
define index_to_datafile=/oracle_db/db1/db_igt/ora_igt_index_02.dbf

define owner_name=V500_125

----------------------------------
chk_sessions.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
COL sessions_count FOR A20
SET HEADING ON
SET FEEDBACK ON
SET NEWPAGE NONE
SET VERIFY OFF


spool tbs_objects_stats.log append
PROMPT
PROMPT ==========================================
PROMPT Checking Sessions of User '&&owner_name'
PROMPT ==========================================
PROMPT 
SELECT schemaname as SCHEMA_NAME,
       'Current Sessions: '||COUNT(*) AS sessions_count
  FROM V$SESSION 
 WHERE schemaname <> 'SYS'
   AND (schemaname IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY schemaname;
PROMPT 
SET HEADING ON
PROMPT ==========================================
spool off

----------------------------------
tbs_objects_stats.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
SET HEADING ON
SET FEEDBACK OFF
SET NEWPAGE NONE

spool tbs_objects_stats.log append
PROMPT
PROMPT ==========================================
SET HEADING OFF
SELECT 'Run Date: '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;
SET HEADING ON
PROMPT ==========================================
PROMPT Migrating for Owner: &&owner_name
PROMPT Migrating Tables from &&table_from_tbs to &&table_to_tbs
PROMPT Migrating Indexes from &&index_from_tbs to &&index_to_tbs
PROMPT ==========================================

PROMPT ==========================================
PROMPT Stats For &&owner_name and &&table_from_tbs and &&index_from_tbs
PROMPT ==========================================
SELECT owner||'.'||tablespace_name||'.'||'TABLES' as record_type, COUNT(*) 
 FROM DBA_SEGMENTS
 WHERE tablespace_name = '&&table_from_tbs'
   AND segment_type = 'TABLE'  
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'TABLES'
UNION ALL
SELECT table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES' as record_type, COUNT(*) 
FROM  DBA_TAB_PARTITIONS 
WHERE tablespace_name = '&&table_from_tbs' 
  AND ( table_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES'
UNION ALL
SELECT owner||'.'||tablespace_name||'.'||'INDEXES', COUNT(*)
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&index_from_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'INDEXES'
UNION ALL
SELECT index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES', COUNT(*)
 FROM DBA_IND_PARTITIONS 
WHERE tablespace_name = '&&index_from_tbs'
  AND ( index_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES';

PROMPT ==========================================
PROMPT Stats For &&owner_name and &&table_to_tbs and &&index_to_tbs
PROMPT ==========================================
SELECT owner||'.'||tablespace_name||'.'||'TABLES' as record_type, COUNT(*) 
 FROM DBA_SEGMENTS
 WHERE tablespace_name = '&&table_to_tbs'
   AND segment_type = 'TABLE'  
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'TABLES'
UNION ALL
SELECT table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES' as record_type, COUNT(*) 
FROM  DBA_TAB_PARTITIONS 
WHERE tablespace_name = '&&table_to_tbs' 
  AND ( table_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES'
UNION ALL
SELECT owner||'.'||tablespace_name||'.'||'INDEXES', COUNT(*)
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&index_to_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'INDEXES'
UNION ALL
SELECT index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES', COUNT(*)
 FROM DBA_IND_PARTITIONS 
WHERE tablespace_name = '&&index_to_tbs'
  AND ( index_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES';
PROMPT
spool off;

=================================
Migrate Tables
=================================

main_tables_flow.sql
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_table_tbs.sql
@./move_tables.sql
@./drop_old_table_tbs.sql

----------------------------------
cre_new_table_tbs.sql
----------------------------------
----------------------------
--Create new Tablespace for Tables
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK ON;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT CREATE TABLESPACE &&table_to_tbs DATAFILE '&&table_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M...
CREATE TABLESPACE &&table_to_tbs DATAFILE '&&table_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M;

----------------------------------
move_tables.sql
----------------------------------
----------------------------
--Generate sql files
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_tables_tbs.sql
SELECT 'PROMPT Start Moving Tables of Owner &&owner_name from Tablespace &&table_from_tbs to Tablespace &&table_to_tbs...' FROM DUAL;
SELECT 'ALTER TABLE '||owner||'.'||segment_name||' MOVE TABLESPACE &&table_to_tbs;' 
  FROM DBA_SEGMENTS
 WHERE tablespace_name = '&&table_from_tbs'
   AND segment_type = 'TABLE'  
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );
SELECT 'PROMPT Finished Moving Tables...' FROM DUAL;
spool off;


@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_tables_tbs_part2.sql
SELECT 'PROMPT Start Moving Tables of Owner &&owner_name from Tablespace &&table_from_tbs to Tablespace &&table_to_tbs...' FROM DUAL;
SELECT 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE &&table_to_tbs;' 
  FROM DBA_TABLES
 WHERE tablespace_name = '&&table_from_tbs'
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );
SELECT 'PROMPT Finished Moving Tables...' FROM DUAL;
spool off;
@gen_move_tables_tbs_part2

spool gen_move_part_tables_tbs.sql
SELECT 'PROMPT Start Moving Partitioned Tables of Owner &&owner_name from Tablespace &&table_from_tbs to Tablespace &&table_to_tbs...' FROM DUAL;
SELECT 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE PARTITION '||PARTITION_NAME||' TABLESPACE &&table_to_tbs;' 
  FROM  DBA_TAB_PARTITIONS 
 WHERE tablespace_name = '&&table_from_tbs' 
   AND ( table_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );  
SELECT 'PROMPT Finished Moving Partitioned Tables...' FROM DUAL;
spool off;

----------------------------
--execute Generated Files
----------------------------
@gen_move_tables_tbs.sql
@gen_move_part_tables_tbs.sql

----------------------------------
drop_old_table_tbs.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
SET HEADING ON
SET FEEDBACK OFF
SET NEWPAGE NONE

----------------------------
--Manual Check
----------------------------
ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT
SELECT COUNT(*) AS OBJECTS_ON_OLD_TBS FROM DBA_SEGMENTS WHERE tablespace_name = '&&table_from_tbs';
--Expected Result: 0
PROMPT
PROMPT
PROMPT DROP TABLESPACE &&table_from_tbs INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE &&table_from_tbs INCLUDING CONTENTS AND DATAFILES;
PROMPT

=================================
Code for Indexes
=================================
----------------------------------

main_indexes_flow.sql
----------------------------------
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_index_tbs.sql
@./move_indexes.sql
@./drop_old_index_tbs.sql


=================================
Migrate Indexes
=================================
----------------------------------
main_indexes_flow.sql
----------------------------------
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_index_tbs.sql
@./move_indexes.sql
@./drop_old_index_tbs.sql

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

cre_new_index_tbs.sql
----------------------------------

----------------------------
--Create new Tablespace for Indexes
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK ON;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT CREATE TABLESPACE &&index_to_tbs DATAFILE '&&index_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M...

CREATE TABLESPACE &&index_to_tbs DATAFILE '&&index_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M;

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

move_indexes.sql
----------------------------------
----------------------------
--Generate sql files
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_indexes_tbs.sql
SELECT 'PROMPT Start Moving Indexes Owned by &&owner_name from Tablespace &&index_from_tbs to &&index_to_tbs ...' FROM DUAL;
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE &&index_to_tbs;' 
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&index_from_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );   
SELECT 'PROMPT Finished Moving Indexes...' FROM DUAL;
spool off

spool gen_move_part_indexes_tbs.sql
SELECT 'PROMPT Start Moving Partitioned Indexes Owned by &&owner_name from Tablespace &&index_from_tbs to &&index_to_tbs ...' FROM DUAL;
SELECT 'ALTER INDEX '||index_owner||'.'||index_name ||' REBUILD PARTITION '||partition_name||' TABLESPACE &&index_to_tbs;'
 FROM DBA_IND_PARTITIONS 
WHERE tablespace_name = '&&index_from_tbs'
  AND ( index_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );  
SELECT 'PROMPT Finished Moving Partitioned Indexes...' FROM DUAL;
spool off;

----------------------------
--execute Generated Files
----------------------------
@gen_move_indexes_tbs.sql
@gen_move_part_indexes_tbs.sql

----------------------------------
drop_old_index_tbs.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
SET HEADING ON
SET FEEDBACK OFF
SET NEWPAGE NONE

----------------------------
--Manual Check
----------------------------
ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT
SELECT COUNT(*) AS OBJECTS_ON_OLD_TBS FROM DBA_SEGMENTS WHERE tablespace_name = '&&index_from_tbs';
--Expected Result: no rows selected
PROMPT
PROMPT
PROMPT DROP TABLESPACE &&index_from_tbs INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE &&index_from_tbs INCLUDING CONTENTS AND DATAFILES;
PROMPT




=================================
When dropping the default tablespace
=================================

DROP TABLESPACE IGT_TABLE INCLUDING CONTENTS AND DATAFILES;

ORA-12919: Can not drop the default permanent tablespace

CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db1/db_igt/igt_table_temp_01.dbf' size 10M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE_TEMP;

DROP TABLESPACE IGT_TABLE INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE IGT_TABLE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 30000M;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE;


DROP TABLESPACE IGT_TABLE_TEMP INCLUDING CONTENTS AND DATAFILES;


=================================
Moving SubPartitions
=================================

move_subpartitions.sql

set trimspool on

set timing off

SET LINESIZE 200

SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;

SET ECHO OFF

SET FEEDBACK OFF

SET TERM OFF

SET NEWPAGE 0

SET SPACE 0

SPOOL gen_move_subpartitions.sql

SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE '||'IGT_TABLE2' ;' 

FROM DBA_TAB_SUBPARTITIONS

WHERE table_owner = 'LAB_QANFV_ALLQQ'

  AND tablespace_name = 'IGT_TABLE';

spool off;
@gen_move_subpartitions.sql

=================================
Moving LOB Segments
=================================
The syntax is:

ALTER TABLE OWNER.TABLE_NAME MOVE LOB(LOB_COLUMN) STORE AS (TABLESPACE NEW_TABLESPACE_NAME);

SELECT table_name FROM DBA_LOBS 
WHERE segment_name = 'SYS_LOB0000220773C00009$$';

For example:
ALTER TABLE SYS.SERVERERROR_LOG move lob (SQL_STATEMENT_ALL) STORE 
AS SYS_LOB0000220773C00009$$ (tablespace DWH_TABLE);

set trimspool on;

set timing off;

SET LINESIZE 200;

SET PAGESIZE 0;
SET HEADING OFF;
SET VERIFY OFF;

SET ECHO OFF;

SET FEEDBACK OFF;

SET TERM OFF;

SET NEWPAGE 0;

SET SPACE 0;

SPOOL move_lob.sql

SELECT 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB ('||column_name||') STORE AS '||segment_name||' (tablespace IGT_TABLE2);' AS sql_cmd

FROM DBA_LOBS 

WHERE 1=1

  AND tablespace_name = 'IGT_TABLE';

spool off

@move_lob.sql

=================================
Move Local Indexes, in the tables TBS
=================================

move_local_indexes_tbs.sql

@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_local_indexes_tbs.sql
SELECT 'PROMPT Start Moving Indexes Owned by &&owner_name from Tablespace &&table_from_tbs to &&table_to_tbs ...' FROM DUAL;
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE &&table_to_tbs;' 
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&table_from_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );   
SELECT 'PROMPT Finished Moving Indexes...' FROM DUAL;
spool off

=================================
Move Indexes on IOT
=================================

For example:

SELECT owner, index_name, index_type, table_name, tablespace_name 

FROM DBA_INDEXES 

WHERE INDEX_TYPE LIKE 'IOT%' 

  AND owner = 'LAB_QANFV_ALLQQ';

spool gen_move_iot_indexes_tbs.sql

SELECT 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE  '||to_tbs||';'

FROM DBA_INDEXES 

WHERE INDEX_TYPE LIKE 'IOT%' 

  AND owner = 'LAB_QANFV_ALLQQ'

  AND tablespace_name = 'from_tbs';

spool off

@gen_move_iot_indexes_tbs.sql

=================================
Handle Temporary segments
=================================

Search for TEMPORARY segments

SELECT tablespace_name, owner, segment_name ,sum(bytes/1024/1024) 

FROM DBA_SEGMENTS

WHERE segment_type = 'TEMPORARY' 

GROUP BY tablespace_name, owner, segment_name;

To fix:

ALTER TABLESPACE IGT_TABLE COALESCE;

=================================
Rebuild Indexes
=================================

Rebuild Indexes

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET VERIFY OFF

SET FEEDBACK OFF

spool gen_rebuild_indexes_tbs.sql

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES 

WHERE tablespace_name = 'IGT_INDEX'

  AND owner = 'MY_SCHEMA'

  AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES);

spool off

spool gen_rebuild_part_indexes_tbs.sql

SELECT 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME ||' REBUILD PARTITION '||PARTITION_NAME||' ;' 

FROM  DBA_SEGMENTS 

WHERE SEGMENT_TYPE LIKE '%INDEX%'  

  AND owner = 'MY_SCHEMA'

  AND PARTITION_NAME IS NOT NULL;  

spool off

SET FEEDBACK ON

@rebuild_indexes_tbs.sql

@move_part_indexes_tbs.sql

spool gen_rebuild_unusable.sql

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES 

WHERE tablespace_name = 'IGT_INDEX'

  AND owner = 'MY_SCHEMA'

  AND status <> 'VALID'

  AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES);

spool off

@gen_rebuild_unusable.sql

spool gen_list_unusable.sql

SELECT 'Unusable Indexes After Rebuild' FROM DUAL;'

SELECT OWNER||'.'||INDEX_NAME||' - '||STATUS FROM DBA_INDEXES 

WHERE tablespace_name = 'IGT_INDEX'

  AND owner = 'MY_SCHEMA'

  AND status <> 'VALID'

  AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES);

spool off

SET FEEDBACK ON

@gen_list_unusable.sql

=================================
Gather Schema Stats
=================================

Gather schema stats

BEGIN

  DBMS_STATS.gather_schema_stats(ownname => 'MY_SCHEMA', estimate_percent => 15);

END;

/

Manual Option

SET LINESIZE 200

SET PAGESIZE 0

SET FEEDBACK OFF

spool rebuild_indexes.sql

--Regular Index

SELECT 'PROMPT HANDLE INDEX '||index_name||CHR(10)||'ALTER INDEX ' ||USER_INDEXES.INDEX_NAME||' REBUILD ONLINE;'

FROM USER_INDEXES

ORDER BY INDEX_NAME;

--Partitioned Index

spool rebuild_partitioned_indexes.sql

SELECT 'PROMPT HANDLE Partitioned INDEX '||index_name||CHR(10)||'ALTER INDEX ' ||USER_IND_PARTITIONS.index_name||' REBUILD PARTITION '||USER_IND_PARTITIONS.partition_name||' ONLINE;'

FROM USER_IND_PARTITIONS, 

     USER_PART_INDEXES

WHERE USER_PART_INDEXES.table_name = 'SFI_CUSTOMER_OPTIONS'

AND USER_IND_PARTITIONS.index_name = USER_PART_INDEXES.index_name

ORDER BY USER_IND_PARTITIONS.index_name, USER_IND_PARTITIONS.partition_name;

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/data/ora_igt_table_01.dbf' RESIZE 1000M;

Code Example. Linux bash, sqlplus,.write log

========================
General
========================
Execute from crontab a Linux script, that call sqlplus code.

Very important to set all the environment variables in script, because in crontab they would be missing.

========================
Code
========================

run_reports_job.sh 

run_reports_job.sql

write_log.sh

ctontab task
5 1 * * * /software/oracle/oracle/scripts/run_reports_job.sh

run_reports_job.sh 

#!/bin/bash
. /software/oracle/oracle/.bash_profile
. /software/oracle/oracle/.set_profile

. /etc/sh/orash/oracle_login.sh igt

WORKDIR=/software/oracle/oracle/scripts
cd ${WORKDIR}
./write_log.sh $0
sqlplus BGD_ROBIQ_IRM_REPORTS/BGD_ROBIQ_IRM_REPORTS@igt  @run_reports_job.sql
exit 0

write_log.sh
#!/bin/bash

PROGRAM_NAME=$1
WORKDIR=/software/oracle/oracle/scripts
cd ${WORKDIR}
RUN_DATE=`date +"%Y%m%d_%H%M%S"`
DELIMITER="============================================="
BASENAME=`basename $PROGRAM_NAME`
LOG_FILE=`echo $BASENAME | sed s/.sh/.log/`

touch $LOG_FILE
echo $DELIMITER >> $LOG_FILE
echo "Running $PROGRAM_NAME at $RUN_DATE" >>  $LOG_FILE
echo $DELIMITER >> $LOG_FILE


exit 0

run_reports_job.sql 
BEGIN
 DBMS_JOB.run(642);
 commit;
END;
/
EXIT;

UNDO Tablespace. Move from one location to another; Add Datafile.

=======================
General
=======================
Due to space shortage on disk need to move UNDO Tablespace to another location.

=======================
Steps
=======================

To add datafile to UNDO TBS:

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/oracle_db/db1/db_igt/data/ora_undotbs_02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE 32000M; 

Increase size of existing datafile

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_01.dbf' AUTOEXTEND ON MAXSIZE 4000M;

=======================
Steps
=======================

See current status

show parameter undo
NAME                                 TYPE                 VALUE
------------------------------------ -------------------- ----------
undo_management                      string               AUTO
undo_retention                       integer              3600
undo_tablespace                      string               UNDOTBS

Create a new UNDO Tablespace, alter system to use it,and from the old one
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle_db/db2/db_igt/igt/ora_undotbs2_01.dbf' SIZE 10000M;

ALTER DATABASE DATAFILE '/oracle_db/db2/db_igt/igt/ora_undotbs2_01.dbf' AUTOEXTEND ON MAXSIZE 30000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

Now, either update SPFILE to use the new name UNDOTBS2 via:
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

Or move UNDO tablespace back to the old name

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oracle_db/db2/db_igt/tbs/ora_undotbs_01.dbf' SIZE 10000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS;

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

=======================
Additional Info
=======================
Inside spfile, the UNDO Tablespace name is stored, but not the actual file location.

oracle@some_server:/software/oracle/admin/igt/pfile>% strings spfileigt.ora
igt.__db_cache_size=3489660928
igt.__java_pool_size=67108864
igt.__large_pool_size=67108864
igt.__oracle_base='/software/oracle'#ORACLE_BASE set from environment
igt.__pga_aggregate_target=2952790016
igt.__sga_target=5637144576
igt.__shared_io_pool_size=0
igt.__shared_pool_size=2013265920
igt.__streams_pool_size=67108864
*.archive_lag_target=1800
*.audit_file_dest='/software/oracle/admin/igt/adump'
*.audit_trail='none'
*.compatible='11.1.0.0.0'
*.control_files='/oracle_db/db1/db_igt/
ora_control_01.ctl','/oracle_db/db1/db_igt/ora_control_02.ctl','/oracle_db/db1/db_igt/ora_control_03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='igt'
*.diagnostic_dest='/software/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=igtXDB)'
*.fast_start_mttr_target=180
*.filesystemio_options='asynch'
*.log_archive_dest_1='location=/oracle_db/db2/db_igt/arch'
*.log_archive_format='arch%T_%s_%r.arc'
*.memory_target=8589934592
*.nls_length_semantics='char'
*.open_cursors=300
*.open_li
nks=12
*.os_authent_prefix=''
*.processes=600
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.SESSION_CACHED_CURSORS=100
*.undo_retention=3600

*.undo_tablespace='UNDOTBS'

Error - UNDO Segment cannot be dropped

SELECT a.name,b.status 

FROM   v$rollname a,v$rollstat b

WHERE  a.usn = b.usn

AND    a.name IN ( 

          SELECT segment_name

          FROM dba_segments 

          WHERE tablespace_name = 'UNDOTBS1'

         ); 

 

NAME                           STATUS

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

_SYSSMU7_3211463042$           PENDING OFFLINE

_SYSSMU18_1767712819$          PENDING OFFLINE

_SYSSMU21_4188676727$          PENDING OFFLINE

SELECT name, xacts active_transactions

FROM  v$rollname, v$rollstat 

WHERE status = 'PENDING OFFLINE' 

  AND v$rollname.usn = v$rollstat.usn; 

NAME                           ACTIVE_TRANSACTIONS

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

_SYSSMU7_3211463042$                             0

_SYSSMU18_1767712819$                            0

_SYSSMU21_4188676727$                            0

In case there were active transactions - this SL would have shown the active sid+serial# to be killed.

SELECT a.name, b.status, d.username, d.sid, d.serial#

FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN ( 

          SELECT segment_name

          FROM dba_segments 

          WHERE tablespace_name = 'UNDOTBS1'

         ); 

NAME                  STATUS          USERNAME       SID    SERIAL#

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

_SYSSMU46_132405334$  PENDING OFFLINE LAB_USER      3988      18531

ALTER SYSTEM KILL SESSION '3988,18531' IMMEDIATE;

>System altered.

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

>Tablespace dropped.

See current UNDO usage

SELECT * FROM V$UNDOSTAT;

SELECT * FROM V$TRANSACTION;

SELECT * FROM DBA_UNDO_EXTENTS;

SELECT * FROM DBA_HIST_UNDOSTAT;

See current UNDO info

SELECT tablespace_name, contents 

FROM DBA_TABLESPACES 

WHERE contents = 'UNDO';

SELECT tablespace_name, SUBSTR(file_name,1,60) 

  FROM DBA_DATA_FILES 

WHERE tablespace_name LIKE 'UNDO%' order by 1,2;

SELECT

  DDF.tablespace_name,

  SUM(DDF.bytes)/(1024*1024) total_space_MB,

  round(FREE_SPACE.free,2) free_space_mb,

  round(FREE_SPACE.free/(sum(DDF.bytes)/(1024*1024))* 100,2) pct_free

 FROM DBA_DATA_FILES DDF,

  (select tablespace_name,SUM(bytes)/(1024*1024) free  

     FROM DBA_FREE_SPACE GROUP BY tablespace_name) FREE_SPACE

 WHERE DDF.tablespace_name = FREE_SPACE.tablespace_name(+)

   AND DDF.tablespace_name LIKE 'UNDO%'

GROUP BY DDF.tablespace_name,FREE_SPACE.free;

SELECT tablespace_name, status, COUNT(segment_name), SUM(bytes/1024/1024)

FROM DBA_UNDO_EXTENTS 

GROUP BY tablespace_name, status;