which is the fast way of inserting 60 millions of records from a view to a table.
method 1:
create table t_temp_table as select * from v_dump_data;
method 2:
through bulk collect
---Bulk_Collect With FORALL---------- DECLARE TYPE srvc_tab IS TABLE OF t_temp_table%ROWTYPE; l_srvc_tab srvc_tab := srvc_tab(); l_start_time NUMBER; l_end_time NUMBER; l_error_count NUMBER;
I have a table which has plenty of rows. In production, I would estimate it to be from 30 millions to 300 millions. I need to update on column (flag) in all the rows (created before certain date).Now saying just:
UPDATE MyTable SET flag = 3 WHERE created < to_date('2010-10-08 23:59:59', 'YY-MM-DD HH24:MI:SS'); COMMIT;
Does not seem like a good idea - the commit-buffer would become too big.I will write a PL/SQL script for this. The question is, whether I should:
a) Update each row separately, and commit after every 10000 rows. ( WHERE RowId = [rowId] ) b) Update 10000 rows with set of dates ( WHERE rowId > [some_row_id] AND RowId < [some_row_id_2]
In the latter example the some_row_ids would naturally be fetched. The rowIds come from sequence. So which one would be more effective?I am not too familiar with PL/SQL or Oracle for that matter.
Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.
I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.
CREATE OR REPLACE PROCEDURE pro_test v_mas_seq NUMBER (9); v_gov_total NUMBER (20, 2); v_emp_total NUMBER (20, 2); v_text_exp CLOB; v_pageaccess VARCHAR2 (15); v_dto NUMBER (7) := 4011486; v_batchno NUMBER (20) := [code]....
I need to load 2 trillion data from an external table to Oracle Heap table. I am using Direct Path insert for that. how to commit after inserting n number of rows.
Below are the data for rows that I want to insert into CUSTOMER_PRODUCT table from a stored procedure.
Instead of making round trips twice to insert these two rows, I looking for a way to pass in the data for both those rows and then insert them from within the stored procedure in one shot.
The stored procedure will be invoked by Java and .NET.
Sample Data for CUSTOMER_PRODUCT:
ROW 1: CUSTOMER_ID : 1000 PRODUCT_TYPE : PROD123 IS_MEMERSHIP : Y IS_EMAIL_SUBSCRIPTION: Y
ROW 2: CUSTOMER_ID : 1001 PRODUCT_TYPE : PROD123 IS_MEMERSHIP : Y IS_EMAIL_SUBSCRIPTION: Y
Question 1: Should collection be used? (or) is there any other approach that could be utilized?
Question 2: Are there any performance concerns in passing collection and iterating it to fetch value to insert into CUSTOMER_PRODUCT table?
The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.
I am trying to delete the one month data from the table , which contains the end customer sales data.The total data count in the table is 30 crores. And the data of one month is nearly 10 Crores.I am using oracle 10g , The date field to be used in the condition of delete is indexed.
i have a problem to view tens of millions of data in oracle 11g i have table Book_Issue that have tens of millions of data but when i execute query to see all the data select * from Book_Issue
it only view 5.000 data
what query should i execute to view million data in oracle 11g
There is a table in Database with millions of records and a query --- Select rowid, ANI, DNIS, message from tbl_sms_talkies where rownum<=:"SYS_B_0" ---- using the high CPU and also this query having high number of executions.
Perhaps this is a common request : I have 2 tables:
Table A ------- ID Value 1 a 2 b 3 c
Table B ------- ID AnotherValue 1 x 2 y
I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:
Table A
ID Value AnotherValue 1 a x 2 b y 3 c (null)
)
I would rather stay away from the standard update statement since it takes far to long and I'd prefer not to use create table as I don't want to duplicate any data...is this possible to do ? (e.g: just insert the columns into this table ?) - or if it's possible the performance overhead just wouldn't make it worth it ?
Declare Cursor c1...; Cursor c2...; begin open c1 ; fecth c1 bulk collect into v1; close c1;
[Code]...
Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?
declare type lst_deptno is table of dept.deptno%type index by binary_integer; type lst_deptno_emp is table of emp.deptno%type index by binary_integer; v_deptno lst_deptno; v_deptno_emp lst_deptno_emp; cursor c1 is select deptno from dept;
if the same name repeating it should to append with _1 and _2 until same name reached.
select 'fname' name from dual union all select 'lname' name from dual union all select 'email' name from dual union all select 'fname' name from dual union all select 'fname' name from dualmy output should be like below...
I am just looking to my control file that I have. I have managed to successfully get my control file to load data into the required tables but just for my learning I was trying to amend the when clause with an and condition but keep getting an error.
My code: OPTIONS (DIRECT=TRUE, ERRORS=0, SKIP=1) load data infile 'A:/My Files/Feeds/20130211/data_summary_1_20131214.txt' "STR ' '"
APPEND into table I_DATA_1 WHEN (RID <> 'RID') fields terminated by "~|~" optionally enclosed by '"' TRAILING NULLCOLS ( RID, S_TIMESTAMP timestamp 'DD-MON-RR HH:MI:SS.FF3 PM' )
the above code works perfectly but if i change the following line:
WHEN (RID <> 'RID' AND 'S_TIMESTAMP' IS NOT NULL)than this gives me an error.
I have a requirement when I need to append and show the contents of a CLOB (rich text) column into the Open office report.
The use case is as below. User(s) can enter the details into a CLOB column. This data entered by each user need to be concatenated onto another CLOB column which will hold all the history of changes. Mainly i wanted to perform a concatenation of these two columns.
| | User Entered | Stored | | Data Stored in Clob Column 1| Data Stored in Clob Column 2 | | v_clob_1 | v_clob_target | ------------------------------------------------------------------------------- |"Text 1" and a image |"Text 1" and a image | -------------------------------------------------------------------------------
Any method to achieve this case. I had tried the following method to achieve this. But no success.
Version: 11.2.0.3 Platform : RHEL 5.8 (But I am looking for platform independant solution)
I want to append the timestamp to spooled log file name in SQL*Plus.The spooled log filename should look like
WMS_APP_23-March-2013.logI tried the following 3 methods found in the google. But none of them worked !
I tried this
col sysdt noprint new_value sysdt_var SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL; spool run_filename_&sysdt_var.Logas suggested in
[URL]
and this
spool filename with timestamp col sysdt noprint new_value sysdt SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL; spool run_filename_&sysdt..Logas suggested in
[URL]
and this
column tm new_value file_time noprint select to_char(sysdate, 'YYYYMMDD') tm from dual ; prompt &file_time spool logfile_id&file_time..logas suggested in
Creating a spool file with date/time appended to file name
I've got SQL code generated by an HP tool to make an update of itself. This code is not modifiable because was execute 'in background' from the tool updater.
The macro operations made by the updater (through sql code) are:
--create a copy of actual table and rename with '_old' suffix --create new table, with indexes and constraints --insert data into new table from old tables
Now, only in one table, when the data were inserted, an ORA-00001: unique constraint (ASSET_SVIL.CFG_CFGSECTIONCFGE) violated, appear.
To insert data is used an insert/select statement with an HINT /* APPEND */. We verify all the data existing in the old table (that is the data to 'migrate') but there aren't duplicate record!
BUT IF WE REMOVE THE HINT /* APPEND */, THE INSERT/SELECT STATEMENT WORKS WITHOUT GENERATE ERRORS!!
Im having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type