SQL & PL/SQL :: Unable To Query Table - Fetching Data Timing Out?
May 1, 2011
User complaints fetching data from table A is timing-out ( as per application standards). I decided to collect latest statistics(dbms_stats) on index used by query which is running for more than 12 hours.not.It is partition table and got close to 10 millions records.
1) I checked dba_waiters - no rows
2) I checked v$locked_object/v$locks - no rows
3) I'm unable to run explain-plan for any criteria on that table
4) I'm unable to open table data-tab in Toad/ Sql developer.
5) I'm unable to query a single partition; no error but it keeps on running.
View 8 Replies
ADVERTISEMENT
May 29, 2011
here i have a table called cn_wghmtdt_trn and some table columns are shift_date(date),net_wt(nothing but crushing weight or cane wt),crop_type_code(rotoon or plant),shift_code(its like 1/2/3 but its default 1)
and we have fetch the
1)today crushed weight
2)shift crushed weight
3)till date cane crushed,
4)total rotoon crushed,
View 5 Replies
View Related
Nov 3, 2011
I am working on forms 6i. I have one data block based on table EMP.
Datablock-A: Empno, Ename, Sal
I have FIND Screen: Empno, Ename, FIND Button.
I can search the data through FIND Screen and populate data in Datablock A.
I am using below logic to search data through FIND Screen.
When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.
In Pre-query trigger of DatablockA:
copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');
It's working fine. But below case is failing.
Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.
But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.
why it's failing to pull records if i try to query data in first case only.
View 2 Replies
View Related
Mar 23, 2012
I have dcs_sku table .The record count is 50 thousand in that table.My requirement is to fech every row,create an xml out of it and post the data to some third party.As the count is very huge,I can't select the entire record and do the operation at a time.way which I will run the sql query in a loop,which will fetch 1st from rown 1 to row 1000,next 1001 to 2000,2000 no 'n' row...
I tried the below query:
select * from dcs_sku where rownum between 1 and 200...This gave me the 1st 200 rows and worked fine.
but the moment I changed the query to :
select * from dcs_sku where rownum between 201 and 300:::No result was coming up.
View 1 Replies
View Related
Nov 30, 2012
I have a table structure and data as below.
create table production
(
IPC VARCHAR2(200),
PRODUCTIONDATE VARCHAR2(200) ,
QUANTITY VARCHAR2(2000),
PRODUCTIONCODE VARCHAR2(2000),
MOULDQUANTITY VARCHAR2(2000));
[Code].....
Now here i want to fetch data having condition as
PRODUCTIONDATE >= Monday of current week
so i would skip only first two rows and will have to get all rows.
I tried using below condition but it would give not give data for 2013 values.
to_number(to_char(to_date(PRODUCTIONDATE,'yyyymmdd'),'IW')) >= to_number(to_char(sysdate, 'IW'))
View 5 Replies
View Related
May 10, 2013
I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.
--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))
--insert into t1(col1, col2, flag) values(1, 'a', 'Y');
--insert into t1(col1, col2, flag) values(2, 'b', 'N');
SELECT *
FROM t1 x
WHERE col1 = 1 AND col2 = 'a' -------------- condition1
AND 0 = -------------- condition2
NVL (
(SELECT COUNT (1)
FROM t1 y
WHERE y.flag = 'N'
AND x.col1 = y.col1
AND x.col2 = y.col2),0)--=0
When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.
View 9 Replies
View Related
Jun 1, 2012
I don't have any dba privileges, can you share a scripts which can tell how many block my query is fetching with or without indexes. How do i also get buffer hit, how can i get i/o without sql trace as i don't have access to dump_dest
I have a below query
SELECT DISTINCT ser_id AS STA_ser_id, rct_name AS STA_name
FROM sd_servicecalls, rep_codes, rep_codes_text
WHERE ser_sta_oid = rcd_oid
AND rcd_oid = rct_rcd_oid
AND rct_name IN ('New', 'Awaiting Approval', 'Approved', 'In Progress', 'Awaiting Supplier', 'Awaiting RFC', 'Awaiting Release', 'Pending Release', 'On Hold', 'Resolved', 'Implemented', 'Closed');
Does large hash value in explain plan mean more resource needed and more time to execute the query, How can i use ADDM for the above sql.
View 7 Replies
View Related
Jun 26, 2008
I am using Pro*C/C++ Release 10.2.0.2 in HP-UX. But Pro*C/C++ application was written long back during oracle release 8. Now we are facing a problem like EXEC SELECT query is not fetching the records even though record is present in DATABASE.
This is not happening every time. This problem starts happening only after heavy use of the unix process.
For every request, unix process will fetch the record and updates the same at the end and process goes to wait mode to get the request again. Let say after 50 request, process is returned with no rows found error.
It started working fine only after restarting the process and problem starts again after 50th or 60th request. This problem we are facing only after upgrade to 10g.
View 1 Replies
View Related
Jun 20, 2012
I have two different database servers where I need to migrate table data from one schema to another schema in batch wise for eg say 100 rows. I used BULK COLLECT with LIMIT. But to access BLOB data from table I have facing errors. What could be other approache to do the same.
here pc_work is a table containing BLOB data in sourse schema. I am fetch data from this table to table t1_test_work using dblink but not working
[
declare
type array is table of test_work%ROWTYPE;
L_DATA array;
cursor C is select * from pc_work@prpctrg;
begin
open C;
LOOP
[Code]....
View 3 Replies
View Related
Jun 16, 2011
In my select statement i am fetching the data from
OE_ORDER_HEADERS_ALL,OE_ORDER_LINES_ALL,WSH_DELIVERY_DETAILS,WSH_SERIAL_NUMBERS.
I need appropriate links for that tables.
View 2 Replies
View Related
Mar 11, 2013
I am fetching data which are older than 2 hours from now. TRANSACTION_TIME is varchar2 field in "MM/DD/RRRR HH:MI:SS PM" format.
SELECT * FROM TRANSACTION_DETAILS
WHERE TO_DATE(TRANSACTION_TIME,'MM/DD/RRRR HH:MI:SS PM') <(SYSDATE - 2/24);
Is there anything wrong in the query.
View 2 Replies
View Related
Oct 24, 2010
The below query take time to fetch the data about 25 seconds for 9 rows.
select uctb.member_code As Member_ID,
((uct.price * uct.quantity * i.multiplier) ) As Traded_Value_In_Lacs,
sum(uct.price * uct.quantity) As Total_No_of_Trades,
sum(uct.quantity) As Total_Volume_Of_Trades
[code]......
View 2 Replies
View Related
Apr 11, 2013
I wanted to fetch a records from a table with no. of records and segment_name,owner,size of the table.So, i wrote a query like,
select owner,segment_name,bytes from dba_segments where segment_name='EMP';
then it is working but when i use count(*) for no.of records means how many records this table contains that time, it is showing an error.
View 9 Replies
View Related
Feb 8, 2011
SELECT
contgrp_num,
cpgrp_desc,
strategy_id,
perftyp_cd,
stiertyp_id_calcbase,
stiertyp_id,
cpgrp_flg_aggr,
pgrptyp_cd
[Code]..
The above query is not returning any value. But, when I put this within a begin-end block, its fetching data.
why its not returning any data without the begin-end block.
The entire code has an outer begin-end block within which the cursor is also defined.
View 1 Replies
View Related
Sep 7, 2010
I have a detailed block with a user ID column. The user name is not available in the block. Therefore, I have created a non-database column to retrieve the user name into it.
Here is the code I've used.
PROCEDURE get_details IS
iLoop number := 1;
CURSOR c is
SELECT FULL_NAME
FROM GRP_EMPLOYEE
WHERE EMPLOYEE_NUMBER = :USER_ID;
[Code]...
However, it retrieves the first record only.
The results are displayed like this.
Loop: Result:
1 Full_Name
2 Empty
3 Empty
4 Empty
View 2 Replies
View Related
Aug 18, 2011
I am trying to pass the concatenated value as parameter to the select statement. But it is not returning the results.
If i pass mytrx(1) := '123' it is working fine. It is failing when i pass more than one value.
Table script
create table xxtesttrx (trx_number varchar2(50));
insert into xxtesttrx
values('123');
insert into xxtesttrx
values('456');
Code
declare
-- declare the table type
TYPE xxtest IS TABLE OF
xxtesttrx.trx_number%Type
INDEX BY BINARY_INTEGER;
[code]......
View 2 Replies
View Related
Mar 6, 2013
I have a schema in which i have 10000 tables and i want to fetch table names from the schema where org_id is not equal to 1,
Note: Here org_id is the column name .
Provide me sql code or procedure to achieve this.
View 2 Replies
View Related
Dec 16, 2008
I want to insert XML data into my ( Oracle 11G Release 1 ) XMLType table using OCCI. I'm getting
ORA-01461: can bind a LONG value only for insert into a LONG column
My XML data size is around 1.5 to 2MB. I have also tried using setMaxParamSize before calling the setString method. But, still I'm getting the same exception.
How to resolve this issue?
View 2 Replies
View Related
Sep 9, 2011
I have one schema argus_app where there is a table cfg_enterprise. There is a view v$cfg_enterprisewhich selects data from cfg_enterprise and there is apublic synonym cfg_enterprise for v$cfg_enterprise.
When I login to argus_app and select from cfg_enterprise there is no data where as i log in to sys and select I can see data.
SQL> conn /as sysdba
Connected.
SQL> select enterprise_name from cfg_enterprise;
ENTERPRISE_NAME
[code]...
what would be creating this problem.
View 5 Replies
View Related
Apr 2, 2013
My job is running at 2 am and that time no application user is connected. Even though, my exp utility shows error on 3 tables (2 are temp tables), everyday. But when expdp is running without error, which was scheduled at 4 am.
Below are the error -
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . exporting table DW_TEST_MOTOR
EXP-00056: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed
[Code].....
View 9 Replies
View Related
May 24, 2013
IS it possible TO schedule same JOB WITH different timings.
I need only one SCHEDULER which should run ON saturday AT 6 pm ,AND monday AT 9 pm.
CREATE TABLE dept1 AS SELECT * FROM dept;
CREATE OR REPLACE
procedure update_record is
BEGIN
insert into dept1 values(10,'xxx','xxx');
END;
[code].....
View 7 Replies
View Related
Aug 28, 2012
I have the following query
Select ei.emp_code,
TO_CHAR(ei.intime, 'dd/mm/rr') "IN Date" ,
--TO_CHAR(ei.intime, 'hh24:mi:ss') IN_Time,
MIN(TO_CHAR(ei.intime, 'hh24:mi:ss')) IN_Time,
TO_CHAR(ei.intime, 'FMDAY') IN_DAY
[Code].....
no I want to modify this query, I mean I want to get data between time 09:15 to 09:12 In time.
View 3 Replies
View Related
Aug 23, 2008
how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.
View 6 Replies
View Related
Apr 17, 2012
Just want to know about archive log shipping time from production to physical standby. How can i know the timing that ARCHIVE LOG takes to reach from production to standby server.
I think we can get this through ALERT log but m not sure.
View 1 Replies
View Related
Jul 16, 2011
I'm facing a problem where I create a form and I want to feed time in "HH:MI" format, Firstly I choose the "Date" datatype, but during the feeding of time i.e "00:30", means if I want to feed only mints, then it doesn't accept, at the other hand if I feed "01:30" means mints with hr then it accepts.
To get rid of this problem I changed datatype "Date" to "Varchar2".
I create 3 columns on my form "TTL_WORKING_TIME" - "TTL_RUN_TIME" = "BRK_DWN_TIME". If I get any value in "BRK_DWN_TIME" column thn it has to be distribute in 3 or 4 reason of B/D, means if I get 00:30 mints brkdwn thn 00:15 mints for "reason1" and 00:15 mints for "reason2".
How I make calculation where I use "Varchar2" datatype.
View 1 Replies
View Related
Jul 20, 2010
I have a database job to ANALYZE TABLES,, it takes more than 40 hours. I want to know exactly how much time does it take..
My job has started on 19th Jul at 02:32 hrs.. What is the way to know the exact time when the job ends.
View 4 Replies
View Related
Jun 23, 2010
SL_NoStatus
191502Implemented
191690Implemented
190998Implemented
191346Implemented
190541Implemented
[Code]..
my above table consists of two columnc sl_no and Status,col1 indicates the process no and the status indicates it is implemented or cancelled or failed during implementation.
i need to find the percentage of the implemented+cancelled process over failed..
test case:
lets consider,
A->count(Sl_NO)
B->Count(STATUS) where STATUS='Implemented'
[Code]..
i think i have satisfactorily given enough data.. make it out using sql query..
View 3 Replies
View Related
Sep 7, 2012
Here is my case,
create table t (id number,row_id number primary key ,value number);ID row_id value
1 1 10
1 2 11
1 3 1
2 4 11
3 5 11
3 6 12
4 7 12
4 8 12
4 9 13
4 10 11
4 11 10Requirement is
1) To get all the ID that have the value 10
2) The row_ids for the ID I got in I
Can I do this with Hierarchical query. If not which one of below will be fast?
select * from t where id in (select id from t where value=10);
or
select T2.* from t T1, t T2 where T1.ID=t2.id and T1.VALUE=10;
I have billion of rows so I am looking for either Hierarchical query to solve it or some other way . All the three column the index.
View 5 Replies
View Related
Oct 22, 2012
the query to copy the data in last 24 hours from one to another table
Database Version :- Oracle 11g
View 2 Replies
View Related
Oct 20, 2013
Tablespace usage alerts are checked every ten minutes. You can request a check every minute when you set the threshold, and confirm that this has been set:
orclz> execute DBMS_SERVER_ALERT.SET_THRESHOLD(-
> metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,-
> warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> warning_value => '50',-
> critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> critical_value => '75',-
[code]....
orclz>but you will still have to wait up to ten minutes for the alert to be raised. know whether this frequency can be changed? And why this particular alert behaves differently from all the others? This is the bahaviour in all releases since server alerts arrived.
View 2 Replies
View Related
