Feed aggregator

How to create tables automatically from multiple csv files in oracle database?

AskTom - Hot Questions - Mon, 05/08/2017 - 00:00
How to create tables automatically from multiple csv files in oracle database? For Example: I have a client requirement that load data from several csv files into an oracle database.I have 50 files with different structures.so ,i want create table ...

Show rows where the running total is zero and all preceding rows

AskTom - Hot Questions - Fri, 05/05/2017 - 00:00
Hi Team, Data Setup: <code>create table test_item as with t1 as ( select 1 category, 'abc' Item_name,100 item_volume,12.3 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual union select 1,'abc' ,-100 ,1...

how the data stored in partition table

AskTom - Hot Questions - Thu, 05/04/2017 - 00:00
Hi Tom, I have a partition table, Range partition by date, list sub-partition by region. my question is: Is it possible that same day but different regions' data can be stored at same data block. when running complicated query (the query join a...

Find agents with no transactions for 5 or more continuous days

AskTom - Hot Questions - Wed, 05/03/2017 - 00:00
Table1: TRANSACTION FIELDS : AGENT_ID, TRNX_AMT, INSERTED_ON <code>create table TRANSACTION (AGENT_ID varchar2(10), TRN_AMT number(4,2),INSERTED_ON date);</code> Table2: AGENT_DETAILS FIELDS: AGNET_ID,NAME,STATUS <code>create table AGENT_DE...

Combining multiple rows with a "priority"

AskTom - Hot Questions - Wed, 05/03/2017 - 00:00
I have a table: <code>create table mytab (ROLE_NAME VARCHAR2(30), MENU_TAB VARCHAR2(20), colA VARCHAR2(1), colB VARCHAR2(1), colC VARCHAR2(1));</code> I populate: <code>INSERT INTO MYTAB VALUES('TECH','MAIN','A','B', NULL); INSERT INTO MYTA...

Real-time materialized view not working as expected

AskTom - Hot Questions - Fri, 04/21/2017 - 00:00
Hello, I have a problem with <b>Real-time Materialized View `ON QUERY COMPUTATION?</b> functionality. My Real-time MV is enabled for both <b>QUERY REWRITE & ON QUERY COMPUTATION</b>. As I understand, when the MV is fresh, we get a MAT_VIEW R...

Query Writing Differently (Relational Division)

AskTom - Hot Questions - Thu, 04/20/2017 - 00:00
<code>create table developers (name varchar2(30), skill varchar2(30)); create table projects (name varchar2(30), skill varchar2(30)); insert into developers values ('SMITH','ORACLE'); insert into developers values ('SMITH','JAVA'); insert i...

Correctly identifying Dynamic Sampling queries run by Optimizer

AskTom - Hot Questions - Wed, 04/19/2017 - 00:00
It is clear that dynamic sampling queries run by the optimizer contains <b>/* DS_SVC */ </b>clause in them (when traced). e.g. SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel...

Changing Hostname, IP address, DNA Name for the database

AskTom - Hot Questions - Mon, 04/10/2017 - 00:00
We have an Oracle 11.2.0.4 database with ASM storage. The linux version is OEL 7.0. Recently, the clients are having changes to Host Name, IP Address, DNS Name. We have to update the same for the database. Below is the list we have made, where w...

latch: cache buffers chains

AskTom - Hot Questions - Tue, 04/04/2017 - 00:00
Dear Experts, I work in a telecom domain, We have AIA and SOA application which we are tunning for increasing the tps. We have observed below even when same session are hitting the database. I would request you to please provide me suggestion or s...

TNS Issues when connecting to database with Forms Builder 12.2.1.2.0

AskTom - Hot Questions - Sun, 03/19/2017 - 00:00
I am getting the following issue when trying to connect to the database with Oracle Forms Builder (12.2.1.2.0): ORA-12154: TNS: Could not resolve the connect identifier specified. I am using: username: hr password: hrpassword database: pdb...

how to reclaim space from table fnd_lobs?

AskTom - Hot Questions - Fri, 03/17/2017 - 00:00
I want to reclaim the space from the table "fnd_lobs", which measures 100 gb, but it actually measures 50 gb, I have not found any method to recover the other 50 gb. they are looking for in google, and in a few forums, there was a user who said that...

Expdp and impdp

AskTom - Hot Questions - Tue, 03/14/2017 - 00:00
I have a requirement to export a full database using EXPDP and import only three schemas using remap_schemas to a different schema than the original. How can I achieve that?

UTL FILE FRENAME (mv) doesn't work - ORA-29292

AskTom - Hot Questions - Mon, 03/13/2017 - 00:00
I have a a shared sftp directory mounted \xx.xx.xx.xx\SFTP_AX\ /sftp/ cifs user,uid=54321,gid=54321,suid,username=user,password=pass,workgroup=application,file_mode=0775,dir_mode=0775,rw 0 0 Then a PL/SQL that moves my file to another mounted dir...

Advanced Compression Options in Oracle 11g

AskTom - Hot Questions - Fri, 03/10/2017 - 00:00
Hi Chris/Connor, Can you please share any document on Advanced Compression Options in Oracle 11g. I have searching on this and getting only theoretical stuffs. Could you please share any reference, blog to study on this to understand its use on OL...

Retreive rows of primary key in batches

AskTom - Hot Questions - Mon, 03/06/2017 - 00:00
I want to fetch a 80Million table, due to performance constraints, I want to process the data in batches(5000), i want to get the firstKey and lastKey for each batch. Each batch has 5000 unique Id records. Example of output:- BATCH_ID FIRS...

Daily Insert, Delete on table

AskTom - Hot Questions - Mon, 03/06/2017 - 00:00
I have a table tb_card_data CARD_NO TYPE EMAIL MODULE CREATED_ON SEQ_NO ------- ---- ----- ------ ---------- --------- 12345 C x@a.com MOD_1 06/03/2017 20170306000001 (create_on + sequence number) .. so on around 500k records for MOD...

Why won't my table go in memory?

AskTom - Hot Questions - Fri, 03/03/2017 - 00:00
I downloaded the developers days VM with the latest 12.2 installation on it, and wanted to try out the in memory feature of 12c. I've read the documentation and done everything I think I need to, however unfortunately my table doesn't seem to want to...

I have to import the specific tables using DBMS_DATAPUMP PACKAGE . How to exclude the unwanted tables ?

AskTom - Hot Questions - Thu, 03/02/2017 - 00:00
Hi Tom , I have used the below import par file to exclude the tables those names are with $ DIRECTORY=FEB09_E DUMPFILE= MXPRD_01feb09_%U.dmp LOGFILE=IMP_EDRPRE.log REMAP_SCHEMA=MXPRD:MAXIMO remap_tablespace=MXPRD_DATA:MAXIMO_DATA,MXPRD_INDE...

Pages