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)'