How To Export Latest N Records From A Table
Jul 2, 2012How to export latest n records from a table using the below query(latest n records)
select * from (select empid,ename,sal,joining_date from emp order by joining_date desc) where rownum<n;
ADVERTISEMENT
Get Latest Records?
Jun 9, 2011I have a table that contains history for vehicle positions. In order to find the latest positions quickly, I've included a LATEST column that is 1 if the record is the latest position and 0 otherwise. The table is maintained via a stored procedure. The procedure first sets the latest record for the vehicle to history...
UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = <vehicle ID> AND latest = 1
It then inserts the new latest record...
INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (<vehicle_id>, <x pos>, <y pos>, SYSDATE, 1)
Is it possible for me to end up with 2 latest records?Consider this scenario...
Session #1: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #2: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #1: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8533, -117.1180, SYSDATE, 1)
Session #2: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8534, -117.1178, SYSDATE, 1)
I'd end up with 2 latest records. How can I protect against this? I considered using SELECT FOR UPDATE, but seems like there are too many negatives going that route
PL/SQL :: Latest Salary (last 2 Updated Records)
Jun 18, 2012I wanted to know the latest salary(last 2 updated records ) getting by emp- id 252 based on below mentioned information
source desti sal emp id MGR
1-Jan 1-Feb 1000 252 venkat
2-Jan 2-Feb 4000 252 venkat
2-Feb 2-Feb 5000 252 venkat
SQL & PL/SQL :: Insert All Records From External Table Into Export Table
Mar 25, 2013 following is the requirement
External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B
EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
[code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
Export 20 Small Records From One Table In 9i Into XML File
Aug 26, 2008I need to export 20 small records from one table in Oracle 9i into an xml file. Each record has 4 fields. what the command would be in sql plus.
View 1 Replies View RelatedSQL & PL/SQL :: To Retrieve Only Latest Repair Information Based On Latest Date
Apr 3, 2012I would like to retrieve only the latest repair information based on the latest date regardless of the other information, so I would like to query only items 3 and 5 in the following example.
drop table a;
create table a(
seq number,
custom_id number,
repair_id number,
repair_date date);
[code]........
Retrieving Max / Latest Date From A Table With A Join To One Another Table
Sep 19, 2011I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.
Here is what I have:
selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
from cs join wr on cs.applicant_id=wr.applicant_id
where wr.status_cd='AC'
group by cs.applicant_id
Export/Import/SQL Loader :: Imp Records Of One Schema Into Another Schema Of Same Table
Nov 3, 2012I had done following steps,
schemas(toy,toys)
1) i open the session of toy schema
First i taken backup of table
create table bck20121103_himan as select * from himan;
Backup table is created.
After taking the Backup table
delete himan;(deleting the records)
2) i log in to another session(toys)
exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/
3) i log in to toy schema
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y
i tried the above statement it taken so much of time..
Later i tried
I log in to toy session
i rename the table with other name.
later i imported
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y
it's successfully imported.
SQL & PL/SQL :: Updating Table With Latest Record
Mar 17, 2011DROP TABLE TESTING CASCADE CONSTRAINTS;
CREATE TABLE TESTING
(
DATAPERIOD DATE,
EMPLID VARCHAR2(20 BYTE),
B_OS_REASON VARCHAR2(9 BYTE)
)
TABLESPACE USERS;
[Code] ........
SQL> select * from testing order by 1;
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972
In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972 XXXXXX
SQL & PL/SQL :: How To Select Latest Updated Record From Table
Sep 21, 2011I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0
View 8 Replies View RelatedSQL & PL/SQL :: Retrieving Latest Create_date And Pay_total For Each Act_id From Table?
Apr 19, 2010I am trying to retrieving the latest create_date and pay_total for each act_id from the table. Table is having more than 5000000 records. I prepared the below query but it didn't the data even after 6 hours.
select a.act_id,a.create_DATE,a.Pay_TOTAL
from payment_tab a
where 1 = (select count(distinct b.create_DATE)
from payment_tab b
where b.act_id=a.act_id
and
b.create_DATE >= a.create_DATE)
i cannot create the indexes on this table.
Export/Import/SQL Loader :: Expdp Command To Export Table By Specifying Query Parameter
Aug 16, 2013I am using expdp command to export the table by specifying Query parameter. But i am unable to export the table based on the condition.
Ex:EXPDP username/password dumpfile=employee.dmp logfile=emp.log directory=DATADIR_EXP TABLES=EMPLOYEE query=EMPLOYEE:"UPDATED_TIME >= '04-JUN-13' AND UPDATED_TIME >= '05-AUG-13'" Estimate in progress using BLOCKS method...Processing object type TABLE_ EXPORT/ TABLE/ TABLE_ DATATotal estimation using BLOCKS method: 3 GBORA-31693: Table data object "<username>"."EMPLOYEE" failed to load/unload and is being skipped due to error:ORA-00933: SQL command not properly endedMaster table "<username>"."EMPLOYEE" successfully loaded/unloaded...Dump file set for <username>.SYS_EXPORT_TABLE_01 is: E:IMPDPemployee.dmpJob "<username>"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 12:34:45 Oracle 11g,
Export/Import/SQL Loader :: How To Export Full Dump And Metadata Of Particular Table
Apr 9, 2013let us consider mytest schema is having 6 tables
tname tabtype
myt table
myaxpertlog table
abb table
ccc table
ddd table
xxx table
now from this schema i want full dump and also from myaxpertlog table i required metadata only not records.
c:> export mytest/log file=20130409mytest0904pm.dmp tables=(myaxpertlog) rows=n
if i tried i am get only one table but it does have records.
Export/Import/SQL Loader :: Export Table From Client Machine
Jan 8, 2013I want to export a table ( using exp or expdp ) from client machine. Dump file should be created to client machine.
Is this possible ? How to do this ?
Export/Import/SQL Loader :: Export Table To A Text File
Mar 17, 2013We need to export a big table into a text file with Column delimiter '&|' and row delimiter '$#'.
DB Version is 10.2.0.2, the table size is around 4 GB.
Export/Import/SQL Loader :: Export Partition Table
Aug 31, 2012I want to export table. That table was partitioned 322.if i query that table ,it's showing 322 lines .
object type :table partition.
expdp command for export the table.
Export/Import/SQL Loader :: Use Export And Then Rename Table?
Jan 12, 2013oracle 11g.2 ASM with RAC under RHEL 5
we have 2 table same structure one of them is empty and the pther one is contain data the vendor do the insert as select but i found he is wrong due to there duplicated ,now i want to use export and then rename the table and then import but i need with export do a condition
exp user/pass tables=MTR_EPPC_CALLED_DATA file=MTR_EPPC_CALLED_DATA.dmp query="where callstarttime >=to_date('01122012','ddmmyyyy')
and callstarttime <=to_date('31122012','ddmmyyyy')"
but it's seem the query take one condition how can i use this above condition in export ???also my friend say there is way to insert with rowid is this possible ??
SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source
Jun 1, 2010I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
SQL & PL/SQL :: Export Large Number Of Records From Select Into Text File
Feb 9, 2011I 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]....
Server Utilities :: Export / Import XML Records In Oracle 11gR2
Jan 28, 2012In oracle 11g R2 , I face a problem when I export/import xml records in the tables . Everytime it takes huge time (like 2 or 3 days) to import or export the data . But the dump size is very small (4gb) and this dump comes from a vendor so that I dont understand that is it a data structure problem or it the normal behaviour to import xml records.
I am not used to with xml record with oracle database before. Here I am using datapump feature . I also mention that when I delete the schema (where I imported the xml data) , it also takes 2/3 days to delete.The import script will hang in the following stage :
=========================
bash-3.2$ impdp system/sys123 DIRECTORY=isb_dir DUMPFILE=jblbld_20110301_01.dmp,jblbld_20110301_02.dmp,jblbld_20110301_03.dmp logfile=JBLBLD_28Jan2012.log schemas=TBLD;date
Import: Release 11.2.0.1.0 - Production on Sun Jan 29 11:10:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/******** DIRECTORY=isb_dir DUMPFILE=jblbld_20110301_01.dmp,jblbld_20110301_02.dmp,jblbld_20110301_03.dmp logfile=JBLBLD_28Jan2012.log schemas=TBLD
Processing object type SCHEMA_EXPORT/USER
[code]...
The machine has 16 cpu , 32 gb RAM and during export/import most of the time maximum memory will free.
Export/Import/SQL Loader :: Import Scott To Scott1 Without Affecting Existing Records
Sep 29, 2012My database version
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionOS version:
Windows 7 64bit I have schema(scott) export with schema level option and imported with different name as (scott1).At regular period of time i need to import the scott to scott1 without affecting existing records.such as
*1. Need to append new created records.*
*2. Need to append updated records.*
for the above requirement I did in the following way
expdp xxxx/******** schemas=SCOTT directory=dumpdir dumpfile=SCOTT_28-SEP-2012.dmp logfile=exp_SCOTT_28-SEP-2012.log imported in the following way impdp xxxx/******** AS SYSDBA REMAP_SCHEMA=SCOTT:SCOTT1 directory=DUMPDIR dumpfile=SCOTT_28-SEP-2012.dmp logfile=imp_SCOTT2_28-09-2012.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=APPEND.
The problem is i couldn'table to append the records to existing tables the log error show such ways.
ORA-31684: Object type USER:"SCOTT1" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
[code].....
Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?
Feb 7, 2012We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
PL/SQL :: Selecting Records From 125 Million Record Table To Insert Into Smaller Table?
Jul 17, 2013Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
[code]....
SQL & PL/SQL :: Having Latest Date
Aug 9, 2013I have a query where i need to pull back the latest dated record for a tariff
my data looks like
Col_A Col_B Col_C
1| 100 29-Sep-11 Tariff_1
2| 200 24-Apr-12 Tariff_2
3| 300 17-Oct-12 Tariff_3
and i need to add a subquery which pull back the col_c with the larges Col_B i.e i need to pull back tariff_3 only
i tried using a max but dont think that will work.
...
and c.Col_c in ( select col_c where max(col_b))
SQL & PL/SQL :: How To Find The Latest Date
Jul 31, 2010SQL> select * from emp;
SINO BOOK UPDATION_
---------- ---------- ---------
1 UB 01-MAR-10
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 12-JUN-10
4 MB 31-JUL-10
6 rows selected.
SQL> SELECT sino, book, updation_date
2 FROM emp
3 WHERE updation_date IN (SELECT MAX (updation_date)
4 FROM emp
5 GROUP BY book);
SINO BOOK UPDATION_
---------- ---------- ---------
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 31-JUL-10
I would like to know, how to find out the latest date from above query without using group functions like max, min,order by and group by.
SQL & PL/SQL :: Getting Latest Record From Different Tables?
Feb 4, 2011I tried to post this issue earlier but it was not very clear. Well. Let me try to put in more better way.I have four tables storing order & customer information.
For given order number and cust _id I need to display latest record. here are my four tables.
SQL> select * from so;
ORD_NO ORD_DATE CUST_ID
---------- --------- ----------
1 01-JAN-10 10
2 02-JAN-09 20
3 03-FEB-11 30
SQL> select * from sol;
FK_ORD_NO FK_CUST_ID CUST_NAME LOCATION
---------- ---------- ------------------------- ----------
1 10 abc MA
2 20 xyz CA
3 30 ijk LA
[code]...
There will be a stored procedure that will take order_no and cust_id as input paremeters and return out ref cursor.
create procedure P1( in_ord_no in number,
in_cust_id in varchar2,
out_cur out sys_refcursor);
Here is the expected output.
Case 1 - in_ord_no =4 , in_cust_id = 10
Expected output - Empty cursor. Because bothe tables does not have matching record.
Case 2- in_ord_no = 3 in_cust_id = 30
Expected output =
[code]...
SQL & PL/SQL :: How To Find Last Or Latest Inserted Row
Apr 22, 2013i have a table in that every month i insert rows and my table doesn't have primary key,index and date filed.for example:-table is like this
name salary Id
john5000101
brat4500102
smith4600103
john5500101
brat4600102
smith4800103
i think one cannot tell in above table whether "john" salary is 5000 or 5500(last insert row is 5000 then john salary is 5000)
when it comes to retrieve i have to pick the latest(last) insert row of particular Name.Is there any auto generated row_numbers in Oracle.
SQL & PL/SQL :: Selecting Latest Date
Aug 23, 2012have a bit of a SQL trouble. I have a simple table (pcuk_BG_alloc_TAB) which stores Parts, Quantities and Applied dates
PART_NO QUANTITY APPLIED
PartA 100 10/8/2012
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 50 10/8/2012
PartC 75 15/8/2012
PartC 80 21/8/2012
I am only interested the latest applied date for each part and am looking for this to be returned in a select statement (as below)
PART_NO QUANTITY APPLIED
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 80 21/8/2012
I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.
Latest Critical Patch Update?
Oct 14, 2010identify the latest Critical Patch Update for 10g RAC. My DB server is 10g RAC 2 node cluster running on IBM Aix Pseries servers. The version is 10.2.0.3.
View 1 Replies View RelatedDownload Latest Critical Patches?
May 3, 2011I want download latest critical patches of 11.1.0.7.0, 11.1.0.6.0. how can i download
View 1 Replies View Related