PL/SQL :: Deleting Large Number Of Rows From Table
Apr 30, 2013
Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.
Method I have used:
1. Created Temp table
2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.
delete from B where exists (select 1 from temp where b.col1=temp.col1);
3. Why it is taking too much time for deleting records in table A.
Is there any thing that during deleting data from such master table, it is referring to all dependent tables even if dependent data is not present ?
View 12 Replies
ADVERTISEMENT
Nov 29, 2012
I have a query that returns 11 Million rows but not all of them can be displayed in SQLDeveloper or DBVisualizer because of limited memory or other type of issues. I need to copy the entire result set to excel for further calculations.
Is there any way that i can select N number of rows out of my actual result set.
For example:
a) A result set contains 10 Million rows in total.
b) I want to display first 5 Million rows by executing a query
c) Then I want to display the remaining 5 Million rows by executing the query again with any parameter changes.
So all I want is to extract the rows of my actual result set in two or more executions, depending on the number of rows.
View 3 Replies
View Related
Oct 8, 2013
I have a report with single row having large number of columns . I have to use a scroll bar to see all the columns. Is it possible to design report in below format(half columns on one side of page, half on other side ofpage :
Column1DataColumn11DataColumn2DataColumn12DataColumn3DataColumn13DataColumn4DataColumn14DataColumn5DataColumn15DataColumn6DataColumn16DataColumn7DataColumn17DataColumn8DataColumn18DataColumn9DataColumn19DataColumn10DataColumn20Data I am using Apex 4.2.3 version on oracle 11g xe.
View 2 Replies
View Related
Aug 3, 2011
Can we select table's rows of 5 to 15, like
select * from emp
where rowid <=5;
It is through an error when i used the below method.
select * from emp
where rowid >=5 and rowid <=15;
View 4 Replies
View Related
May 17, 2012
I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..
Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
View 12 Replies
View Related
Dec 13, 2011
how to update the middle of plenty rows in the middle of the columns
sample_data
id name state REGION LOC
1 v A.p 1 1
2 a
3 g K.A 0 3
4 y
5 i T.N 1 0
6 l M.P 0 1
7 c U.P
This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,
View 8 Replies
View Related
Mar 29, 2013
generate the number of rows based on table field.
Sample code is given below.
CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING
View 4 Replies
View Related
Sep 8, 2012
how to resolve the following issue ...
insert into GTT (select query) inserting less no. of rows than that returned by the query.
The global temporary table has ON COMMIT PRESERVE ROWS.
View 3 Replies
View Related
Nov 8, 2010
I was asked in a telephone interview about committing a delete ( a few million rows)in an oracle log table which had a trillion rows in total. He said that delete took 2 days.
Then he asked me if then commit is performed(assuming a huge rollback segments are allocated) how long does it take for that commit .
View 5 Replies
View Related
Sep 4, 2012
What is the first term in the Fibonacci sequence to contain 1000 digits?
DECLARE
f_num INTEGER;
n_num INTEGER;
h_num INTEGER;
[code]...
why I am getting this error?? Is there any problem in my code??
View -1 Replies
View Related
Feb 14, 2012
POWER(47.3616, 27.1261) returns: 2.80403309600359E45 which causes "number precision too large" error in my variable which is defined as NUMBER(20,20).
I've tried ROUNDing and TRUNCing the product, but still get the same long output. The only thing that seems to work is SUBSTR.
Is that the ONLY way to deal with this? It seems like there would be a better way or is there another type I should use these super long decimal results?
SQL> SELECT POWER(47.3616, 27.1261) AS Exp_Val
2 FROM dual;
EXP_VAL
----------
2.8040E+45
SQL>
[code].....
View 2 Replies
View Related
Dec 7, 2012
How many types of pl/sql value or numeric errors are there and when occurs.Like as follows
1.ORA-06502: PL/SQL: numeric or value error: character string buffer too small
2.ORA-06502: PL/SQL: numeric or value error: number precision too large
View 8 Replies
View Related
Aug 6, 2010
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
ORA-01704: string literal too long
2. The function is going for an infinite loop.
View 10 Replies
View Related
Nov 5, 2012
I need to generate random numbers from the range 71 million to 90 million. While generatind the same iam getting the value with decimal places.
How to generate with out decimal places ?
SQL> select ABS(dbms_random.value(71000000,90000000)) from dual;
ABS(DBMS_RANDOM.VALUE(71000000,90000000))
-----------------------------------------
87283730.7
View 6 Replies
View Related
Feb 9, 2011
I need to export large number of records from select into the text file. It's about 2milion records.I can do it by PLSQL (see below) where executing of process takes time too much. How to export to text file faster?
DECLARE
fileid UTL_FILE.file_type;
BEGIN
fileid := UTL_FILE.fopen ('VRS_CEM', 'cust.txt', 'W');
UTL_FILE.put ( fileid, 'IMSI|MSISDN|CONTRACT');
UTL_FILE.NEW_LINE(fileid);
[code]....
View 8 Replies
View Related
Feb 5, 2013
how to manage large number of record in ArrayList.i.e abc table has 20 million record and jvm size is 2 GB.
View 1 Replies
View Related
May 7, 2010
We are facing one issue on one of the database. The database is generating large trace files(14000) from last two days. That consumes around 15G space on the disk. And the content of the trace files is not having any meaningful message to debug:
cat /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc
*** TRACE DUMP CONTINUES IN FILE /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc ***
Dump file /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc
*** TRACE DUMP CONTINUED FROM FILE /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc ***
... (Many lines with above message)
The alert log is having one repeated error yesterday:
Thu May 6 22:00:03 2010
Errors in file /apps/oracle/admin/fs90uat/bdump/fs90uat_j000_11811.trc:
ORA-12012: error on auto execute of job 2647927
ORA-04063: ORA-04063: package body "ORACLE_OCM.MGMT_DB_LL_METRICS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "ORACLE_OCM.MGMT_DB_LL_METRICS"
ORA-06512: at line 1
The corresponding trace file is having error:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /apps/oracle/product/10.2.0/db_1
System name: SunOS
Node name: corpqadb30
[Code] .......
View 2 Replies
View Related
Nov 19, 2012
create or replace
Procedure ReadingsPurge
As
v_sql varchar2(500);
v_date date;
p_count NUMBER;
[Code]...
-- Code below drops partitions that are older than the NoOfDays Parameter
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;
[Code]....
Above code is compiling successfully.
After I added the lines makred in the red font, when I tried to execute the stored procedure, I got an error
Error starting at line 1 in command:
execute ReadingsPurge
Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at "CDC_USER.READINGSPURGE", line 30
ORA-06512: at line 1
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
View 2 Replies
View Related
Apr 29, 2013
Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.
Method I have used:
1. Created Temp table
2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.
delete from B where exists (select 1 from temp where b.col1=temp.col1);
3. why it is taking too much time for deleting records in table A.
View 5 Replies
View Related
May 24, 2010
I'm testing a procedure which loads data into my database, and after each test I want to empty some of the tables and reset the sequences. I have this script to do that...
DELETE FROM COM_MERGE;
COMMIT;
DELETE FROM COM_TITLE;
COMMIT;
DELETE FROM COM_ISSUE;
COMMIT;
DELETE FROM COM_PAGE_ELEMENT;
COMMIT;
DELETE FROM COM_ELEMENT;
COMMIT;
DELETE FROM COM_STORY_TITLE;
COMMIT;
BEGIN
COM_RESET_SEQUENCES;
END;
Today I added the call to the sequences procedure to my script, but I have been using the script to delete from tables for a number of days without problem.However today I am finding that when I run the script it works ok the first couple of times, but when I try running it for a third time, it hangs after the second delete (in other words it stops when it gets to the delete from COM_ISSUE). After this happened the first couple of times I stopped the db and restarted it, then the script was ok twice, but again I'm finding that the script hangs. There is no error message, but the script fails to complete.
I didn't know if it was because originally I had one commit at the end of the script, so I added commits after each delete but that didn't solve it.I am using SQL Developer but I have found the same problem when running the script from SQL Plus.This is the definition of the COM_ISSUE table (just in case the table is the source of the problem).There is only one row in COM_ISSUE.
CREATE TABLE "BILL"."COM_ISSUE"
(
"CI_ID" NUMBER NOT NULL ENABLE,
"CI_TITLE" NUMBER NOT NULL ENABLE,
"CI_DATE" NUMBER NOT NULL ENABLE,
"CI_PRICE" NUMBER NOT NULL ENABLE,
"CI_PUBLISHER" NUMBER NOT NULL ENABLE,
CONSTRAINT "COM_ISSUE_PK" PRIMARY KEY ("CI_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536
[code]....
View 9 Replies
View Related
Sep 29, 2011
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
Expected result after deletion
GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26
View 5 Replies
View Related
Nov 2, 2012
I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
View 10 Replies
View Related
Nov 2, 2012
I have scenario here.
I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
View 6 Replies
View Related
Aug 12, 2013
I want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?
View 2 Replies
View Related
Sep 20, 2012
I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:
1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?
2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.
I'm trying to get the first block of the IF working first, so the rest is just placeholders.
DECLARE
v_mm number (2);
v_yyyy number (4);
min_mm number (2);
min_yyyy number (4);
max_mm number (2);
max_yyyy number (4);
min_date date;
[code]....
View 12 Replies
View Related
Jan 25, 2013
i have three tables and all of these tables have around 30L records.
Using join i am retrieving records from these tables but it is taking much more time to get output.
Partition can improve performance?
View 7 Replies
View Related
May 13, 2011
How to estimate next extent size for very large table? What should I take into account? Is there any formula for that?
View 4 Replies
View Related
Sep 2, 2011
I have two large tables(rptbody and rpthead) which has over millions or even more records. Below is the table schema
describe rpthead
Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER
[code]...
What I want is getting all data if the referenced RPTNO belongs to a particular product_id from rptbody table, here's the sql
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0
WHERE
(
t0.RPTNO IN
(
SELECT t1.RPTNO FROM RPTHEAD t1 where t1.PRODUCT_ID IN ('4647')
)
)
ORDER BY t0.LINENO
Since the result set is pretty large, so my application(think it as c couple of jobs, each job should be finished in a time window) can only process a subset of all data, so I need pagination so that the next job can continue the processing until all data is processed, below is the SQL with pagination
select * from (
select a.*, ROWNUM rnum from
(
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0
WHERE
(
[code]....
As you can see each query will take 100 rows from the db. The problem for now is that the query taking too much of time(10+ mins), I know the slowness is due to "ORDER BY t0.LINENO", but it's required for pagination.
View 4 Replies
View Related
Sep 30, 2012
I am trying to create a new index on large table of size around 100GB. but i am getting the following error:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP.
temp tablespace size is : 20 GB.
does it mean that the whole index will be created at temp tablspace first?
View 3 Replies
View Related
Aug 26, 2011
I am working with an online application with the database in Oracle 10G. We have a table with 10 million rows and this table is subjected to grow in future also. Moreover we cannot archive some of these rows as these records are required for referencing.
We have all necessary indexes on the table but querying this table takes a lot of time especially when it is joined with other tables. some methods with which I can manage this table in a better way so that queries joining this table would execute faster..
SELECT
TAB1.C6,
TAB1.C8,
TAB1.C10,
TAB3.C4,
[code]....
View 7 Replies
View Related
