Table Data Storage And Retrieval?
Apr 10, 2008
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 to empid2 from emp table.
How do we optimise the data retrieval and storage for the table Team. optimize the query and data retrieval time from Team table.
View 4 Replies
ADVERTISEMENT
Jan 17, 2012
i hv one table breco_data which is having two columns one is partycode and second is chequee no .and i just want filter the partycode and cheque no which are not availble in the breco_data table .and party cannot have same cheque no
View 2 Replies
View Related
Jan 16, 2013
what could be the cause of the following scenario :
A form is running, data is entered and saved. After saving on form, checking in the backend, the data is present. But when using the same form again to retrieve data, no data is shown.. and also if tried to enter same data (previously entered for saving again) then error comes up that data is already in the database.
could this be related to any restrictions that might be placed on tables or something ??
View 5 Replies
View Related
Nov 12, 2010
I create a view on production server which takes almost 10 to 12 minutes when it shows data. this view contains 3 or 4 tables on which all primary and unique columns have indexes.which index will be better for fast retrieval of data .
View 5 Replies
View Related
Oct 24, 2013
How To Increase Data Retrieval / Insertion Speed my data base has more than 0.5 million records Forms Some Time Respond Very Slow .
View 8 Replies
View Related
Jul 13, 2010
Is there any way to find out the division between the time taken for query parsing, creating execution plan and actual data retrieval seperately? If I enable 'set timing on' I see the elapsed time which is the total time taken for all these 3. Some of my queries are taking long time when I run it first time and so want to know what is it taking long? is it the parsing or creating the execution plan, if so what can I optimize.
View 3 Replies
View Related
Mar 29, 2011
I am working in a bank as an system consultant, i have a SAN Storage Area and oracle as below.
SAN 1
This interface includes the DATA FILES of the oracle tablespace
SAN 2
SAN1 Mirrors the DATA FILES of the oracle tablespace to SAN 2
1. Can i rely on real time data recovery from SAN2 ?
2. if SAN1 (Data Files are currupted) will the SAN2 Data Files will be currupted as well.
3. If the SAN2 is currupted then what Oracle Features can be used to have uncurrupted data.
View 5 Replies
View Related
Mar 1, 2013
My organisation is currently discussing different storage options for the database storage. Our production database is nearly 2TB and we do not want to continue with the existing NetApp storage (we use a 2 node RAC running 11.2.02 with nfs filesystem from NetApp filer).
We were looking at different options and came across Nimble Storage, they are very fast growing company aiming mid-range storage customers. The initial talks and demonstration looked very promising in terms of IO performance (they claim 40,000 - 60,000 IOPs for their CS400 series Nimble Storage array) and other options they are providing but we understand that majority of their customers are using it for VDI and other infrastructures.
They have demonstrated us using if for Oracle database with ASM storage over iSCSI LUNs. We are yet to do the POCs and benchmarking.
Has anyone come across Nimble Storage for running Oracle databases?
View 3 Replies
View Related
Apr 12, 2011
We are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?
View 2 Replies
View Related
May 15, 2012
I have an 11gR2 data base with Local Extent Management and Manual Segment Management. Can Data Guard be used to replicate this instance into ASM storage? [I have multiple long fields (BLOBs and CLOBs) in various tables.]
View 3 Replies
View Related
Apr 3, 2012
is there possible to retrieve date with its time in oracle ,if we didnot provide time while insertion? like hiredate column of emp table can we get hiredate of each emp with its time of joining. i am using "11g Enterprise Edition Release 11.2.0.1.0"
View 3 Replies
View Related
Oct 20, 2010
Any links on hierarchical retrieval concepts?
View 3 Replies
View Related
Oct 8, 2012
I have Oracle 10.2.0.4.0 on RAC environment. I had 19 Data files on ASM. However one of my colleague has created new data file on a Node using Physical Drive which like this.
G:ORACLEPRODUCT10.2.0DB_1DATABASEDATA_001.DBF instead of +DATA/ref/datafile/data.400.70812332
Now this file is in Recover State and when i try to make it online it says Block # 12 of this file is corrupted.
select * from v$datafile
20 20 6463317847 06/10/2012 10:11:18 6 20 RECOVER READ WRITE 6467876664 07/10/2012 08:39:09 0 6468047778 07/10/2012 09:30:53 0 0 104857600 12800 104857600 8192 G:ORACLEPRODUCT10.2.0DB_1DATABASEIFSAPP_DATA_001.DBF 0 8192 NONE 0
View 2 Replies
View Related
Oct 29, 2012
I'm trying to develop a procedure which will read a BLOB field of a table and write an RTF document which will be used as template to another procedure but when the procedure fires up the select statemente, i got stuck with a ORA-06502 error.
This error, after reading the documentation, is caused by incompatibilities between fields (numeric or value error string) But i've seen this example everywhere in the internet and what is causing it.
The source code of my procedure follows:
PROCEDURE p_transfer_db_client(pcPath IN VARCHAR2,
pnSequence IN NUMBER) IS
v_src_blob BLOB;
v_file UTL_FILE.FILE_TYPE;
[code]...
View 1 Replies
View Related
Jun 19, 2012
I have 11.1.0.7 instance running and got more 300G of storage available for the database. I would like to know what are the steps to add these disks/storage to the existing DATA asm disk group?
View 5 Replies
View Related
Aug 9, 2012
I am getting error while installing Oracle 11g on windows xp. Ora --12638: Credential Retrieval failed error. I understand that some people have got that error after the installation( Changing SQL.Net file).
View 9 Replies
View Related
Dec 24, 2012
One of my databases which is running on exadata x2-2 , has been restored to non-exadata machine in order to test few things. I had a sub-partition table in the exadata , compressed for query high. test machine (NOT EXADATA) , after uncompressing this subpartiton tables i am getting the following err message :
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type I have executed the following commands :
alter table crm.cm_ncd modify partition P01_CM_NCD nocompress;
alter table crm.cm_ncd modify partition P02_CM_NCD nocompress;
alter table crm.cm_ncd modify partition P03_CM_NCD nocompress;
[code]...
ERROR at line 1:
ORA-12801: error signaled in parallel query server P005
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type If all the partitions are uncompressed why i am getting this error message ?
View 1 Replies
View Related
Jul 24, 2012
how to insert data in a table by deleting previous entered data and only inserting current data like:
CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);
[code]....
I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:
INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);
it must show aaa,8 and aaa,9 bt not the previous values.
NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.
View 4 Replies
View Related
May 7, 2012
when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below
go_block('show');
clear_block(NO_VALIDATE);
declare
cursor c1 is select *
from qtr_demand order by 1;
begin
[code]..........
View 26 Replies
View Related
Oct 4, 2011
we are moving datacenter from one place to another where existing datacenter has got ASM(RAC env) with HP storage which needs to be moved into target datacenter for EMC storage.basically How ASM can be disassociate from source HP storage then associate target EMC storage with very minimal down time.
what are all the ways and all step by steps required for the same.
View 1 Replies
View Related
Dec 23, 2012
How password value is stored in 9i/10g/11g internally and how to retrieve those password values ?
View 1 Replies
View Related
Oct 9, 2012
I am an Oracle dba new to xml db,we have binary storage with 4schemas ,Right now indexing on 2gb table takes like 20GB on Path table and secondary xml index.
I want to change the table storage without disturbing the application development just the xml type column into Object relational and merged all the 4xml schemas into master schema.
Right now i want to insert the data from old tables which had different URL . How I can improve performance without having 20Gb on secondary index objects.
View 1 Replies
View Related
Nov 3, 2011
I am working on forms 6i. I have one data block based on table EMP.
Datablock-A: Empno, Ename, Sal
I have FIND Screen: Empno, Ename, FIND Button.
I can search the data through FIND Screen and populate data in Datablock A.
I am using below logic to search data through FIND Screen.
When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.
In Pre-query trigger of DatablockA:
copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');
It's working fine. But below case is failing.
Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.
But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.
why it's failing to pull records if i try to query data in first case only.
View 2 Replies
View Related
May 26, 2013
I am trying to add a new column in a table and insert data from another column of same table.
alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit
As there are huge number of records in the POSITION table ...its taking for ever to execute this query.
View 1 Replies
View Related
Jun 21, 2010
I have written a java code which reads 2 millions of data under a particular column from CSV file and store it into a set. Now there is a table in Oracle database which contains 10 millions of records for that particular column. Now, I want to form a SQL query which select those records under that particular column from the database table which is in CSV file but not in database table. For e.g.
If I consider the CSV file name as employee.csv and it has column called employee_name under which the records are as follows
employee_name
---------------------
Sudip
Kaushik
Joyanta
Soumya
Ritesh
Gautam
And the database table name called cmp_employee and it has column called employee_name under which the records are as follows
employee_name
--------------------
Sudip
Kaushik
Joyanta
Soumen
Souvik
Sahoo
Now, the "SQL Query" should return ---- "Soumya, Ritesh, Gautam" this three names.
View 2 Replies
View Related
May 30, 2012
I need to import some data from .csv files. There is one file each day, so I want them to be automatically imported into the DB. This is the format it comes in:
DSM,LOD,20120524,01,01,9999AMP02,1.1262240,M,0.6397380,M
DSM,LOD,20120524,01,02,9999AMP02,1.1315700,M,0.6450840,M
DSM,LOD,20120524,01,03,9999AMP02,1.1297880,M,0.6450840,M
I want this data to go into TEMP_TABLE. It then needs to reformatted as it goes from temp_table to my_table:
filename,readingID, field1,field2,date,num1,num2,meterid,read1,m1,read2,m2
so filename is the actual name of the .csv file that this row came from. And reading id is date, num1, num2, meterid combined. And the remaining fields coming from temp_table
This is what I have:
procedure import_data()
begin
TEMPFILENAME CHAR(60)='DSM_2010_Husky_Oil_20120525064122_20120525065011.csv';
create table temp_table
(dsm char(3),lod char(3),usage_date date,he char(2),reading char(2),loc_id char(9),mwh number(15,10),eormmwh char(1),mvar number(15,10),eormmvar char(1));
[code]....
, which does not work at all.
View 2 Replies
View Related
Jan 3, 2013
Here is my problem, i need to create some files with my own format(let say 5000 records each) from a huge data table (May contain 5 Million records). And i want this creation to be multi threaded.
so how can i form queries efficiently to fetch records like 1..5000 and 5001..10000 and so on. I can form some thing like select * from table where rownum<5000 and not exists ( already fetched records) . but it is not the efficient one.
View 5 Replies
View Related
Aug 29, 2013
In one of the book (Personal Oracle Real Application Clusters: Create Oracle 10g Grid Computing) I have seen the detailed explanation to configure Oracle 10g RAC on external disk- MAXTOR A01A200.
Is it possible to configure Oracle 11g Release 2 RAC with two node using MAXTOR disk?
or Is there any other external disk available which I can use it as a shared storage?
View 2 Replies
View Related
Feb 4, 2011
I need a 2-3 TB storage server group for Oracle Database. We want SAN Storage & Server for Oracle Replication, but not Microsoft Storage Server.
For example : HP DL380 G6 as a server and EMC CLariion CX4 120 as a SAN Storage etc...
We have an option, but want some others. What do you recommend for a Server & for SAN Storage (Make & Model) to use for Oracle Replication?
View 2 Replies
View Related
Aug 9, 2013
I wanted to setup a two node 11g RAC with ASM at my home for learning purpose. I have read many documents to configure shared storage using NFS, Openfiler, VMsetup etc. Is there any hardware available (something like maxtor disk) which is not much expensive to setup a shared storage ?
View 3 Replies
View Related
