cursor oracle 10g

What is corsors in oracle
-----------------------------
A cursors is a pointer used to fetch rows from a result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in this structure is a pointer to the next record to be fetched from the query results.

Two types of classification - I and II
--------------------------------------

I. Implicit and Explict

a. Implicit : More fast and less coding effort.

Will never raise INVALID_CURSOR error

Cannot be opened outside the statement

Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select )

Implicit cursor returning more than one record? FOR LOOP cursors.

b. Explicit : 2 network round trips. Store data first then retrieve data. More programmatic control.

Programmer could open; fetch data, close, check attributes etc.

II Static and Dynamic

a. Static : Normal cursor (implicit or explicit)

b. Dynamic : Ref cursor: Cursor created only when it is opened.

Could be declared once and defined many times in different procedures.

Ref Cursors can have Record/s as return types. So could be used for returning

data to other languages like Java, C++ etc

II.b) Ref cursor - Two types

Strong : Ref cursor with a specified return type.

Weak : No return type specified.

3. Cursor attributes

%FOUND - records fetched successfully

%NOTFOUND - no records fetched

%ROWCOUNT - Number of records fetched

%ISOPEN - returns TRUE if cursor is open

Could be used for implicit and explicit cursors.

Eg:

Implicit:
---------

select * from emp -- also for delete operations

If SQL%FOUND then

v_count:= SQL%ROWCOUNT

end if;



Explicit:
--------------

open c1; -- cursor c1 is select

fetch <>

exit when c1%NOTFOUND



Eg: Explicit cursor.

Declare

Cursor cur1 is

select ename,empno,sal from emp
where empno between 9000 and 1000
and sal<50000
and deptno=50
begin
open cur1;
fetch cur1 into v_ename,v_empno,v_sal;
exit when cur1%notfound;
---
close cur1;
end;



Key words :

cursor oracle 10g
ref cursor in oracle
parameterized cursor in oracle
dynamic cursor in oracle
create cursor in oracle
cursor in oracle stored procedure
fetch cursor in oracle
cursor sql oracle

ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s

ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s

Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE



Diagnosis the problem
========================



1. Determine the largest contiguous space available for the tablespace with the error

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '';

The above query returns the largest available contiguous chunk of space.


2. Determine NEXT_EXTENT size

SELECT NEXT_EXTENT, PCT_INCREASE FROM DBA_SEGMENTS WHERE SEGMENT_NAME = AND SEGMENT_TYPE = AND OWNER =

is usually stated in the error message


3. Compute the NEXT EXTENT SIZE if the segment resides in a dictionary managed tablespace and has a PCT_INCREASE >0

SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '';

Use the "next_extent" size with "pct_increase" (from #2 above) in the following formula to determine the size of extent that is trying to be allocated.

extent size = next_extent * (1 + (pct_increase/100)



4) Determine if the tablespace containing the object is AUTOEXTENSIBLE and has reached MAXSIZ

For Data Files

SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name=' ';

For Temp Files

SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name=' ';


5) Determine which solution best fits the conditions

If the NEXT EXTENT size (step 2 or 3 above) is larger than the largest contiguous chunk of free space then
Manually Coalesce Adjacent Free Extents may be an option
If after coalescing ... there is still insufficient contiguous space ... then one of the other options must be chosen

If the volumes on which the datafile/tempfile(s) for the tablespace have insufficient space then
Add a Datafile/Tempfile or Defragment the Tablespace are your only options ... and this file must be added to a new volume with sufficient space

If the tablespace is AUTOEXTENSIBLE and at MAXSIZE ... either raise max size (after verifying that the volume has space) or Add a Datafile/Tempfile or Defragment the Tablespace



===========================
Possible solutions
=============================

A. Manually Coalesce Adjacent Free Extents
ALTER TABLESPACE COALESCE;

B. Modify one or more datafiles/tempfiles in the tablespace to use AUTOEXTEND
ALTER DATABASE DATAFILE|TEMPFILE '' AUTOEXTEND ON MAXSIZE ;

NOTE: It is strongly recommended that MAXSIZE be specified to try to prevent the datafile/tempfile from consuming all available space on the volume


C. Add a Datafile/Tempfile
ALTER TABLESPACE ADD DATAFILE|TEMPFILE '' SIZE ;


D. Lower "next_extent" and/or "pct_increase" size if segment is in a Dictionary Managed Tablespace
For non-temporary and non-partitioned segments:

ALTER STORAGE ( next pctincrease );



E. Resize the Datafile/Tempfile
ALTER DATABASE DATAFILE|TEMPFILE '' RESIZE ;


F. Defragment the Tablespace

ORA-1658: unable to create INITIAL extent for segment in tablespace %s

ORA-1658: unable to create INITIAL extent for segment in tablespace %s


Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

ORA-1654: unable to extend index %s.%s by %s in tablespace %s

ORA-1654: unable to extend index %s.%s by %s in tablespace %s

Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1652: unable to extend temp segment by %s in tablespace %s

ORA-1652: unable to extend temp segment by %s in tablespace %s
===============================================

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1651: unable to extend save undo segment by %s in tablespace %s

ORA-1651: unable to extend save undo segment by %s in tablespace %s
=================================================

Cause: Failed to allocate an extent of the required number of blocks for saving undo entries for the indicated offline tablespace.
Action: Check the storage parameters for the SYSTEM tablespace. The tablespace needs to be brought back online so the undo can be applied.

ORA-1650: unable to extend rollback segment %s by %s in tablespace %s

ORA-1650: unable to extend rollback segment %s by %s in tablespace %s
====================================================
Cause: Failed to allocate an extent of the required number of blocks for a rollback segment in the tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Sometimes the DBA does not know why Oracle is unable to allocate a new extent and for a quick solution he proceeds to add a new datafile. Not always is this solution is the best. Oracle has some views that help to determine which solution is better. This views are:

1. DBA_FREE_SPACE
2. DBA_FREE_SPACE_COALESCED

Indicate to DBA there is not free extent in the tablespace reported to support
the new extent.  Consulting the view DBA_FREE_SPACE the DBA can know if really
the tablespace does not have space avaliable or the tablespace is fragmented and
a reorganization should be made.  Remember that two contiguous extents are
considered two free spaces and both spaces can not be neither summarized nor
counted as one for free space contiguous.

ORA-01653: Unable To Extend Table %s.%s By %s In Tablespace %s

ORA-01653: Unable To Extend Table %s.%s By %s In Tablespace %s
=====================================================

Cause:  Failed to allocate an extent for table segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
       files to the tablespace indicated.


This error does not necessarily indicate whether or not you have enough space
in the tablespace, it merely indicates that Oracle could not find a large enough area of free
contiguous space in which to fit the next extent.

In order to see the free space available for a particular tablespace,
you must use the view DBA_FREE_SPACE. 

The DBA_TABLES view describes the size of next extent (NEXT_EXTENT) and the
  percentage increase (PCT_INCREASE) for all tables in the database.
The "next_extent" size is the size of extent that is trying to be allocated (and for
  which you have the error).
  
  When the extent is allocated :
  next_extent = next_extent * (1 + (pct_increase/100))



Possible Solutions:-
==================

- Manually Coalesce Adjacent Free Extents
      ALTER TABLESPACE  COALESCE;
  The extents must be adjacent to each other for this to work.

- Add a Datafile:
       ALTER TABLESPACE  ADD DATAFILE ''
       SIZE  ;

- Resize the Datafile:
       ALTER DATABASE DATAFILE '' RESIZE  ;

- Enable autoextend:
      ALTER DATABASE DATAFILE '' AUTOEXTEND ON
      MAXSIZE UNLIMITED;

- Defragment the Tablespace:

- Lower "next_extent" and/or "pct_increase" size:
       ALTER   STORAGE ( next  
       pctincrease );

- If the tablespace is being used as a temporary tablespace, temporary segments may
 be still holding the space.

Oracle apex 4.0 installation in oracle database 11g quickly

Oracle apex 4.0 installation in oracle database 11g quickly
===========================================================
===========================================================


installation of oracle Database 11gr2
======================================

you have moved the entire content of Components folder from File 2 (after
unzipping Database > Stage > Components: ) to Component folder of file 1
(after unzipping F:\Oracle 11g EE R2\Oracle\database\stage\Components).
Now you have got your complete Oracle Database 11g EE R2 Software in a single file.
Now open setup.exe from Oracle Database 11g EE R2/database folder and continue the
installation process.


installation of Apex 4.0 into 11g
===================================

1)After downloading unzip it in the following directory :

G:\app\apex_4_install\

its create a apex directory like :-

G:\app\apex_4_install\apex


2) Now You need to create a Tablespace for apex data as below :

SQL> CONN / AS SYSDBA

SQL> CREATE TABLESPACE APEX_DATA datafile
'E:\app\Administrator\oradata\Orcl\APEX_data.DBF'
SIZE 6g ;


3) install apex (@apexins) software in your created tablespace .


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator> G:

G:\cd G:\app\apex_4_install\apex

G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Tue J

Copyright (c) 1982, 2007, Oracle. All rights rese

SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> @apexins APEX_DATA APEX_DATA TEMP /i/

----its needs 25/30 minutes


4) again creating for image directory (@apxldimg.sql)


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator> G:

G:\cd G:\app\apex_4_install\apex

G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Tue J

Copyright (c) 1982, 2007, Oracle. All rights rese

SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> @apxldimg.sql

PL/SQL procedure successfully completed.

Enter value for 1: G:\app\apex_4_install
old 1: create directory APEX_IMAGES as '&1/apex/images'
new 1: create directory APEX_IMAGES as 'G:\app\apex_4_install/apex/images'

Directory created.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

timing for: Load Images
Elapsed: 00:03:26.51

Directory dropped.

SQL>

5) --for configuration ( @apxconf.sql)


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator> G:

G:\cd G:\app\apex_4_install\apex

G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>
G:\app\apex_4_install\apex>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Tue J

Copyright (c) 1982, 2007, Oracle. All rights rese

SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> @apxconf.sql

PORT
----------
8080

Enter values below for the XDB HTTP listener port and the password for the Appli
cation Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.


Enter a password for the ADMIN user []
Enter a port for the XDB HTTP listener [ 8080]
...changing HTTP Port

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.


Commit complete.

SQL>



And then press enter and your installation in this stage is done.


6) But you need to unlock few of the schema that is related to Apex application.
Now unlock the schema as :

SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

SQL> ALTER USER XDB ACCOUNT UNLOCK;

User altered.

SQL> ALTER USER APEX_040000 ACCOUNT UNLOCK;

User altered.

SQL> ALTER USER FLOWS_FILES ACCOUNT UNLOCK;

User altered.

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

User altered.

SQL>



7)Now is the time to open the apex application

Open a brower window and type the url as http:// < computername>:/apex

Example: If computer name is Halim_pc and port is 8080
then URL will be like

http://halim_pc:8080/apex

LOGIN like below

Workspace : internal
Username : ADMIN
Password : admin

change password .


cheers...................................

what are Major performance tuning areas in oracle database ?

Major performance tuning areas in oracle database.
========================================================


- Memory - shared pool, large pool, buffer cache, redo log buffer, and sort area size.
- I/O - distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
- CPU - CPU utilization.
- Space management - extent allocation and Oracle block efficiency.
- Redo log and checkpoint - redo log file configuration, redo entries, and checkpoint.
- Rollback segment - sizing rollback segments.
- Network

Drop user username cascade and error ORA-02429: cannot drop index used for enforcement of unique/primary key

Drop user username cascade and error ORA-02429: cannot drop index used for enforcement of unique/primary key
=============================================================================================================

Today in our test environment, i am trying to drop a user but i can't success. why ?



SQL>
SQL>
SQL> drop user mybank cascade ;
drop user mybank cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL> alter session set tracefile_identifier = 'drop user mybank cascade_1';

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Session altered.



location of the trace file :-

SELECT s.sid,
s.serial#,
pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');






in the trace file i found :-


--------------------------------------content of text file-----------------------------

=====================
PARSING IN CURSOR #10 len=402 dep=2 uid=0 oct=3 lid=0 tim=676591965 hv=3607805727 ad='50a6fba0'
select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296), 256) defhscflags from partobj$ where obj# = :1
END OF STMT
PARSE #10:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=676591962
EXEC #10:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=676592040
FETCH #10:c=0,e=7,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=676592064
STAT #10 id=1 cnt=0 pid=0 pos=1 obj=262 op='TABLE ACCESS BY INDEX ROWID PARTOBJ$ (cr=1 pr=0 pw=0 time=10 us)'
STAT #10 id=2 cnt=0 pid=1 pos=1 obj=263 op='INDEX UNIQUE SCAN I_PARTOBJ$ (cr=1 pr=0 pw=0 time=6 us)'
=====================
PARSING IN CURSOR #11 len=33 dep=1 uid=0 oct=10 lid=0 tim=676592272 hv=0 ad='8ac3194'
drop index "MYBANK"."PK_STTRNINT"
END OF STMT
PARSE #11:c=15625,e=7271,p=0,cr=68,cu=0,mis=1,r=0,dep=1,og=4,tim=676592269
=====================
PARSING IN CURSOR #12 len=652 dep=2 uid=39 oct=47 lid=39 tim=676592536 hv=3055982128 ad='43eec67c'
BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
END OF STMT
PARSE #12:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=676592532
EXEC #3:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,tim=676592790
FETCH #3:c=0,e=39,p=0,cr=7,cu=0,mis=0,r=1,dep=3,og=1,tim=676592848
EXEC #13:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,tim=676592986
FETCH #13:c=0,e=374,p=0,cr=58,cu=0,mis=0,r=1,dep=3,og=1,tim=676593377
EXEC #14:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,tim=676593444
FETCH #14:c=0,e=30,p=0,cr=8,cu=0,mis=0,r=1,dep=3,og=1,tim=676593492
=====================


===================================end content of tracefile=============================================================
=================================================================================================



Solution:-



at last i identify that, droping user are holding an unique index(Primary key) with referencing another user's table .
index script like below's :-



CREATE UNIQUE INDEX MYBANK.PK_STTRNINT ON ISLBAS.STTRNINT
(COMPCODE, INDOCTYP, INSUBTYP, INDOCNUM, INDOCDAT,
SERLNUMB)


After this identification, i just drop the referencing user first. then i succed to drop my target user.


OR

alter table islbas.STTRNINT disable constraint PK_STTRNINT ;


drop index "MYBANK"."PK_STTRNINT"


CREATE UNIQUE INDEX islbas.PK_STTRNINT ON ISLBAS.STTRNINT
(COMPCODE, INDOCTYP, INSUBTYP, INDOCNUM, INDOCDAT,
SERLNUMB)