SQL & PL/SQL :: Data Load And Purge
Apr 12, 2012
Data purging and loading into different table.
Currently we a have a table with size 300gb, and we wanted to remove this table to release storage. but have to keep the 6 month data which would be around 3 million per day ( 3 million X 180 days).
i have the following questions.
1) what would be the best strategy to move this 6 month data from Table A (will be removed) to table B ( will be online table).
2) later we want to delete the data from table B which is 6 month older every day or week ( frequency is still to be decided)
note that We cannot use the partition as don't have a access to create and delete partition. what would be the best strategy for deleting the data?
View 18 Replies
ADVERTISEMENT
Nov 8, 2012
declare
min_as_of_dt date;
max_as_of_dt date := to_date('30-SEP-2012', 'DD-MON-YYYY');
begin
DBMS_OUTPUT.ENABLE(10000000);
[code]......
Problem: rows are not getting deleted after running this script.
View 1 Replies
View Related
Sep 24, 2012
I want to purge scott's recycle bin as user "SYSTEM". Because I do not know the user's password. How do I achieve it?
SQL> purge scott.recycle bin;
purge scott.recycle bin
*
ERROR at line 1:
ORA-38302: invalid PURGE option
View 21 Replies
View Related
May 30, 2011
We are facing space crunch in our 4 development 11i ERP environment servers and decided to reclaim the space . We are having 11.1.0.7 Database . What we observed is, there are many objects present in the recyclebin and we went on to purge the reclybin of MSC user.
SQL>select owner,sum(space)* 8 / 1024/1024 "Size in GB" from dba_recyclebin group by owner order by sum(space) ;
OWNER Size in GB
--------------- ----------
MSC 127.672485
IRCUS 22.5664063
GL 2.98492432
APPS .38079834
XXDII_ESB .192993164
APPLSYS .001159668
TMPBKP .000427246
But even after purging the recycle bin of MSC User , we didn't find any changes in DBA_FREE_SPACE for those tablespaces in which recyclebin objects were present. We would have seen the freed space in dba_free_space.
View 3 Replies
View Related
Feb 20, 2013
I am trying to load data from Db A to Db B. My filter is where date@A >= to_date('20130101','YYYYMMDD') and date@A <= to_date('20130131','YYYYMMDD') .But when I check my target table in db B , it only has dates between Jan-01 and Jan-30. No Jan-31.
View 5 Replies
View Related
Jan 18, 2012
Below is the data which i have to load
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 18 17:47:01 2012
Copyright © 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
57140002205124| 23| ST04| 9418285932| 17-JAN-12 11.17.31.820253 AM| Used
54171025176597| 49.86| TU03| 9411165512| 17-JAN-12 11.20.32.943855 AM| Used
54171025182725| 49.86| TU03| 9456310464| 17-JAN-12 11.37.14.346299 AM| Used
(1) first thing i want to remove the txt which is in the bold
(2) my query for creating the table is
CREATE TABLE VMSDATA
(
SERIALNO NUMBER(20),
AMOUNT NUMBER(7,2),
CLASS VARCHAR2(10),
MSISDN NUMBER(12),
VDATE TIMESTAMP(6),
STATUS VARCHAR2(8 BYTE)
and my control file for loading the data is
load data
infile 'path'
badfile 'path'
DISCARDFILE 'path'
truncate into table vmsdata
[code]...
View 1 Replies
View Related
May 24, 2012
I have a table in oracle and i want to load data to flat file which is present on the server. how to and where to provide the destination file location to load the flat file.
Can it be also provided in the plsql program.
View 6 Replies
View Related
May 20, 2011
I am very much new to this vast world of Pl/SQL. Recently I have moved to pl/sql domain. I want to write a package to move data from Table A to Table B by looking up the table C. Bellow is skeleton of my package.
Table name: source_a
columns: X1,X2,X3,X4,X5
Target table name: target_b
columns:Y1,Y2,Y3,Y4,Y5
Lookup Table : lookup_c
columns : Z1,Z2,Z3,Z4,Z5
1) I have to load data from source_a to target_b.
2) If value of column X1(source_a) matched with value of column Z1( lookup_c) then only we will process the recordsmeans those records will be the valid records. Rest of the records will be dropped.
3) From the valid records If X2 != Z2 then call a procedure pk_rec.generate_Y2(X2) to generate the value of Y2
{pk_rec.generate_Y2 already existing no need to create) else take the records form Z2 and use it to load the y2.
4) From the valid records If X3 != Z3 then call a procedure pk_rec.generate_Y3(X3) to generate the value of Y3
{pk_rec.generate_Y3 already existing no need to create) else take the records form Z3 and use it to load the y3.
5) X4,X5 are directly loaded into Y4,Y5.
View 3 Replies
View Related
Jun 25, 2008
I have a data file that looks like:
REC001;TO_NAME;TO_ADDR;TO_PHONE
REC002;ITEM_ID1;DATE_DELIVERED1
REC002;ITEM_ID2;DATE_DELIVERED2
REC002;ITEM_ID3;DATE_DELIVERED3
REC002;ITEM_ID4;DATE_DELIVERED4
i want to load this in the Database using SQL LOADER in this format:
NAME | ADDR | PHONE | ITEM | DATE DELIVERED
-------------------------------------------------
TO_NAME TO_ADDR TO_PHONE ITEM_ID1 DATE_DELIVERED1
TO_NAME TO_ADDR TO_PHONE ITEM_ID2 DATE_DELIVERED2
TO_NAME TO_ADDR TO_PHONE ITEM_ID3 DATE_DELIVERED3
TO_NAME TO_ADDR TO_PHONE ITEM_ID4 DATE_DELIVERED4
Basically i want the name, addr, phone from REC001 to be repeated every time i load REC002.
View 1 Replies
View Related
Jul 3, 2008
How can i convert my ms excel record into oracle records??
View 3 Replies
View Related
Jul 25, 2011
There is one table with data in ORCL1 database.I have created the table using create table statement in ORCL2 database.Now i want to insert only the data into table.
I know one method ... drop the table i created using drop table statement and then create the table with data using export/import.
Is there any other way we can load data into empty table?
View 29 Replies
View Related
Dec 28, 2010
how to load a XL sheet data into oracleDB.
View 7 Replies
View Related
Feb 27, 2012
How can i load the data into a new table from view,when ever scheduler runs in the night, the data gets loaded in to a view and data coming from different tables and i should load that data every day and i dont want previous data again.The data should be loaded along with view .
View 6 Replies
View Related
Oct 3, 2011
DB version: Oracle DB 10g Enterprise Edition 10.2.0.4.0
I have the following four tables:
tab_main- which lists main projects
tab_sub_main - which lists sub projects
tab_budget - amounts per projects/subprojects
tab_total - I want to load the data here
The table script with data is attached.
I want to load data into tab_total fields for prj_type= 'J' as follows:
1. accn_no from tab_main table.
2. fy from tab_budget table
3. fy_total_amt which is the sum(amt) from tab_budget table by accn_no and fy
4. all_FY_amt which is the sum(amt) from tab_budget table by accn_no
5. all the audit fields- date/user inserted/updated will come tab_budget table
how to create this procedure with cursors.
CREATE OR REPLACE PROCEDURE LOAD_DATA_INTO_TAB_TOTAL_PROC
IS
CURSOR C IS
select distinct m.accn_no, a.control_no,m.prj_type,
b.fy, b.amt, b.user_created, b.date_created, b.user_last_mod, b.date_last_mod
from tab_main m,
tab_sub_main a,
[code]....
CREATE TABLE tab_main
(
ACCN_NO NUMBER(7) NOT NULL,
PRJ_TYPE VARCHAR2(1 BYTE) NOT NULL
)
/
Insert into TAB_MAIN
(ACCN_NO, PRJ_TYPE)
[code]....
View 34 Replies
View Related
Aug 6, 2012
how can I load data into a table from *.ldr* file? How exactly I can use such files to run in loader?
View 16 Replies
View Related
Aug 24, 2011
I wanted to know the best utility in oracle to load data in crores from excel sheets in the database temporary tables in a minimum time.
Is sqlldr the best utility to use in this scenario or to use the parallel and append hint in the insert statment.
how much time the sqlldr and above mentioned hints take to load 10 crore data in the database table.
View 2 Replies
View Related
Sep 6, 2011
CASE 1:
when i tried to load the data i got the below error,
Error starting at line 2 in command:
INSERT INTO RECON_MATCHED_DETAILS (RECON_MATCHED_DETAIL_OID, RECON_ID, STATEMENT_DATE, EXECUTION_DATE, TRANSACTION_NUMBER, TRANSACTION_DATE, TRADE_ID, TRANSACTION_TYPE, LINK_ID, ITEM_TYPE, ASSET_CODE, ISIN, BUYSELL_INDICATOR, SETTLEMENT_DATE, CURRENCY, QUANTITY, VALUE,
[code]...
CASE 2:
i tried to load the data in oracle 11g but i'm unable to load the data,and for testing i tried with a single row of data.but surprisingly the table filled with (null)s
View 3 Replies
View Related
Mar 10, 2013
How to get ill-formatted data into Oracle table? I'm trying to load a large amount of data that is not arranged into neat columns and doesn't have proper record delimiters.
I'd like to use sql loader but I don't think that will work with unstructured data. I'm reading that perhaps using an external table would be the best way to do it. It's sample census data and I've attached a single record to look at.
View 4 Replies
View Related
Jun 18, 2013
I want to load data from oracle table to flat file(csv/text file). Is there SQL query to do this?
View 14 Replies
View Related
Oct 14, 2012
What is the best way to load the data from text file to the table in PL/SQL .How can i write a program for that ?
-data is separated by ',' in text file for each columns in table
View 19 Replies
View Related
Sep 27, 2010
We are using hand scanning machine for attendance, machine saved data in a TEXT file now I want to load data into my oracle base payroll system.
data saved in this format.
31201009240928000100000002690001
31201009240933000100000000060001
as per my understanding
20100924 is date
0928 is time
269 is employee code
but I am unable to understand, is this IN or OUT time?
View 3 Replies
View Related
Jan 21, 2013
I want to use SQLLDR (sql loader) in my forms9i form to load attendance data therefore I installed SQLLDR utility in user computer but it is not working,
Begin
:message := 'Please Wait, Data LOADING...!';
Delete From atnd_load;
Forms_ddl('Commit');
utlt:= 'c:oracleora90insqlldr';
usr:= 'vikorapp';
pass := 'vikorapp';
strng := 'laptop';
cntrl := 'C:VikorPayrollFormsAtndCtl.ctl';
lg := 'c: empAtndLog.log';
bad := 'c: empAtndBad.bad';
host(cmd);
:message:= 'Data Loading Complete...!';
End;
View 11 Replies
View Related
Dec 14, 2011
I want to load data from LST file. The data format and control file is given below. It is loading the 1st line only. it is not loading the other lines. pls let me know what needs to be added in the control file to load this data?
Table Scan: |14-DEC-11 09:54 |xest | 16| 0|SYSTEM |ws_email|declare v_lst_suc da|14-DEC-11 08:32:39| 716444|XEST_USER
XEST_USER.X| | | | | |er.exe |te; v_nxt_sch date; | | |
EST_PING_RCV| | | | | | |cur_time varchar2(30| | |
D: 28609 out| | | | | | |); begin --select| | |
of 28609 Bl| | | | | | | last_date, next_dat| | |
ocks done | | | | | | |e into v_lst_suc, v_| | |
[code]....
View 10 Replies
View Related
Aug 2, 2012
have loaded some data into table 'A' by using sqlloader.
Structure of A will be like
bill_id, bill_amount, bill_date
1 1000 2-1-12
2 2000 3-2-12
Now my query is i have to load some data into another table 'B', with bill_id as one of the column but i will be not having this column in my csv file.
Structure of B should be like
bill_no, bill_id, bill_desc
101 1 abcd
102 2 defg
my csv file have only 'bill_no' and 'bill_desc' data. How can i include bill_id values from A?I am using Oracle 10g.
View 3 Replies
View Related
Mar 7, 2013
I need a tutorial for using utl_file package to read and load in to oracle data.
Oracle is in Linux box.
View 32 Replies
View Related
Nov 3, 2006
I am trying to load data into various tables through a perl script using sql loader. Log files are created which say rows successfully loaded, but there is no data in the database. is there any way of explicitly saying commit with sql loader command (except for the rows options, Ihave tried using that also, with rows=1, but it doesn't work)?
View 1 Replies
View Related
Aug 13, 2011
how to load data from excel sheet to oracle 10g form from client.
View 1 Replies
View Related
Mar 16, 2010
I am new to oracle designer, forms. The requirement is to select a csv file in a form ,read the file and load selected columns from a csv file into a table.
I am using CLIENT_TEXT_IO. I want to know how to extract the data from selected columns from csv file and insert into a table if the lenth of the columns are of variable length.
Another condition is that if there are duplicate rows based on orderid then take the maximum order seq nbr.Do I need to use
temp table for this logic?
View 1 Replies
View Related
Jan 3, 2011
how to load oracle table data into EXCEL Sheet .
View 5 Replies
View Related
Feb 7, 2013
I'm trying to load data into a table using SQL Loader but getting a failure error below.
Log File
========
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Feb 6 23:54:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: /opt/Infor/Outbound_Marketing/7.2.2/EM/metadata/trans.ldr
Data File: /opt/Infor/Outbound_Marketing/7.2.2/EM/logs/trans.log
Bad File: trans.bad
Discard File: none specified
[code]....
View 13 Replies
View Related
