SQL & PL/SQL :: Will Oracle Automatically Deallocate Memory Occupied By Records In Cursor Area

Feb 25, 2013

i have a ref cursor and i have used 'open cursor for' statement:

CREATE OR REPLACE PACKAGE aepuser.pkg_test
AS
TYPE cur1 IS REF CURSOR;
PROCEDURE get_empdetails (p_empno NUMBER, io_cur OUT cur1);
END;

[code]...

then i want to know that- will oracle automatically deallocate the memory occupied by records in cursor area?if yes, then when it will be free , in case of 'open cursor for' ?

View 7 Replies


ADVERTISEMENT

Exact Space Occupied By A Table With LOB Column

Oct 18, 2013

I need to check the exact amount of space used (in bytes or MB) by a table which is having a BLOB column.I tried the following query but it is not giving the proper usage. 

select segment_name , sum(bytes)from dba_extentswhere segment_type='TABLE'and segment_name in ('TEST_CLOB','TEST_BLOB','TEST_CLOB_ADV','TEST_BLOB_ADV') group by segment_name; I even tried the following stored procedure  create or replace procedure sp_get_table_size (p_table_name varchar2)as    l_segment_name          varchar2(30);     l_segment_size_blocks   number;     l_segment_size_bytes    number;     l_used_blocks           number;      l_used_bytes            number;      l_expired_blocks        number;      l_expired_bytes         number;      l_unexpired_blocks      number;      l_unexpired_bytes       number;    begin    select
[code].......

But it is giving the error

Error starting at line 298 in command:exec sp_get_table_size ('TEST_CLOB_ADV')Error report:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE packageORA-06512: at "SYS.DBMS_SPACE", line 210ORA-06512: at "SYS.SP_GET_TABLE_SIZE", line 20ORA-06512: at line 103213. 00000 -  "Invalid Lob Segment Name for DBMS_SPACE package"*Cause:    The Lob Segment specified in the DBMS_SPACE operation does not exist.

*Action:   Fix the Segment Specification Although the LOB section is specified in create table syntax.

View 4 Replies View Related

SQL & PL/SQL :: Find Total Space Occupied On Disk By Tablespaces Of Database

Nov 3, 2010

I am trying to find the space occupied on disk by the tablespaces of the database that contain tables, some (and not all) of whose columns are encrypted. My query is like this:

select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name;

The output of the query is as shown in the attached file:

TABLESPACE_NAMEFILE_NAMEFILE_SIZE_IN_GB
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0044.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0045.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0051.DBF29.296875
DMS_DATAN:ORACLEORADATASPOPRODDMS_DATA_0012.DBF19.53125
[code]...

Since the output (under the heading Total Size of the tablespace) is probably the sum of all the datafiles returned by the query and is obviously incorrect, I have not given the rest of it. I also tried the following:

select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB,
sum (bytes/(1024*1024*1024))over (partition by a.tablespace_name order by file_name) "Total Size of the tablespace"
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name ;
[code]...

Here, the fig. under the heading "Total Size of the tablespace" are probably the sum of all the records returned by the query if distinct is not used i.e all the data file sizes returned by the query.

tune my query and get the desired results? I think this can be achieved by group by with rollup, cube, order by and grouping functions, but am not sure how to proceed. I know that I can get the results by using Enterprise Mgr. Console in 2 mins., but would still like to get the results with the queries.

View 11 Replies View Related

SQL & PL/SQL :: Fetching Records From Cursor

Jun 21, 2012

I have created one global temporary table in which I inserted 2 rows.

I am fetching the rows by using following cursor :

declare
cursor c1 is
select TTD_TRV_MODE
from global_tra_trv_dtl;
v_trv_mode varchar2(10);
Begin
open c1;

[code]....

But instead of 2 rows , 3 rows are getting fetched.

View 6 Replies View Related

SQL & PL/SQL :: Can FOR Not Be Used To Loop Through Records Of A Ref Cursor

Jul 1, 2011

I wrote the following block :

set serveroutput on
declare
rec employees%rowtype;
cur SYS_REFCURSOR;
begin
open cur for 'select * from employees where rownum<:a' using 4;
for i in cur
[code]....

It gave errors if we execute is as such, but worked when I commented out the for loop and instead de-commented the simple loop. Does that mean that FOR cannot be used to loop through the records of a ref cursor ?

View 14 Replies View Related

SQL & PL/SQL :: Retrieve Records From Sys_ref Cursor

Nov 25, 2010

I wrote the function witch returns some information.

function get_cust_info (v_msisdn integer) RETURN sys_refcursor
IS
curs sys_refcursor;
BEGIN
open curs for 'select first_name, last_name, street, town
from the_table where MSISDN = :1' using v_msisdn;
RETURN curs;
end

How to call this function to write result into table? I just want to write function which returns more Varchar.

View 6 Replies View Related

Dividing The Cursor Records Using Commit Limit

Sep 18, 2011

I have to optimize a batch job which returns > 1 lakh records . I have a commit limit being passed . I am planning to divide the cursor records for processing as follows. If the cursor suppose returns 1000 rows and the commit limit passed is 200 , then i want to fetch 200 records first , bulk collect them into associative arrays and then bulk insert into target table.

After this is done, i will fetch the next 200 records from the cursor and repeat the processing. I would like to know how i can divide the cursor records, and fetch "limit" number of records at a time and also be able to go to the next 200 recs of the cursor next time.

View 1 Replies View Related

SQL & PL/SQL :: How To Skip Some Records In Open Dynamic Cursor

Jan 3, 2011

After opening a dynamic cursor, usually fetch hit record into some variables. However, if I do not want to "FETCH INTO " operate Just only skip this record.

DECLARE
TYPE weak_cur_type IS REF CURSOR;
weak_cur_1 weak_cur_type;
weak_cur_2 weak_cur_type;
vs_dsql VARCHAR2(2048);
vd_create_time DATE;
vn_count NUMBER(8);
vn_total_amount NUMBER(13);

[Code]...

View 7 Replies View Related

SQL & PL/SQL :: Identify / Count Records In Ref Cursor Without Actually Fetching?

Jan 22, 2013

/* Formatted on 22/01/2013 19:32:50 */
CREATE OR REPLACE PROCEDURE test_rdm_miles (
p_ref_cursor OUT SYS_REFCURSOR
p_success NUMBER)
IS
BEGIN
OPEN p_ref_cursor FOR
SELECT 5168 mem_uid,

[code]....

I have a Procedure with out parameters as a REF CURSOR and response message as p_success.This ref cursor will be returned to the calling service. Is there a way in oracle by which we can identify whether the Ref cursor holds data without actually fetching it. Since if i choose to fetch the data, i will lose one row when i return the ref cursor back to the calling service Or else is there way i can retrieve the row i lose during fetch.

Other alternative what have been suggested is create an object type ,fetch the ref cursor values in object type. Then i can use the ref cursor to return the data by table casting.

one more solution is

OPEN
FETCH
CLOSE
OPEN (AGAIN) { this will lead to redundancy)

View 7 Replies View Related

SQL & PL/SQL :: Dynamic Update Of Cursor Records When Table Gets Updated Immediately

Apr 16, 2010

I am having a table with 4 columns as mentioned below

For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it

Table1

Col1prodvalue1rank
1A21
2A62
3A53
4B61
5B32
6B73
7C41
8C22
9C13
10C74

Output

Col1prodvalue1rank
1A51
2A52
3A33
4B101
5B02
6B63
7C51
8C52
9C03
10C44

I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).

This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.

View 9 Replies View Related

Server Administration :: Settings Infringe On Any Available Memory On System That Is Already Tight On Memory?

Jul 25, 2012

I have the following setup

SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3G
sga_target big integer 2G

from what I read I beleive this will initially grab 2GB of memory on startup and will grab up to to 3GB of memory total for the SGA. The "total" memory can be allocated to different peices of the SGA when needed but will never exceed 3GB. Is this correct or would these settings infringe on any available memory on a system that is already tight on memory?

Secondly, what happens if both these values are set to the same value?

View 8 Replies View Related

SQL & PL/SQL :: To Run Procedure In Oracle Automatically At 5 Daily

Sep 17, 2011

i want to run the salary posting procedure automatically at mid night every day. i have created a job in toad and defined the required information but it is not working.

View 13 Replies View Related

Oracle 10G Listener Stops Automatically

Mar 8, 2011

We have installed Oracle 10G in windows 2003 server. Oracle is working fine when oracle services are running in local system account, when we change the option �Log On As� from local system account to an domain id the oracle listener stops automatically.

I tried the below mentioned options, We created a new listener it did not worked . we are able to connect the db through command prompt but not through SQL plus or TOADWhen we add the domain id to the local administrator group the oracle is working fine. But we cannot have the domain id in local administrator group since GPO will automatically remove the domain id from local administrator group.

View 2 Replies View Related

Forms :: Oracle 6i Exiting Automatically?

Mar 31, 2013

After compiling in few minutes My Oracle Forms 6i Form is exiting automatically ... How to solve this problem.

View 1 Replies View Related

TimesTen In-Memory :: Process Of Initialization Of Database Into Memory

May 23, 2013

Can i know the internal process of initialization of DB into memory in timesten , when a new connection is establishing? Will timesten create tables and indexes in RAM when first connection is established if the RAM policy is default?

want to know the internal functional flow of timesten when any command is fired against it.

View 3 Replies View Related

Replication :: Oracle 9i - Does Not Push Transactions Automatically

Jun 7, 2002

I have problem with my Replication using oracle 9i.It does not push transactions automatically when refresh time comes but it works fine when pushed manually.I have two sites:

1- Master
a-Master group
2- Materialized view site
a- Materialized view group (Asynchronous)
b- Materialized view with refresh occur automatically on future date every 5 minutes
c- refresh type FORCE.

View 4 Replies View Related

SQL & PL/SQL :: Oracle Jobs Not Start Automatically As Per Interval?

Dec 20, 2010

My Oracle jobs are not running. I can run them manually but they don't start automatically as per interval. The job_queue_processes=100 and CJQ process is not running. I started CJQ0 process by resetting it to 0 and then to 100. But as soon as I enabled a job this background process stopped.

The job also did not execute in its next execution time. I have created the database manually without using DBCA. Is the problem because of this? I have set crontab immediately to get rid of it.

View 4 Replies View Related

ALL Scripts Which Automatically Get Run By DBCA In ORACLE 11G Enterprise Edition

Jan 19, 2013

which are ALL the scripts which automatically get run by DBCA in ORACLE 11G Enterprise Edition.

View 2 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

Forms :: How To Generate Numbers Automatically In Multirecord Table Using Oracle 6i

Sep 28, 2013

I have two control block i.e. class_register and student_info. In which student_info is multi-record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table. Now I want to generate stud_roll_no automatically until the last record is present.

View 4 Replies View Related

Enterprise Manager :: Oracle 11g Console Not Starting Automatically And Manually?

Jun 10, 2011

When I am trying to enter to the enterprise manager of oracle 11g, but it is not working. In my services list the 'oracledbconsoleorcl' is assigned as automatically to start with windows. But it is not starting. When I tried to start it manually the following message is displayed repeatedly even after restarting the pc.

'Windows could not start the oracledbconsoleorcl on Local computer. For more information, view the system event log. If this is a non-Microsoft services, contact the service vendor, and refer to service-specific error code 2.'
In the event log the following error messages are displayed:The OracleDBConsoleorcl service terminated with service-specific error 2 (0x2). and

The OracleCSService service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.

View 4 Replies View Related

Windows :: Oracle DB Out Of Memory Error

Sep 4, 2012

My database is working fine till y.day. But today when I try to connect as sysdba i get the following error- let me know how to resolve the same

C:UsersAdministrator>set ORACLE_SID=sha

C:UsersAdministrator>sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 4 05:11:14 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup mount

ORA-27102: out of memory
OSD-00026: additional error information
O/S-Error: (OS 1455) The paging file is too small for this operation to complete

View 4 Replies View Related

Oracle Apps 11i - Limited Memory Usage?

Nov 5, 2012

We are running Oracle Apps 11i on Windows 2003 R2 Enterprise SP2 (32 bit). After enabling /3GB switch parameter in Boot.ini file, the OS recognizes the 8GB RAM and consumes it upto 5.6GB during non-peak hours but as workload increases in evening when end users generate auto receipts the same memory usage ratio reaches upto 6GB resulting the OS gets hang and don't respond at all. Inspite of having 2GB free memory it doesn't consume it. We have to shut down it by pressing the power off button and restart it

The system configuration is as under:
HP ProLiant DL380 G6
Intel Xeon 2.4 GHz
8GB RAM (4 x 2 GB)

View 8 Replies View Related

Out Of Process Memory Error When Upgrading From Oracle 9.2.0.8 To 11.2.0.2

Apr 7, 2012

I got the following error when upgrading from Oracle 9.2.0.8 to Oracle 11.2.0.2 version using catupgrd.sql script.

=================================================================
CREATE OR REPLACE PACKAGE BODY kupm$mcp wrapped
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ERROR:
ORA-03114: not connected to ORACLE

SP2-1519: Unable to write to the error log table sys.registry$error
ORA-04030: out of process memory when trying to allocate 8392728 bytes (pga
heap,redo read buffer)
ORA-04030: out of process memory when trying to allocate 8392728 bytes (pga
heap,redo read buffer)
ORA-04030: out of process memory when trying to allocate 8168 bytes
(callheap,kcbtmal allocation)
Process ID: 81984
Session ID: 1 Serial number: 5

==========================================================

My machine environment is:

UNIX AIX 5.3 level 12
Oracle 11.2.0.2
RAM size 2GB
ulimit settings:
> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) unlimited
coredump(blocks) 2
nofiles(descriptors) unlimited

Currently i tried to increase SGA & PGA to possible extent, but still getting this error? Is there any change required in ulimit, RAM etc?

View 1 Replies View Related

RAC & Failsafe :: Memory Usage Increasing On Oracle

Jan 10, 2012

After the reboots each system was using around 30 GB of memory... now that it's been up for a week memory is up to 98GB used on each system. None of the systems are swapping.

Mem: 98999084k total, 97937116k used, 1061968k free, 774900k buffers
Swap: 16779884k total, 0k used, 16779884k free, 89510312k cached

View 2 Replies View Related

Oracle 8i Shared Realm Memory Error

Jul 2, 2012

My environment is Windows XP PC

Ram - 1 GB
Oracle version 8.1.7.0.0

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Even i start the Oracle service through the services.msc there are no new entries seen in alert log file of the database.I even changed the init DBNAME. ora file but that file is not being read. what is the normal procedure used by oracle 8i while starting the DB ? Which file should be edited if i come across shared realm memory error.

View 6 Replies View Related

Recovery Area Cannot Be Used For Archive Log?

Sep 13, 2013

I`m using the the version 11.0.2.3 for testing.  I read a line as below from the OCP book.

If none of the LOG_ARCHIVE_DEST_n parameters have been set, then transitioning. the database to archivelog mode will internally set LOG_ARCHIVE_DEST_10 to the flash recovery area, if it has been configured. In my database the fast recovery already been set as below.

SQL> show parameter db_recovery
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FASTRECOVERY
db_recovery_file_dest_size           big integer 4248M

So I thought if I turn the database into archive log mode, then the log_archive_dest_10 will be set as the same value as db_recovery_dest.

SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> show parameter log_archive_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string

See, after the database become archive, the log_archive_dest_10 still be empty.  So is there any thing wrong with my understanding?

View 5 Replies View Related

Performance Tuning :: Memory And SGA Target In Oracle 11gR2?

Dec 29, 2012

I am aware that from 11g, memory_target is sufficient for memeory management between SGA and PGA.

what happens if MEMORY_TARGET set to non-zero and SGA_TARGET set to zero values in a 11g database? Does it enable automatic memory management within the SGA?

We regularly hit by ORA-4031 errors. Also, memory_target advisory (v$memory_target_advice) does not show any advisory information.

for eg:
memory_max_target = 500m
memory_target = 500m

and

sga_max_size=500m
sga_target=0

View 6 Replies View Related

Performance Tuning :: Memory Parameters In Oracle Database 9.2.0.8

Nov 18, 2012

I have a question regarding memory parameters in oracle database 9.2.0.8, especially sga_max_size and db_cache_size. Database server has 32G of ram. Oracle parameter on server shmmax is set to 16G. Is reasonable to set sga_max_size to the same value, and db_cache_size to 80% of that size?

View 2 Replies View Related

ORA-01034 - ORACLE Not Available ORA-27101 - Shared Memory Realm Does Not Exist

Apr 7, 2012

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

connected as- / as sysdba
Connected to an idle instance.

SQL> startup
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01263: Name given for file destination directory is invalid
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>

ORACLE_HOME is - E:\oracle\product\10.2.0\db_2
ORACLE_SID is - orcl11

I'm using Oracle 10.2.0 in windows 7.When i installed oralce it worked properly for 1 week, now throwing an error ora-01034 and ora-27101.

I just perform CRUD Operations with database as a java dev by connecting via MYEclipse, but it is not at all allowing me to connect.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved