Oracle ORA-00020 : Maximum Number of Processes(XXX) Exceeded.

Hi,

When connecting to oracle sometimes, user may face this error:

Maximum Number of Processes(XXX) Exceeded.

The reason is the process in the server are in use.

Solution:

<![if !supportLists]>1.    <![endif]>Is to increase the value of Processes in initialization parameter.

Or

<![if !supportLists]>2.    <![endif]>Kill the long time ideal session to free the processes. From oracle and in unix if needed

--List of processes not connected to sessions (Oracle8i):

***********************************************

select 'kill -9 '|| SPID || ';'  from v$process where program!= 'PSEUDO'and addr not in (select paddr from v$session)and addr not in (select paddr from v$bgprocess)and addr not in (select paddr from v$shared_server);

Thanks & Regards

Fauzi

The remote server returned an error: (407) Proxy Authentication Required.

Hello,

I was writing an asp.net utility(console application) to fetch an image from a URL. During development i faced the following message:

The remote server returned an error: (407) Proxy Authentication Required.

The solution is we need to place a app.config file to the project and add the following nodes

defaultProxy useDefaultCredentials="true"

Hope it helps...

Startup The Database In Archivelog Mode / NoArchivelog Mode

Startup The Database In Archivelog Mode
Steps Required To Take A Database Not In Archive Log Mode And Alter It To Archive Log Mode	SELECT log_mode FROM v$database; SHUTDOWN; STARTUP MOUNT EXCLUSIVE; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; SELECT log_mode FROM v$database;
Startup The Database In NoArchivelog Mode
Steps Required To Take A Database In Archive Log Mode And Alter It To No Archive Log Mode	SELECT log_mode FROM v$database; SHUTDOWN; STARTUP MOUNT EXCLUSIVE; ALTER DATABASE NOARCHIVELOG; ALTER DATABASE OPEN; SELECT log_mode FROM v$database;

 

Source: http://psoug.org/reference/archivelog.html

allowDefinition='MachineToApplication' beyond application level

When am trying to build a web application through visual studio 2010, I was getting the following error message.

·         "It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. 
This error can be caused by a virtual directory not being configured as an application in IIS. "

 

The solution is to check whether the is any other sub folder which is having web.config file. After removing it, The build was successful.

 

Best Regards

 

Grants given to objects through Roles for a Schema & without Roles (Direct)

Hi Folks,

 

The following queries are very much useful to get the list of Grants given to Roles for a particular schema objects & to List the direct grants given to users.

 

Grants given to objects through Roles for a Schema

**************************************************

SELECT TP.OWNER,TP.GRANTEE,RP.GRANTEE, TP.TABLE_NAME, TP.PRIVILEGE FROM DBA_TAB_PRIVS TP, DBA_ROLE_PRIVS RP WHERE TP.GRANTEE = RP.GRANTED_ROLE AND TP.OWNER='Schema Name'

ORDER BY 1,2,3 

 

 

Individual Grants given to objects without using Roles:

********************************************************

SELECT TP.OWNER,TP.GRANTEE,TP.TABLE_NAME, TP.PRIVILEGE FROM dba_tab_privs TP

where TP.OWNER='<Schema Name>' AND GRANTEE NOT IN (Select ROLE from DBA_ROLES) ORDER BY 2,3,4

 

Thanks & Regards

Fauzi

 

Oracle: History of SQL Statements in Oracle 11g

Hello,

 

The following sql query is very much help full to get the history of sql queries run on the server.

 

SQL:

select a.USER_ID, a.SESSION_ID,b.sql_text from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b where a.sql_id=b.sql_id

 

Thanks & Regards

Fauzi

 

 

Oralce Query to Get Last Sunday or Frieday of the month

The following query is very much useful:

 

 

SELECT next_day(last_day(sysdate)-7,'Friday') from dual

 

SELECT next_day(last_day(sysdate)-7,'Sunday') from dual

 

Thanks & Regards

Fauzi

 

Oracle: Steps to recover when Redo Files are Lost

RCAT>>select name from v$database;

NAME
---------
RCAT

RCAT>>alter system set db_recovery_file_dist='+DAT' scope=both;
alter system set db_recovery_file_dist='+DAT' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dist='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dist='+DATA' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dest='+DATA' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest_size=10M scope=both
RCAT>>/

System altered.

RCAT>>/ed
SP2-0042: unknown command "/ed" - rest of line ignored.
RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DAT' scope=both
RCAT>>/

System altered.

RCAT>>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
RCAT>>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
RCAT>>startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
RCAT>>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
RCAT>>startuo
SP2-0042: unknown command "startuo" - rest of line ignored.
RCAT>>startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1896
Session ID: 1 Serial number: 5

RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>
C:\Users\Administrator>
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:03 2013

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

Connected to an idle instance.

SQL> select * from v$instance
  2  ;
select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SQL> exit
Disconnected

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:42 2013

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-03113: end-of-file on communication channel
Process ID: 3208
Session ID: 1 Serial number: 3

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:59 2013

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
SQL> alter database mount ;

Database altered.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL>

--

Fauzi
+965-97324246
  

Oracle : Recovery Steps when control file is lost. If backup was done

SQL> startup;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> select status fron v$instance;
select status fron v$instance
                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> ed
Wrote file afiedt.buf

  1* select status from v$instance
SQL> /

STATUS
------------
STARTED

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      F:\RCAT\CONTROL01.CTL, F:\RCAT
                                                 \CONTROL02.CTL
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>
SQL> shutdown
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\RCAT\SYSTEM01.DBF'

SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo01.log
ORA-00328: archived log ends at change 950490, need later change 967649
ORA-00334: archived log: 'F:\RCAT\REDO01.LOG'

SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
INACTIVE                947455 01-MAY-13       950491 01-MAY-13

         3          1          3   52428800        512          1 NO
CURRENT                 959675 01-MAY-13   2.8147E+14

         2          1          2   52428800        512          1 NO
INACTIVE                950491 01-MAY-13       959675 01-MAY-13

SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
CURRENT                 972032 02-MAY-13   2.8147E+14

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0

SQL> set sqlprompt RCAT>>
RCAT>>
--

Fauzi
+965-97324246
  

Oracle: To create spfile copy

From SP file:

 

SYS>>create pfile='f:\oracpara.ora' from spfile;

File created.

 

From Memory:

SYS>>create pfile='f:\oracpara.ora' from memory;

File created.

Oracle: To run a structural change sql statement from RMAN:

To run a structural change sql statement from RMAN:

 

RMAN> sql 'alter database enable block change tracking'
2> ;

Oracle : SQL to find in Top Activity in EM

Hello,

 

The following SQL from default schema is useful to create TOP activity in Server,

select * from sh.sales,sh.customers, sh.channels

Note: Only for testing server :)

 

It was helpful to find it in Enterprise Manager.

 

 

Oracle: To Know Current System Change No.

 To Know Current System Change No. :

 

 select Current_scn from v$database;

Oracle: How to lock a table with select command

How to lock a table with select command:

 

SQL: select * from scott.dept for update;

Remember to Rollback when you want to unlock later :)

Oracle: Best Practice for TABLES & INDEXES Tablespace

Tip of the day: Best Practice (for realtime):

Create tablespaces for TABLES, and separate tablespaces
for INDEXES for better performance and management.

Oracle 8i : DB Links script with credentials

The Following script is very much helpful to generate script for DB Links created in the server.

 

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)

||'connect to ' || L.USERID || ' identified by '

||L.PASSWORD||' using ''' || L.host || ''''

||chr(10)||';' TEXT

FROM sys.link$ L, sys.user$ U

WHERE L.OWNER# = U.USER#;

 

http://www.google.com/search?q=kuwait+weather&sourceid=ie7&rls=com.microsoft:en-us:IE-SearchBox&ie=&oe=

 

 

 

Thanks & Regards

Varusai Fauzi Mohammed |Kuwait Oil Company (KOC) | |Systems Analyst | Research & Technology | |Email: FVARUSAI@kockw.com | Web: www.kockw.com | |Tel: +965 238 72496 | Fax: +965  23982370 | |P.O Box 9758 | Ahmadi | Postal Code 61008 | Kuwait |   Please consider the environment before printing

 

 

Oracle: syntax to Alter materialized view

Alter materialized view MV_NAME

refresh complete

start with trunc(sysdate)+17/24

next trunc(sysdate) - to_number(to_char(sysdate - 1, 'd')) + 5;

 

 

Thanks & Regards

Fauzi

 

SQL to list the number of database files in Oracle Database

Hi there,

 

Found this interesting query which will list you all the DB files holding your database:

 

select    name, bytes

from    (select    name, bytes

    from    v$datafile

    union    all

    select    name, bytes

    from     v$tempfile

    union     all

    select     lf.member "name", l.bytes

    from    v$logfile lf

    ,    v$log l

    where    lf.group# = l.group#) used

,    (select sum(bytes) as poo

    from dba_free_space) free

/

 

Hope it helps,

 

Regards

Fauzi

How to select distinct Columns from DataTable C#

Following is the code helpful to filter distinct columns from a DataTable

 

DataTable distinctDT = dtSOURCE.DefaultView.ToTable("TAB_NAME", true, "YourCOLUMN");

 

Time to rush back to Visual Studio Bye J