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: 36 min 57 sec ago

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

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

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

after update count of executed updates

Thu, 02/16/2017 - 00:00
I have a script executed in SQL*PLUS command line that should give out the summ of all real updated rows from the WHERE clause after all update executes simiilar as on execute update command directely in SQL*Plus with "1 row updated". But the fol...

char (10) data type hosting numeric data for natural keys

Thu, 02/16/2017 - 00:00
Hi, Can you please provide some guidance on the possible performance issue one might have in creating a char(10) data type that hosts natural keys? Also, I'm baffled by the following results.. THIS WORKS FINE even char(10) blank pads the ...

Optimizer Dynamic Sampling at OPTIMIZER_DYAMIC_SAMPLING level 2

Wed, 02/15/2017 - 00:00
At my Oracle Oracle Standard Edition database, at OPTIMIZER_DYNAMIC_SAMPLING = 2 which is the default, I see Dynamic Sampling is happening for some of the SQLs for which all tables already have already gathered table statistics. Due to this,...

How to extract substrings enclosed within double quotes from a string

Sun, 02/12/2017 - 00:00
I have a requirement to extract column names (there could be multiple instances) from a SQL string. The column names will always be enclosed within double quotes. e.g. "le code" = 802 AND "principal amount" > 1200 OR "branch id" = 'ABC' <b>Req...

What is your salary ?

Fri, 02/10/2017 - 00:00
Tom, WHat is your salary? Thanks

please help me

Thu, 02/09/2017 - 00:00
simply retrieving a table using table name dynamically DECLARE TABL_NAME VARCHAR2(255):='MY_DETAILS'; STRNG VARCHAR2(255); begin STRNG :='SELECT * FROM' || TABL_NAME; DBMS_SQL.PARSE (STRNG); END; im new to sql please help why this simple ...

Shuffle values randomly in columns

Tue, 02/07/2017 - 00:00
Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible. I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit mo...

Insert Into table_name Select for table with CLOB column is very slow.

Wed, 02/01/2017 - 00:00
Hi Sir, I have a activity for making non partition table to partition table, the table has clob column i was using below approach to fulfill the activity. rename existing table to _old create new partition blank table insert into new blank pa...

SQL Query Help Required

Tue, 01/31/2017 - 00:00
I'm new to Oracle and have got a requirement from my client as follows. Eventhough I have got the PL/SQL solution for this requirement, I'm interested in understanding whether a SQL based solution is possible rather than a PL/SQL approach. If yes, p...