How to reclaim or release the size of fragmented data files of tablespaces?

All Database objects like, tables,triggers,procedures,packages,functions,etc. are resided in tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space.

The Segment Advisor which will recommend us when database objects will get benefit from a reorganization to free up disk space.

Oracle has several tools to help reclaim disk space:

1 . Alter database datafile tempdb.dbf resize 50 M

This will remove spaces by physically. if the datafile, and this will not work, any of the segments of the tablespace are extended beyond your resize boundary. So, apply proper datafile size while using this.

2. Alter tablespace tempdb coalesce

This will reclaim space from honeycomb fragmentation.

How can I change password of databse user 'DBSNMP'?

1. Remove all Jobs and Events currently registered against this database.
   
2. Stop the Intelligent Agent
   
   Oracle7 / 8i % lsnrctl dbsnmp_stop
   Oracle9i / 10g / 11g % agentctl stop
   
   
3. Edit the $ORACLE_HOME/network/admin/snmp_rw.ora file. Add the following parameter:
   SNMP.CONNECT..NAME=
   SNMP.CONNECT..PASSWORD=
   The variable is the exact listing of the database name as it appears in the snmp_ro.ora file. If is the default (DBSNMP), there is no need to specify the user here. Only the password is required.
   On UNIX, set the following permission on the "SNMP_RW.ORA" file:%chmod 600 snmp_rw.ora
   
4. Change the DBSNMP password on the database. You can use either Security Manager, Sqlplus, or Server Manager. If you use SQLPlus or Server Manager, you can issue the following command:
   
   SQL> alter user "dbsnmp" identified by "newpassword";
   
   
5. Stop and restart the Intelligent Agent.