How to check Database is in restricted mode or not

Hi All,

Users are not allowed to login in restricted mode. You get ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Login to database and

SQL> select logins from v$instance;
.

Example:


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TEST

SQL> Select logins from v$instance;

LOGINS
----------
RESTRICTED

SQL>


It should be ALLOWED.

ORA-01000: maximum open cursors exceeded

Many times we may see this error. This means we have crossed the limit of open cursor. You can check the value assigned for cursor

show parameter open_cursor

Solution 1:

Increase the value for open_cursor

ALTER SYSTEM SET OPEN_CURSORS=500 SID='*' SCOPE=BOTH;


Solution 2:

Tune the sql statement to limit it for creating multiple sessions. Below sql will help you to check the number of cursor open by sessions.


select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursor '
group by s.username, s.machine order by 1 desc;

Drop table commands ends up with ORA-02449

Hello,

Many may have noticed that when you try to drop table we face ORA-02449 error. This is due to the primary key in the table which is referring to the foreign key of another table.

Error occurred is :

ORA-02449: unique/primary keys in table referenced by foreign keys


To overcome this situation, make sure you do not need this table. And then fire below command to delete the table. This drop statement with cascade constraints option will delete the primary key constraints from the table and drop the table.


DROP TABLE EMP CASCADE CONSTRAINTS;

Unable to drop ASM diskgroup getting ORA-15027

Hello,

Recently I came across such situations that I was unable to drop the ASM diskgroup. I was getting  ORA-15027 . Later I realized that the spfile fot the ASM instance is available in the diskgroup.

To solve this, first we need to move the spfile to different location. Once that is done, we can delete the desired diskgroup.

State	Type	Rebal	AU	Total_MB	Free_MB	Name
MOUNTED	EXTERN	N	1048576	1488042	1487967	DATA

 Here is the desired, diskgroup to be dropped.

ASMCMD> dropdg -r DATA
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount (DBD ERROR: OCIStmtExecute)
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.840344755
ASMCMD> spmove '+DATA/asm/asmparameterfile/registry.253.840344755' '/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora';
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/asm/asmparameterfile/registry.253.840344755' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD> spget
/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora;
ASMCMD> exit
-bash-3.2$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 24 08:31:24 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> shut immediate
ASM diskgroups volume disabled
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             385977688 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora/app/oracle/product/11.2.0
                                                 .3/grid/dbs/spfile+ASM.ora;
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
-bash-3.2$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   1488042  1487967                0         1487967              0             N  DATA/
ASMCMD> spget
/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora;
ASMCMD> dropdg -r DATA
ASMCMD> lsdg
ASMCMD> exit


 Good luck !!!!!!!!!!!!!!!!!!!!

Drop table commands ends up with ORA-02449

Hello,

Many have noticed that when you try to drop table we face ORA-02449 error. This is due to the primary key in the table which is referring to the foreign key of another table.

Error occurred is :

ORA-02449: unique/primary keys in table referenced by foreign keys


To overcome this situation, make sure you do not need this table. And then fire below command to delete the table. This drop statement with cascade constraints option will delete the primary key constraints from the table and drop the table.


DROP TABLE EMP CASCADE CONSTRAINTS;

table dropped.

ORA-00980: synonym translation is no longer valid

As the name suggests, we can get an idea that while synonym called it is not accessible. There are more than one possibility for this error.

1.) The base table, on which synonym is created, is deleted.
2.)  The entire schema of the base table is dropped and we forget to drop the synonym.
3.) If synonym created on remote database, that is, via dblink. Then please check if dblink is working.

Below query will all the synonyms in the database.

select * from dba_synonyms where table_owner not in ('SYS','SYSTEM');

To avoid looking at the large number of synonyms , you can specify synonym name to look for the table name.

select * from dba_synonyms where table_owner not in ('SYS','SYSTEM') and synonym_name='TEST';

ORA-29278: SMTP transient error: 421 4.3.2 Service not available, closing transmission channel

While setting up access control list in Oracle 11g you may face this error. This error is purely linked to the smtp server.

Firstly, you can query the dictionaryDBA_NETWORK_ACLS and check if you have added correct entry under host. Host column will be displaying your SMTP server.

For example:

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                                          HOST                 LOWER_PORT UPPER_PORT

------------------------------ -------------------- ----------    ----------

/sys/acls/SMTP_SERVER.xml      10.67.65.188

SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('10.67.65.188'));

COLUMN_VALUE

--------------------------------------------------------------------------------

10.67.65.188

10.67.65.*

10.67.*

10.*

*

If the smtp host entry is ok. Then contact sysadmin team to check the SMTP server services.