Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 hour 50 min ago

SID YES SERVICE_NAME NO in tnsnames.ora

Sun, 2018-06-03 06:06
Hello Tom, We use tnsnames.ora . 1) What is the difference between SID entry and SERVICE_NAME in the tnsnames.ora 2) When I put SID the connection is successful, when I put SERVICE_NAME it says tns could not resolve service name So This w...
Categories: DBA Blogs

storing password history

Fri, 2018-06-01 17:26
<code> I have a table create table user_password_history (user_cd varchar2(3), user_pass varchar2(30), created_date date); i want to keep last 5 records of each user in this table. kindly guide me how to achieve it. Thanks for your suppor...
Categories: DBA Blogs

The one case where we need an index rebuild - how to avoid it?

Fri, 2018-06-01 17:26
Having read Tom's books I know that in general there's no reason to rebuild indexes, and so we don't. However, we have one particular index which does benefit from rebuilding. Since it's a locally-partitioned index on a date-partitioned table we ...
Categories: DBA Blogs

CHAR Index (vs) NUMERIC Index

Fri, 2018-06-01 17:26
Hi Tom, In comparison to an Index on CHAR field and Index on NUMERIC field pls let me know which Index(whether CHAR or NUMERIC) would be faster. Thanx & Regards, Madhusudhana Rao.P
Categories: DBA Blogs

TNS-12541 : TNS: No Listener

Fri, 2018-06-01 17:26
Hi, In order to configure Standby database I have created one listener "Prod" & TNS Service "To_Stand" on Primary Side.Listener created successfully & its running.But the problem is TNS is not establishing connection with the database.Its saying "No...
Categories: DBA Blogs

locking explain plan table

Thu, 2018-05-31 04:46
Hi Tom, while creating explain plan for query, explain plan table is getting locked until commit or rollback. If we don't want to commit / rollback, is it a problem that explain plan table stays locked? also while it is locked we don't have any prob...
Categories: DBA Blogs

crsctl and background processes of rac

Thu, 2018-05-31 04:46
Hi Tom, Which background process or any process works to retrieve the result of command "crsctl stat res -t" Regards, S.Alam
Categories: DBA Blogs

How to generate some big test tables and rapidly export their SQL data

Wed, 2018-05-30 10:26
Hello teams, Our Developer manager asks me to export Oracle production database's three big tables with <i><b>CSV</b></i> or this format <i><b>"insert ... into ..."</b></i> for giving another App teams (their database is MySQL 5.7) once again, I r...
Categories: DBA Blogs

Database Link between Oracle 10g and Oracle 12c

Wed, 2018-05-30 10:26
Dears, am trying to create a dblink between Oracle 10g and Oracle 12c the dblink is created successfully my case is as below when select data from a table directly over dblink give the result without problems but call a package or stored procedur...
Categories: DBA Blogs

expdp between two scn numbers

Wed, 2018-05-30 10:26
Hi Team, Can we run expdp command between two scn numbers? Thanks Krishna
Categories: DBA Blogs

I just want to know the difference between the below two queries.

Wed, 2018-05-30 10:26
Q: List the Emps who are senior to their own MGRS. select * from emp e,emp m where e.mgr = m.empno and e.hiredate < m.hiredate; select * from emp e,emp m where e.empno= m.mgr and e.hiredate > m.hiredate; the above two queries are getting s...
Categories: DBA Blogs

11g Apex Email not working from Sqlplus

Mon, 2018-05-28 21:46
Hi Tom, I can successfully send email from Apex application using apex_mail.send. When I try to use the same from sqlplus it gives the following Error BEGIN apex_mail.send (p_to => 'test@test.com', p_fr...
Categories: DBA Blogs

Why might Consistent Reads be increasing with every iteration of a select cursor being run by PL/SQL?

Mon, 2018-05-28 03:26
<code></code>We have some PL/SQL that is running many sql statements, and taking an awfully long time to complete. (It?s Oracle code, btw, in E-Business, not custom). The problem seems to be a couple of the statements, (run thousands of times each) w...
Categories: DBA Blogs

Full text indexes in Oracle XE

Mon, 2018-05-28 03:26
hello Is it possible to combine b-tree indices with Full-Text indices in oracle 11(express)? Merci
Categories: DBA Blogs

Need some Oracle Request syntaxe to extract informations

Mon, 2018-05-28 03:26
hy all, it's my first discussion that i post . please help me: i work on a solution of supervision of oracle database, and i need two request syntaxe to extract informations: The first: the requset to show the most Oracle query consume cpu time ...
Categories: DBA Blogs

Is it possible - to show OS disk free space together with DBA_DATAFILES data ?

Mon, 2018-05-28 03:26
In some of my custoner's databases the DBAs are using AUTOEXTENSIBLE datafiles, but with many datafiles comparting the same filesystem, in this sense : tablespace A, datafiles /u01/oradata/ts_A_file01.dbf autoextend unlimited tablespace B, da...
Categories: DBA Blogs

Exadata Vs RAC

Mon, 2018-05-28 03:26
Tom ? My understanding of RAC( or grid computing) , we use cluster of not so expensive servers for higher availability. But Oracle is marketing Exadata ( expensive severs ? relatively speaking ) for performance / higher availability , so on. ( if ...
Categories: DBA Blogs

Regular Expression is not working if the search criteria with LIKE and NOT LIKE in single input field

Sat, 2018-05-26 14:46
Hi Tom, Need your help ! Please find the LiveSQL link Thanks in Advance ! I have a table st_exp with s_desc column only, user has option to search by s_desc criteria. Scenario :The user may enter text critiria LIKE and NOT LIKE in the ...
Categories: DBA Blogs

Error in date comparison for partitioned table

Fri, 2018-05-25 02:06
Hi Tom, I am seeing a strange issue with a query which queries data from a partitioned table having sub-partitions. Please see table, query and the error. Can you please help, what could be the reason for this error? <code>CREATE TABLE trans_de...
Categories: DBA Blogs

Procedure Performance Number vs Pls_integer

Fri, 2018-05-25 02:06
I have task to improve performance in some of the packages and procedures in our application. We have 1 package and it has subprograms around 15-20 procedures. Below are my clarifications required. Iam making changes to datatypes from NUMBER to...
Categories: DBA Blogs

Pages