ORA-01536: space quota exceeded for tablespace

One of our testing database schema got this error.
It means that users is trying to use more space than the Database Administrator assigned to him. Quota is a maximum number of bytes that a user can allocate in particular tablespace. Implementing quotas in database is a reasonable approach because it prevents buggy or malicious code to fill the tablespace.
Database Administrators should remember that after they setup quotas for users they are responsible for constantly monitoring and extending quotes if required.

ORA-01536: space quota exceeded for tablespace is a common error in Oracle Database.

It is worth to mentioned that prior to Oracle 11g quotas in Oracle could be assigned to both permanent and temporary tablespaces. Starting from Oracle 11g quotas can only be set on permanent tablespaces.

here is a solution to fix this error.

first we need to check the usage of quota and allocated quota in dictionary views, use the below query to check the usage and allocation.

connect  to a database with system privileged user. 

sql>select TABLESPACE_NAME,USERNAME,BYTES/1048576 used_bytes,MAX_BYTES/1048576 allocated_bytes from dba_ts_quotas where username='your_problem_facing_username';

in the results if u get allocated and used bytes are equal then you have to allocate more bytes of quota to that particular user.

The below syntax tells how to allocate quota to a particular user.

sql> alter user  username quota bytes(in mb, gb) on tablespace_name;

example:- sql>alter user user1 quota 25m on user;

the above example allocates 25 mb of quota (space)  to user1 in user tablespace.

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file ORA-01110: data file 203: ‘1__.1__.0.49\e$:/oradata/oracle10g/oradata/sde1.dbf

This error i was getting after changing my PC ip address. But i updated my changed ip address in tnsnames.ora file after that i was getting the above error. I fixed the above error very easily. I just renamed my datafile after that problem was fixed.

i had change my ip address after that i was raising the error on my old ip address. i just renamed that datafile to new ip address.

Step1:-alter database rename file 'E:\oradata/oracle10g/oradata/sde1.dbf'

             to 'E:\oradata/oracle10g/oradata/sde1.dbf';

 after executing this query the prob fixed.

To kill a session using orakill utility.

In Windows we have several utilities, the Oracle-centric "orakill" utility and the Windows "taskkill" program.  The Windows command to kill this session would be as follows. just you have to go inside the oracle installed folder (Example I mentioned below.)

step1: C:\oracle\product\10.2.0\db_1\BIN\orakill instance_name spid

spid you can retrieve using the below query

stpe2: sql>select a.username,a.osuser,b.spid from v$session a,v$process b

 where a.paddr=b.addr

 and a.username is not null;

and instance_name you can get using the below query.

step3: sql>select instance_name from v$instance;

just mention the instance_name and spid in the above step1 command syntax.

after that check the status of the killed  user session in the database.

sql>select username,osuser,status from v$session where username='&username';

cannot copy filename data error cyclic redundancy check

Once if you get this error on any file after that it's not possible to copy tht particular file to any other location.
To fix  this error follow the simple methods..

step1:-go to run window and type cmd

step2:-set the particular drive as your home directory (by default  c directory as your home directory if you are facing tht error in any other drive type drive name like this   d:)

step3:-chkdsk /r 
it will ask some questions say yes to all questions then reboot your pc. while rebooting it will check the particular disk and make tht particular error affected disk segment rite.

step4:-next you can copy tht error affected file to any locations..

              

ORA-28002: the password will expire within 7 days

Today i'm facing this error while logging in one of my 11g production database system account. If you don't wish to receive this kind of error and you want set password expiry to unlimited, here is the simple steps you can fix this is error.

step1:-logging in as system account. then check the profile name of the particular account.
    sql>select profile from dba_users where username='USERNAME';
the above query gives the profile name of your user account.

step2:-log in to that particular account (which account you are receiving error)
sql>password
type the above command after logging in to that particular account, it will asks the old password and new password  and finally it will return (password changed).

step3:-check the resource password_life_time=value in dba_profiles
   sql>select * from dba_profiles where resource_name like '%PASSWORD_LIFE_TIME%';
the above query gives the value of your password_life_time resource, if it set to any numeric value then change it to unlimited.

step4:-set the particular profile password_life_time to unlimited.
sql>alter profile default limit password_life_time unlimited;

hope it will works.

ORA-01041 internal error hostdef extension doesn't exist

one day i was This error in my production database. i shutdown my database properly but when i was making database up i was facing this error. i fix this error so simply just i restarted oracle service after that everything was fine..database became up. problem fixed..

FORMULA for Determining the number of dispatcher in oracle database.

Number of dispatchers =
CEIL ( max. concurrent sessions / connections for each dispatcher )


For example, assume a system that can support 970 connections for each process, and
that has:
■ A maximum of 4000 sessions concurrently connected through TCP/IP and
■ A maximum of 2,500 sessions concurrently connected through TCP/IP with SSL
The DISPATCHERS attribute for TCP/IP should be set to a minimum of five
dispatchers (4000 / 970), and for TCP/IP with SSL three dispatchers (2500 / 970:
DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'

ORA-01031: insufficient privileges while trying to connect as sysdba in client machine.

I installed new oracle 11g software on server machine and i configured everything like password file and tns names everything in server machine but when i was trying to connect  as a sysdba in client machine the below error showing.
ORA-01031 insufficient privileges.
solution:- Logged in server machine. type compmgmt.msc in run window. then computer management window will appear so there i checked local users and groups  there my OS user account was not added in oradba  group. so added my OS user account there, then i tried in client machine to connect as a sysdba and connected successfully without prob.. 

ORA-20000: index "string"."string" or partition of such index is in unusable state

This kind of error i saw in our production database.
so first i checked that index status in all_indexes view, it was in unusable state. after that i tried to rebuild that index with that particular table_owner. for that i issued the below query.
sql>alter index table_owner.index_name rebuild online;
but it was giving the below error
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
then i checked in all_indexes view that index is partitioned or not but it was not partitioned then i issued the below query so that became unusable state to valid.
sql>alter index table_owner.index_name rebuild parallel;
The above query causes the index to be rebuilt from the existing index by using parallel execution processes to scan the old and to build the new index

PING[ARC1]: Heartbeat failed to connect to standby 'hcmsstby'

 This problem arises when standby server not getting archive logs from standby server. Here is the solution to fix this problem.

step1:- connect(go) to standby server log in as a system account.
step2:- then type the below command
SQL>recover standby database;
after executing this command it gives three options to recover file from main server. the options listed in below statement.
Specify log: {=suggested | filename | AUTO | CANCEL}
then specify which option is familiar to you. If you specified AUTO then automatically that statement only gives which log is needed for recovery then copy that log from main server then paste it in standby server. Then after that run the above commmand
SQL>recover standby database;
if their is no log is missing then standby server updates logs then logs will come automatically from main server to standby server. Then put standby server in managed mode. In this mode standby server automatically updates and logs will comes from main server. the command is listed below.
setp3:-SQL>recover managed standby database;
if you want to check the logs are updated in standby server then cancel this command using the below command.
step4:-SQL>recover managed standby database cancel;
then run that step2 command. then you will get log missing error message then cross check that archive log number in main server if that archive log is not exists in main server then you are standby server updated up to that  archive log. Then put your standby database is in managed mode using step3 command.

ORA-28000: the account is locked

Cause: The user has entered wrong password consequently for maximum number
of times specified by the user"s profile parameter FAILED_LOGIN_ATTEMPTS, or
the DBA has locked the account

Solution:- Contact oracle dba or unlock the user account using below queries.

sql>alter user username unlock;
if the user is locked by user has entered maximum number of times wrong password then use the below query to unlock the account.
simple method.
sql>alter user username account unlock;
one more method.
sql>alter user username identified by new_password account unlock;