In SAP with Oracle installed, you can drop a tablespace using BRTOOLS. You can perform the activity (example), if the tablespace is no longer required after upgrade or reorganization.
Before you can proceed the dropping steps, ensure below steps are taken.
• No segments, indexes and extents been under the tablespace. Please move all of them to another tablespace before you proceeding with below steps. This can be check from transaction code DB02 or from SQL queries.
select TABLESPACE_NAME, FILE_NAME, FILE_ID from DBA_DATA_FILES where TABLESPACE_NAME = 'PSAPSR3ABC';
select count(*) from DBA_INDEXS where TABLESPACE_NAME='PSAPSR3ABC';
If above step is checked. Now you can proceed to drop the tablespace.
1. Execute brtools command and select 2 – Space management.
> brtools ------------------------------------------------------------------------------- BR*Tools main menu 1 = Instance management 2 - Space management 3 - Segment management 4 - Backup and database copy 5 - Restore and recovery 6 - Check and verification 7 - Database statistics 8 - Additional functions 9 - Exit program -------------------------------------------------------------------------------
2. Select 3 – Drop tablespace.
------------------------------------------------------------------------------- Database space management 1 = Extend tablespace 2 - Create tablespace 3 - Drop tablespace 4 - Alter tablespace 5 - Alter data file 6 - Move data file 7 - Additional space functions 8 - Reset program status -------------------------------------------------------------------------------
3. Select 3 ~ Tablespace name to fill in the tablespace name which you are going to drop.
------------------------------------------------------------------------------- BRSPACE options for drop tablespace 1 - BRSPACE profile (profile) ...... [initSID.sap] 2 - Database user/password (user) .. [/] 3 ~ Tablespace name (tablespace) ... [PSAPSR3ABC] 4 - Confirmation mode (confirm) .... [yes] 5 - Scrolling line count (scroll) .. [20] 6 - Message language (language) .... [E] 7 - BRSPACE command line (command) . [-p initSID.sap -s 20 -l E -f tsdrop -t PSAPSR3ABC] -------------------------------------------------------------------------------
4. Select “c” to continue.
BR0259I Program execution will be continued... BR0291I BRSPACE will be started with options '-p initSID.sap -s 20 -l E -f tsdrop -t PSAPSR3ABC'
5. Again, select “c” to continue the execution.
BR0257I Your reply: 'c' BR0259I Program execution will be continued... ############################################################################### BR1001I BRSPACE 7.20 (25) BR1002I Start of BRSPACE processing: selmquwi.tsd 2013-06-25 07.14.28 BR0484I BRSPACE log file: /oracle/SID/sapreorg/abcdefr.tsd
6. If you still have files in the tablespace. As below example, the tablespace still having 2 files and in order to remove the files, you will need to add force option. Select 3 – Force tablespace drop (force). Put in as Yes.
------------------------------------------------------------------------------- Options for dropping of tablespace PSAPSR3ABC 1 * Number of files in tablespace (files) . [2] 2 * Total tablespace size in MB (size) .... [61440] 3 - Force tablespace drop (force) ......... [yes] 4 - SQL command (command) ................. [drop tablespace PSAPSR3ABC including contents and datafiles cascade constraints] -------------------------------------------------------------------------------
7. Choose “c” to continue. Wait the process to complete with below message.
BR1069I Tablespace PSAPSR3ABC dropped successfully
