At a customers site I see lot of long lasting library cache locks during a complex ETL run. Several Sessions run in parallel and create Database tables with dynamic sql ( CREATE TABLE AS SELECT ....) .
Sometimes these procedures wait for each other with wait event 'Library Cache' . I presume that this is a side effect of the dynamic DDL in the stored procedures. Is that possible even when the Procs create different Tables but reference the same tables in their SELECT clauses ?
I presume that this is plausible but I need some arguments to convince my colleagues.
i am doing a data transformation using the following function
FLOOR(ONSET_TPQ-VAX_DATE)
Where ONSET_TPQ and VAX_DATE are both dates. Now in a situation where the ONSET_TPQ precedes VAX_DATE I want the result to be null for those records. As an example if ONSET_TPQ= 2000 and VAX_DATE=2010
I want the result to be NULL as I dont want negative values. Any exact SQl syntax on how to do that. DO i write a case statement?
1)If i issue a DELETE statement to delete a row, will this statement drag any data from the datafile to database buffer? How is the change made by a DELETE statement recorded in buffer cache? How is this change then applied to the data in datafiles after commit?
Say Database Buffer Cache configured as 2M and my updates may use 4m size,will it throw an error message or update will happen perfectly without any issues?
Currently there is only one report server for publishing reports in our application server . Its a standalone installation, oracle load balancing is not installed.I am thinking about starting atleast three more report servers to increase the performance
1) Having multiple report server increase performance or not? 2) Oracle report server load balancing how it is configured on same one sever?
Is there any script to run or a way in oracle database to get the whole database structure such as (Tablespaces, schemas, datafiles, redo-log files, tables(structure), views....etc) plus the number of daily transactions and operations happens such as (insert, delete, update,...etc)
Generally, a fully detailed report of my database structure, changes in structure and daily DML operations.
I have an AWR report based on two snapshots one an one hour time window, under the "SQL ordered by Elapsed Time" section, there is a query having Elapsed Time = 7012 seconds, which is almost two hours, how is this possible? I have been told that this i aggregate value of all sessions.
create table ex (sno number,ename varchar2(10),job_code char(4),sal number); insert into ex values(101,'John','Java',21000,'IT'); insert into ex values(102,'Michel','BI',25000,'IT'); insert into ex values(103,'Johny','Java',30000,'IT');
SQL> select name,decode(unit,'bytes',value/1024/1024,value) as mb from v$pgastat; NAME MB---------------------------------------------------------------- ----------aggregate PGA target parameter 25600aggregate PGA auto target 2724.14648global memory bound 1024total PGA inuse 22601.7333total PGA allocated 26653.6230maximum PGA allocated
[code]....
I understand I have soft limit( aggregate PGA target parameter) which was overlimited (maximum PGA allocated = 35374.4638) hence we have over allocation count>0.Extra bytes read/written=13GB,hence we have excessive 13Gb that we had to flush on disk(excessive I/O operations) cause of limitation in 1024MB(global memory bound)(it's not enough to join or to sort something so we must do onepassor multipass) ,which defines the size of single operation of sort or join(so does it mean that it's some kind of sort_area_size and hash_area_size for automatic workarea_size_policy? and in this case what about _smm_max_size?)aggregate PGA auto target - is the amount of space(total) that Oracle can give for work areas running in automatic mode.
So I can't understand ratio between global memory bound and aggregate PGA auto target - why does the aggregate PGA auto target such tiny?(relatively process count)?Is the global memory bound static for particular aggregate PGA target parameter?
I can change it only by redefining aggregate PGA target parameter?What would be with aggregate PGA auto target if I started 10 sort operation and each of them takes about 1Gb of memory.How huge it would be? 10Gb?
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
When I use JDBC to retrieve the data and convert it to mill seconds, and it also shows as negative time. My question is the retrieved time doesn't show the negative date, but obvious it is a BC date and earlier than 0001-01-01.
1. How can we detect/show the date to show correctly for the BC date without checking c.time < to_date('0001-01-01','YYYY-MM-DD')? (I am wondering if there is format configuration to show the time like 17-JUN-08 04.06.22.893 PM BC, or something to show the time is negative )
I am working on a script that is reading from a numeric column converting it to a character string where the decimal is dropped (assumed) and the value is padded with leading zeros to fill the specified lengh. Using to_char(payment_amt,'FM0000000V00') is working fine for non-negative values. When the value of the field is a negative number the output includes a leading - (i.e., -000002458).
For the negative numbers I need to convert them to a 'Signed EBCDIC' value, where the last digit is converted to a specified character (in the above example it would be changing the 8 to a Q) and also replace the - with a zero, resulting in 00000245Q.
What I am doing is generating a fixed-length record with values in a specific location. What I'm getting now looks like this:
S -- format mask display both sing + and - MI -- format mask display negative sign at end PR --Returns negative value in <angle brackets>. and Returns positive value with a leading and trailing blank.
I want the output should display only leading negative if the value is negative. I have used S99990.99 as format Mask. but need output should display only leading negative if the value is negative.
SELECT id, pob,exp FROM emp where exp= (:exp)or @exp is null O/P: id pob exp _____________________ 1 CT 2 2 NJ 3 3 NY 2
It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values .
