DBA Blogs

SQL statements inside AWR views

Tom Kyte - Wed, 2018-06-06 07:26
Hi Tom, I'm trying to find the history of executions for a given sql statement by using AWR views like DBA_HIST_ACTIVE_SESS_HISTORY, DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT, DBA_HIST_SQL_PLAN etc. I find the SQL_IDs for SQL by querying DBA_HIST_SQLT...
Categories: DBA Blogs

Display master child data as a set - from 2 different tables

Tom Kyte - Wed, 2018-06-06 07:26
Hi , I will be glad if you could help me in this. I have a Parent Table ( ORDER_HEADER ) and a Child table ( ORDER_LINE ). They are linked by order_id. ORDER_HEADER holds order details for customers and ORDER_LINE holds the child lines for ea...
Categories: DBA Blogs

Parsing a CLOB field with CSV data and put the contents into it proper fields

Tom Kyte - Wed, 2018-06-06 07:26
My Question is a variation of the one originally posted on 11/9/2015. parsing a CLOB field which contains CSV data. My delimited data that is loaded into a clob field contains output in the form attribute=data~ The clob field can contain up to 6...
Categories: DBA Blogs

process limit and sessions

Tom Kyte - Wed, 2018-06-06 07:26
Hi there, For the past few days,during the peak hours i am getting the following error in a frequent way " Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found " I googled it first,...
Categories: DBA Blogs

Which is default if session is interrupted - COMMIT or ROLLBACK?

Tom Kyte - Wed, 2018-06-06 07:26
Hi Tom, I use Oracle SQL Developer. I do not use autocommit and in normal situation I always use after some block of SQL commands (INSERT, DELETE etc.) COMMIT, or ROLLBACK. But what does happen if I do not finish this block of SQL command in...
Categories: DBA Blogs

Composite Range-Hash interval partitioning with LOB

Tom Kyte - Tue, 2018-06-05 13:06
Hi, I would like to partition a table with a LOB column using Range-Hash interval partitioning scheme. But I not sure how the exact partition gets distributed in this scenario and also I noticed following differences based on how I specify my p...
Categories: DBA Blogs

Index Column Order – Impact On Index Branch Blocks Part I (Day-In Day-Out)

Richard Foote - Mon, 2018-06-04 07:16
I recently replied on Twitter to some comments regarding an excellent blog post by Franck Pachot – Covering indexes in Oracle, and branch size, where I disagreed somewhat with one of the conclusions stated in the post: “ensure that selective columns appear as early as possible (without compromising the index access efficiency of course) in order to […]
Categories: DBA Blogs

SID YES SERVICE_NAME NO in tnsnames.ora

Tom Kyte - 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

Application Development Services Update

Application Container Cloud Service Secure Environment Variables Environment variables can now be secured by using the user interface or the deployment.json file. The value of the secure...

We share our skills to maximize your revenue!
Categories: DBA Blogs

storing password history

Tom Kyte - 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?

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

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

Tom Kyte - 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

Virtual Developer Workshop - Container Native Application Development

Want to  take advantage of cloud computing frameworks, which are composed of loosely-coupled cloud services. Oracle would like to invite you to The Oracle Cloud...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs