SQL & PL/SQL :: What Is A Global Procedure In Oracle And Its Usage
Aug 6, 2010what is a global procedure in oracle and its usage
View 5 Replies
ADVERTISEMENT
SQL & PL/SQL :: Usage Of Cursors In Stored Procedure
Feb 22, 2012i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
SQL & PL/SQL :: Usage Of Loop - Creating A Procedure With 4 Input Parameters
Jun 6, 2012I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.
create or replace procedure PRC_add_data(
P_Emp_No varchar2,
P_Member_Name varchar2,
P_IDvarchar2,
P_UHID varchar2
)
is
BEGIN
INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID)
END;
/
SQL & PL/SQL :: Executing A Procedure Containing A Global Temporary Table
Mar 3, 2010create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';
stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;
-- when am trying to execute this
begin
test;
end;
-- showing ora-01031, insufficient privileges.
Creating And Dropping Global Temp Tables Inside A Single Procedure
Aug 20, 2010I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
SQL Query To Find CPU Usage And Memory Usage
Jun 28, 2013Is their any query to find cpu usage & memory usage of all the queries currently running on DB?
View 5 Replies View RelatedCDC Publish / Subscribe Usage With ETL On Oracle 10g
Oct 12, 2012I have a question about the CDC publish/subscribe usage with ETL on Oracle 10g. Is the ETL logic code part of the subscriber part? In other words, when the publisher is notified of a record change and sends the record to the staging area then calls the subscriber. Is the subscriber that runs the ETL logic that processes the record before it is sent to the DW/Dim tables? Something like this flow:
Source Database ---> Change table/Publish ---> Subscribe view/ETL ---> Target Database
Oracle Apps 11i - Limited Memory Usage?
Nov 5, 2012We 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)
RAC & Failsafe :: Memory Usage Increasing On Oracle
Jan 10, 2012After 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
Server Administration :: CPU Usage By Oracle 10g Database
Aug 16, 2012I have three oracle 10g databases (db1, db2, db3)on a linux box.
A command at the OS level to find total cpu used by each of the above databases.
Oracle 11g - Global Database Name Value?
Sep 22, 2011When I installed Oracle 11.g I set 'GlobalDataBaseName' ("orcl.net").SID was "orcl". from where can I invoke 'GlobalDataBaseName' value ?
View 1 Replies View RelatedQuery To Check Disk Space Usage For Oracle
Apr 21, 2010What is the query to check disk space usage for oracle 10g ? What is the query to check percent of disk space usage for oracle 10g ?
View 1 Replies View RelatedRAC/ASM Clusterware Installation :: CPU Usage On Oracle Cluster On AIX 6.1 With No Load
Aug 22, 2012After installing a 4 node cluster 11.2.0.3 with 16 CPU's (4 on each node) on IBM 795 with aix 6.1 each server is using 0.5 CPU with no user load on the system. Running SIHA on one server typically uses 0.05 CPU with no user load on the system.
View 5 Replies View RelatedForms :: Define Global Variable In Oracle 6i
Aug 12, 2010I want to define global variable in oracle form 6i....so this variable may i used anywhere in project...but i don't know where exactly global variable defined?
View 7 Replies View RelatedPerformance Tuning :: Oracle 11g High Memory Usage Dbw0 And Dbw1
Jul 31, 2012We are using the 11g AMM feature and Memory_Target set to 96GB and total RAM on the Server is 128GB Now the top and free shows up only 200MB memory free on the system.
There are 2 process dbw0 and dbw1 which consumes the top memory and this is 30GB per dbw.
Why is the dbw process taking up so much memory when there is not much load on the database.
Performance Tuning :: How To Determine System Memory Usage By Oracle Processes
Jun 20, 2012we have 96GB Memory on the UNIX server and 85% of its usage shows oracle processes I want to determine which Oracle processes are taking most of the memory
SGA is around 36G
SGA_TARGET is 40G
PGA is around 4G
the total of around 40-45 GB of usage is understandable but what other oracle process are chewing up the remaining 30-40 GB on the server is not known
load averages: 7.35, 6.46, 6.15; up 248+11:33:21 12:25:03
2202 processes: 2196 sleeping, 1 zombie, 5 on cpu
CPU states: 83.8% idle, 10.5% user, 5.8% kernel, 0.0% iowait, 0.0% swap
Memory: 96G phys mem, 15G free mem, 128G total swap, 128G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
21720 oracle 258 0 0 40G 40G cpu/48 215:28 2.04% oracle
10709 oracle 1 0 2 1816K 1448K cpu/9 0:02 0.90% res_conf_email_
[code]......
Forms :: Oracle Global (variable Name) Can't Hold 4000 Characters
Dec 13, 2011 In current form, i use *pll file to pass Global.<variable name> to the form *.fmb
The problem is that if i copy a string of 4000 characters (which i need to) to Global.<variable name>, it will automatically cut a whole chunk to shorter string (less than 1000).
Is there a better way to that Global.<variable name> can hold 4000 characters?
SQL & PL/SQL :: Regarding NVL Function Usage
Oct 20, 2011There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Now when I run the 'select mgr from emp e;' query the output which I get is:
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .
SQL & PL/SQL :: NULL Usage?
Mar 31, 2011 when to use || NULL oerator or what is the meaning of this statement when it is used with the column name.
and A.RtmtCd = B.RtmtCd|| NULL
or
A.RtmtCd||NULL IS NOT NULL
PL/SQL :: How To Check CPU Usage
Mar 6, 2013I have one procedure which is executing daily two times(i.e morning run and evening run) . In morning run it's executing around 150 mins and evening run executing around 25 mins.
It's happening for all procedures executing in longer run in morning. There is NO LOCKS, I have checked LOCKS while executing morning run. I suspects CPU usage in morning run.
How to check CPU usage ? AND also each session how much utilizing CPU. I'm using oracle 8i database, I know it's older version but my company is using oracle 8i. So i have to look in this database version only.
SQL & PL/SQL :: Usage Of Hints In The Views
Oct 11, 2011I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows
create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);
my question is how to use hints on this table if your are using view to access the data from internal table.
Normal structure is if i don't wrong:-
index( <<view name|view alia name>> <<table name|table alias name>> name of index)
Consider my case
select * from view_t130 where <index_column> --not picking up index
i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.
select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>
how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.
SQL & PL/SQL :: Function BFILENAME() Usage?
Jul 31, 2011 I had a question on BFILENAME() :
first,
create or replace directory UTL_FILE_DIR as 'k:lob_test';
GRANT READ,WRITE ON DIRECTORY UTL_FILE_DIR TO user1;
and then ,I login use user : user1
create or replace package body p_utl_mail_attach_raw as
procedure send_single_user_attach_raw(........)
fil BFILE;
filenm VARCHAR2(50) := 'aaa.jpg'
begin
fil := BFILENAME('UTL_FILE_DIR', filenm);
end ;
this result it show :
UTL_FILE_DIR/aaa.jpg, exists=F, length=0, open=F
SQL & PL/SQL :: Conditional Sequence Usage?
Jan 31, 2013I'd like to have a conditional that only gets the next value from a sequence is the current (or supplied) value is null.
Here are some trivial examples:
CREATE SEQUENCE ts
MINVALUE 1
START WITH 1
INCREMENT BY 1;
SELECT ts.NEXTVAL FROM DUAL;
SELECT COALESCE(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT NVL(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT CASE
WHEN ts.CURRVAL IS NOT NULL THEN ts.CURRVAL
ELSE ts.NEXTVAL
END
FROM DUAL;
Ideally multiple executions of any of these (or a better one of your design) should return the same value from the sequence, but mine do not.
Created Partition - Usage?
Nov 9, 2012I have one table and I created partition month wise. I also creates index on that table. How can I check whether parition will be used when I query that table? In explain plan I can see PARTITION RANGE as ALL. Is it using the partition?
View 2 Replies View RelatedHow To Find CPU Usage By Each User Session
Sep 7, 2011I can get "CPU used by this session' value from v$sesstat. That is the value in centiseconds used by each session. How to convert the value into a percentage of total CPU resource ?
Or there are some other ways to gain the value. i.e. Percentage of Total CPU usage for each Oracle session.
Analytic Functions / TEMP Usage?
Jan 26, 2011I am building a reporting table using the count analytic function in order to count up several different attributes in one statement.What I find is that this method quickly eats up my TEMP space. This is 10gR2. I have attempted to use MANUAL workarea policy with as large ofsort_area_size as possible (2G) but that does not seem to have any effect on performance or TEMP usage. The RAW table is about 12G with 75 million rows. I am not that concerned about execution time, but rather TEMP usage.
--INSERT into <object>...
select distinct
file_sid,filename,control_numb,processing_date,file_class,
vendor_id,vendor_desc,
c_status_id,c_status_desc,
[code]...
I am not seeing any increase in onepass or multipass executions on the PGA during execution of this statement using...
SELECT CASE WHEN low_optimal_size < 1024*1024
THEN to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(high_optimal_size+1)/1024|| 'kb'
ELSE to_char(low_optimal_size/1024/1024,'999999') ||
[code]...
I'd like to get a better explaination of how analytics use the instance resources and TEMP space. For example if I add
a count with a different window (such as the last two columns commented in the above query) I blow out my temp space (70G).
Is the critcal factor the use of distinct? or multiple windows? or something else?
SQL & PL/SQL :: Instr Function And Index Usage
Apr 14, 2012I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.
CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);
The below query is going for full table scan due to this.
select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2
how to recreate the above index so that these queries uses this index.
Listing Usage To Get O/p In A Specific Form?
Mar 18, 2013 I have a table with data in below format:
Table A
Id Value
1 R
2 S1
2 S2
3 T
the o/p in below format:
Col
R|S1|T
R|S2|T
The general is that multiple value for any given id must be aggregated separately.
Performance Tuning :: TEMP Usage History
Jul 19, 2010My stats jobs failed last night with "ORA-01652 :Unable to extend TEMP" error.
Is there any way to check this history data, what other session was using TEMP tablespace extensively ?
SQL & PL/SQL :: Usage Of Context Variables Inside The Trigger?
Feb 17, 2010Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query when used inside the trigger code will return the trigger owner.
Select Username from User_Users
Will any of the below variables return the trigger owner when used inside the trigger code
sys_context('USERENV','CURRENT_USER')
or
sys_context('USER','CURRENT_SCHEMA')