How to free up database space in Tablespace in Oracle?

Oracle uses the high-water mark after deleted rows in database, you can free up this space at the table level with following methods.

1 . using export/import of table -

For a complete restructuring of table and space freeing up export/import of a table allows us to restructure our files and release the lost space.

2. using dbms_redefinition -

This procedure will reorganize a table while it remains online for updates.

3. using alter table tmp shrink -

If you are using Oracle 10g and later, you could use
alter table tmp shrink space compact.

4. using coalesing table -

It removes space above the high-water mark, It puts together the uncontiguous fragmented extents.

The honeycomb fragmentation and Swiss Cheese fragmentation are mainly occured in oracle. When the free extents are side by side, its in honeycomb fragmentation, and when the extents are separated by live segments, its in Swiss Cheese fragmentation.

alter table tmp coalesce;

5. using deallocate unused space -

Oracle uses it to explicitly deallocate unused space at the end of a segment and makes that space available for other segments within the tablespace.

alter table tmp deallocate unused space;

Oracle deallocates unused space beginning from the end of the objects and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM). For indexes, deallocate unused space coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.

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.

What does I can do after logging in user DBSNMP in Oracle?

The Oracle Intelligent Agent requires a database logon for each SID that it manages.

DATABASE USERNAME : DBSNMP
DEFAULT PASSWORD : DBSNMP

The user name and/or password should be changed from the default but you will need to make a few additional modifications. It Supports Oracle SNMP (Simple Network Management Protocol).

ORA-01536: space quota exceeded for tablespace 'USERS'. Whats Soluition of this?

ORA-01536: space quota exceeded for table space "USER".

"DBA removed the user from the RESOURCE role As effect of it, User losts the ability to create tables,even though the CONNECT role explicitely allows CREATE TABLE."

This Error is due to User Permissions.
My User can update a table having CONNECT role granted to it. But, It can't write due to user quota specified by DBA. I thought that using the "default" profile would keep this sort of problem. As a Resolution of this Problem -"
One Question with me is Why it showing quota error ! why not it is a user permissions related error?

Oracle Errors while coding of PL/SQL Blocks

ORA-00001 Unique constraint violated. (Invalid data has been rejected)
Solution: Check PL/SQL Code.You are inserting data that already exists in database.

ORA-00600 Internal error (contact support)
Solution : Check the connection with Oracle Database.

ORA-03113 End-of-file on communication channel (Network connection lost)
Solution : Reconnect to Oracle

ORA-03114 Not connected to ORACLE
Solution : Reconnect to Oracle

ORA-00942 Table or view does not exist
Solution : Table or view in which you are entering or fetching data from table/view is not exists in database.

ORA-01017 Invalid User name/Password
Solution : Your are trying to connect with database with wrong password or wrong username.

ORA-01031 Insufficient privileges
Solution : You are currently accessing data from which table or view or any object not granted to the user through which you are currently logged in.

ORA-01034 Oracle not available (the database is down)
Solution : Database connection is not available. Please restart the database or database is in Mount Stage.

ORA-01403 No data found
Solution : While fetching data, No data found from the written Query.

Oracle Date Conversion function - TO_DATE()

TO_DATE in Oracle pl/sql is to convert char data type to a value of DATE data type.

TO_DATE( String, format, nls_param )
String : is the string to convert to date
format : is the format that will be used to convert string to a date.
nls_param : is the nls_languageused to convert string to a date.

Examples:
SQL> select to_date('APR/03/2009' ,'MON-DD-YYYY HH24:MI:SS') DATE from dual;
SQL> select to_date('20080312','YYYYMMDD') from dual;
SQL> select to_date('20080312','YYYYDDMM') from dual;
SQL> select to_date('2006/11/14 18', 'yyyy/mm/dd hh24') from dual;