SAP- Brtools – Tablespace deletion step by step

smdev:SMD:/home/orasmd> brtools
BR0651I BRTOOLS 7.00 (28)

BR0280I BRTOOLS time stamp: 2010-08-25 10.25.31
BR0656I Choice menu 1 – please make a selection
——————————————————————————-
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

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2010-08-25 10.25.35
BR0663I Your choice: ‘2’

BR0280I BRTOOLS time stamp: 2010-08-25 10.25.35
BR0656I Choice menu 5 – please make a selection
——————————————————————————-
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

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2010-08-25 10.25.39
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2010-08-25 10.25.40
BR0657I Input menu 83 – please check/enter input values
——————————————————————————-
BRSPACE options for drop tablespace

1 – BRSPACE profile (profile) …… [initSMD.sap]
2 – Database user/password (user) .. [/]
3 ~ Tablespace name (tablespace) … []
4 – Confirmation mode (confirm) …. [yes]
5 – Scrolling line count (scroll) .. [20]
6 – Message language (language) …. [E]
7 – BRSPACE command line (command) . [-p initSMD.sap -s 20 -l E -f tsdrop]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2010-08-25 10.25.49
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2010-08-25 10.25.49
BR0681I Enter string value for “tablespace” []:
PSAPSR3700
BR0280I BRTOOLS time stamp: 2010-08-25 10.26.08
BR0683I New value for “tablespace”: ‘PSAPSR3700’

BR0280I BRTOOLS time stamp: 2010-08-25 10.26.08
BR0657I Input menu 83 – please check/enter input values
——————————————————————————-
BRSPACE options for drop tablespace

1 – BRSPACE profile (profile) …… [initSMD.sap]
2 – Database user/password (user) .. [/]
3 ~ Tablespace name (tablespace) … [PSAPSR3700]
4 – Confirmation mode (confirm) …. [yes]
5 – Scrolling line count (scroll) .. [20]
6 – Message language (language) …. [E]
7 – BRSPACE command line (command) . [-p initSMD.sap -s 20 -l E -f tsdrop -t PSAPSR3700]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2010-08-25 10.26.20
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0291I BRSPACE will be started with options ‘-p initSMD.sap -s 20 -l E -f tsdrop -t PSAPSR3700’

BR0280I BRTOOLS time stamp: 2010-08-25 10.26.20
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRTOOLS time stamp: 2010-08-25 10.28.18
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

###############################################################################

BR1001I BRSPACE 7.00 (28)
BR1002I Start of BRSPACE processing: sedzavzu.tsd 2010-08-25 10.28.18
BR0484I BRSPACE log file: /oracle/SMD/sapreorg/sedzavzu.tsd

BR0280I BRSPACE time stamp: 2010-08-25 10.28.19
BR1009I Name of database instance: SMD
BR1010I BRSPACE action ID: sedzavzu
BR1011I BRSPACE function ID: tsd
BR1012I BRSPACE function: tsdrop

BR0280I BRSPACE time stamp: 2010-08-25 10.28.20
BR0657I Input menu 310 – please check/enter input values
——————————————————————————-
Options for dropping of tablespace PSAPSR3700

1 * Number of files in tablespace (files) . [7]
2 * Total tablespace size in MB (size) …. [700]
3 – Force tablespace drop (force) ……… [no]
4 – SQL command (command) …………….. [drop tablespace PSAPSR3700]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2010-08-25 10.28.45
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2010-08-25 10.28.45
BR1070I Searching for segments in tablespace PSAPSR3700…
BR0285I This function can take several seconds/minutes – be patient…

BR0280I BRSPACE time stamp: 2010-08-25 10.28.45
BR1071I Tablespace PSAPSR3700 is empty

BR0280I BRSPACE time stamp: 2010-08-25 10.28.45
BR0370I Directory /oracle/SMD/sapreorg/sedzavzu created

BR0280I BRSPACE time stamp: 2010-08-25 10.28.46
BR0319I Control file copy created: /oracle/SMD/sapreorg/sedzavzu/cntrlSMD.old 11223040

BR0280I BRSPACE time stamp: 2010-08-25 10.28.48
BR0768I Tablespace PSAPSR3700 set OFFLINE

BR0280I BRSPACE time stamp: 2010-08-25 10.28.48
BR1090I Dropping tablespace PSAPSR3700…

BR0280I BRSPACE time stamp: 2010-08-25 10.28.49
BR1016I SQL statement ‘drop tablespace PSAPSR3700’ executed successfully
BR1069I Tablespace PSAPSR3700 dropped successfully

BR0280I BRSPACE time stamp: 2010-08-25 10.28.49
BR0794I Database file /oracle/SMD/sapdata1/sr3700_1/sr3700.data1 deleted
BR0480I Directory /oracle/SMD/sapdata1/sr3700_1 deleted
BR0794I Database file /oracle/SMD/sapdata2/sr3700_2/sr3700.data2 deleted
BR0480I Directory /oracle/SMD/sapdata2/sr3700_2 deleted
BR0794I Database file /oracle/SMD/sapdata3/sr3700_3/sr3700.data3 deleted
BR0480I Directory /oracle/SMD/sapdata3/sr3700_3 deleted
BR0794I Database file /oracle/SMD/sapdata4/sr3700_4/sr3700.data4 deleted
BR0480I Directory /oracle/SMD/sapdata4/sr3700_4 deleted
BR0794I Database file /oracle/SMD/sapdata4/sr3700_5/sr3700.data5 deleted
BR0480I Directory /oracle/SMD/sapdata4/sr3700_5 deleted
BR0794I Database file /oracle/SMD/sapdata6/sr3700_6/sr3700.data6 deleted
BR0480I Directory /oracle/SMD/sapdata6/sr3700_6 deleted
BR0794I Database file /oracle/SMD/sapdata6/sr3700_7/sr3700.data7 deleted
BR0480I Directory /oracle/SMD/sapdata6/sr3700_7 deleted

BR0280I BRSPACE time stamp: 2010-08-25 10.28.49
BR0340I Switching to next online redo log file for database instance SMD …
BR0321I Switch to next online redo log file for database instance SMD successful

BR0280I BRSPACE time stamp: 2010-08-25 10.28.53
BR0319I Control file copy created: /oracle/SMD/sapreorg/sedzavzu/cntrlSMD.new 11223040

BR0280I BRSPACE time stamp: 2010-08-25 10.28.53
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRSPACE time stamp: 2010-08-25 10.29.00
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2010-08-25 10.29.00
BR1020I Number of tablespaces processed: 1
BR1003I BRSPACE function ‘tsdrop’ completed

BR1008I End of BRSPACE processing: sedzavzu.tsd 2010-08-25 10.29.00
BR0280I BRSPACE time stamp: 2010-08-25 10.29.00
BR1005I BRSPACE completed successfully

###############################################################################

BR0292I Execution of BRSPACE finished with return code 0
BR0280I BRTOOLS time stamp: 2010-08-25 10.29.00
BR0256I Enter ‘c[ont]’ to continue, ‘s[top]’ to cancel BRTOOLS:
c
BR0280I BRTOOLS time stamp: 2010-08-25 10.29.07
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRTOOLS time stamp: 2010-08-25 10.29.07
BR0656I Choice menu 5 – please make a selection
——————————————————————————-
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

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
b
BR0280I BRTOOLS time stamp: 2010-08-25 10.29.11
BR0663I Your choice: ‘b’
BR0673I Going back to the previous menu…

BR0280I BRTOOLS time stamp: 2010-08-25 10.29.11
BR0656I Choice menu 1 – please make a selection
——————————————————————————-
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

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
9
BR0280I BRTOOLS time stamp: 2010-08-25 10.29.13
BR0663I Your choice: ‘9’
BR0280I BRTOOLS time stamp: 2010-08-25 10.29.13
BR0680I Do you really want to exit BRTOOLS? Enter y[es]/n[o]:
y
BR0280I BRTOOLS time stamp: 2010-08-25 10.29.15
BR0257I Your reply: ‘y’

BR0280I BRTOOLS time stamp: 2010-08-25 10.29.15
BR0652I BRTOOLS completed successfully
smdev:SMD:/home/orasmd>

refresh information in dbms_space.asa_recommendations()

You need to analyze object you reorganized and then check parameters of this function to see that default it uses all (it means also old) runs of auto space advisor:

SELECT segment_name,
round(allocated_space/1024/1024,1) alloc_mb,
round( used_space/1024/1024, 1 ) used_mb,
round( reclaimable_space/1024/1024) reclaim_mb,
round(reclaimable_space/allocated_space*100,0) pctsave,
recommendations ,
re.task_id, ta.execution_end
FROM TABLE(dbms_space.asa_recommendations()) re, dba_advisor_tasks ta
Where ta.task_id=re.task_id

so use dbms_space.asa_recommendations(‘FALSE’,’FALSE’,’FALSE’)
to see latest.

you can also manually invoke the run of advisor by submitting of DB job (under a SYSDBA account):

Declare
v_Job Integer;
Begin
dbms_job.submit(v_Job,
‘Begin dbms_scheduler.run_job(”AUTO_SPACE_ADVISOR_JOB”); End;’ );
commit;
end;

SMON process consumes 100 % CPU

As soon as customer starts up the database, a lock is put on
SYS.SMON_SCN_TIME by SMON and it never go away.

Database Performance becomes slow.

SMON_SCN_TIME has huge no.of records.

SQL> select count(*) from sys.smon_scn_time;

COUNT(*)
———-
137545

1 row selected.

It is found that the object has been locked.

SQL> select object_id from dba_objects where object_name = ‘SMON_SCN_TIME’;

OBJECT_ID
———-
575

1 row selected.

SQL> select * from v$locked_object where object_id = 575;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID
———- ———- ———- ———- ———-
ORACLE_USERNAME OS_USER_NAME PROCESS
—————————— —————————— ————
LOCKED_MODE
———–
5 5 1494 575 164
dbadmin 4444350
3 conn / as sysdba

/* Set the event at system level */

SQL> alter system set events ‘12500 trace name context forever, level 10’;

/* Delete the records from SMON_SCN_TIME */

SQL> delete from smon_scn_time;

SQL> commit;

SQL> alter system set events ‘12500 trace name context off’;

Now restart the instance.