Feed aggregator

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

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

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

Procedure help - privileges

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - Wed, 02/15/2017 - 00:00
At my Oracle 12.1.0.2 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

AskTom - Hot Questions - 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 ?

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

please help me

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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.

AskTom - Hot Questions - 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...

Pages