AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 2 hours 34 min ago

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

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 ,i want create table ...

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

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

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

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"

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

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)

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

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

Mon, 04/10/2017 - 00:00
We have an Oracle 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

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

Sun, 03/19/2017 - 00:00
I am getting the following issue when trying to connect to the database with Oracle Forms Builder ( 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?

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

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

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

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

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

Mon, 03/06/2017 - 00:00
I have a table tb_card_data CARD_NO TYPE EMAIL MODULE CREATED_ON SEQ_NO ------- ---- ----- ------ ---------- --------- 12345 C 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?

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 ?

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...

Procedure help - privileges

Fri, 02/24/2017 - 00:00
I am creating a procedure within a schema that has dba access and delete any table privilege. I am deleting from other schemas using this procedure. It gives a compilation error saying the table doesn't exist. Do I need to have direct delete privile...