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?
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) ;
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.
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.
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
(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
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.
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.
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.
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?
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 .
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)
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
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.
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;
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?
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.
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)?
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?
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]....
