DBA Blogs

Explanation of Hints

Tom Kyte - Mon, 2016-07-18 01:26
Hi Tom, 1)first up all great thanks to you for spending time for me explain about following hints Parallel append index no_index first_rows and all_rows with an example in which situation we may use what hint in select the data from table? ...
Categories: DBA Blogs

Member procedures & Member function in plsql i.e member sub programs in oracle(plsql)

Tom Kyte - Mon, 2016-07-18 01:26
Hi Tom, 1)why we are using,what is purpose of Member procedures & member functions in plsql subprograms explain an example? 2)Difference between stored subprograms and member subprograms?
Categories: DBA Blogs

How to set all elements of collections

Tom Kyte - Mon, 2016-07-18 01:26
I've three questions 1) Suppose I have a collection of numbers say 300 values & i want to initialise with number 72 what would be the best way to do so ,rather than writing 300 times 2) I've to generate reports in pdf so what would you sugg...
Categories: DBA Blogs

Will open cursor hold up more tablespace when it is not closed?

Tom Kyte - Mon, 2016-07-18 01:26
Hi Tom, I am using oracle 11g and the tablespaces keeps growing, I have recently identified a issue with open cursors which is not closed when the session was closed. Will open cursor eats up all the spaces which leads to consume more temp spa...
Categories: DBA Blogs

ORA-01578: ORACLE data block corrupted (file # , block # )

Tom Kyte - Mon, 2016-07-18 01:26
Hi Tom, I m getting following error in my production data base alert log since last 5 month. Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_j000_26775.trc: ORA-01578: ORACLE data block corrupted (file # , block # ) ORA-01578: O...
Categories: DBA Blogs

ORA - 08103:Object No Longer Exists

Tom Kyte - Mon, 2016-07-18 01:26
Hi, I am getting an error Ora-08103: Object No longer exists when select queries are fired against a partitioned table having local Bit map indexes. But when the same process/job (which fires select query) is restarted the issue does not come up a...
Categories: DBA Blogs

Why the "Row Source Operation" ('reality exec') differs from the "Execution Plan" ('guess exec')

Tom Kyte - Sun, 2016-07-17 07:06
Hi, Team There's a procedure in our Oracle 11.2.0.1 DB which captures data from MS SQL Server through DB Link, since the context is quite long (cursors, pragma autonomous_transaction and loop included), let us take it brief like this: CREATE OR...
Categories: DBA Blogs

XML Update

Tom Kyte - Sun, 2016-07-17 07:06
Hi Tom, I need to update some attribute(XML) in oracle table whose datatype is XML. Is there any package provided by oracle which help me? Or if you can tell me anyway how can I update it.
Categories: DBA Blogs

clone(duplicate active Database)

Tom Kyte - Sun, 2016-07-17 07:06
I Want to clone a database from one computer to another computer, both computer connected with lan. The database which i have to clone is available on computer 2. on computer 1 i have created an instance using oradim utility On computer 2 <code>...
Categories: DBA Blogs

Add_months

Tom Kyte - Sun, 2016-07-17 07:06
Hi Guys, I am just a little bit confused regarding the following : select add_months(to_date('30/01/2016','DD/MM/YYYY'),1) from dual Result is : 29/02/2016, shouldn't it be 28/02/2016 ?? Thanks Mohannad
Categories: DBA Blogs

issue while adding new apps node in oracle EBS r12.2.5

Tom Kyte - Sun, 2016-07-17 07:06
Hi DBA Experts, I am facing following issue while adding new application node please help me to fix this issue. Executing command: perl /u02/applmgr/SR1225/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl ebs-create-node -contextfi...
Categories: DBA Blogs

export to CSV

Tom Kyte - Sun, 2016-07-17 07:06
Tom, I need to export data from a table into a .csv file. I need to have my column headres in between "" and data separated by ','. also depending on the column values a row may be printed upto 5 times with data differing in only one field. ...
Categories: DBA Blogs

Links for 2016-07-16 [del.icio.us]

Categories: DBA Blogs

Finding transacted tables,

Tom Kyte - Sat, 2016-07-16 12:46
Hello, Using Oracle data dictionary, how to find out the list of tables that have undergone Insert/Update/Delete by a particular user account in the last 7 days? Also, if possible I want to know the number of transactions happened and the size of...
Categories: DBA Blogs

What is best way to collect GLOBAL STATS of a table with 4 billion records

Tom Kyte - Sat, 2016-07-16 12:46
Hi Tom I need to take global stats collection for a table with 4 billion records. This is a partitioned table but I need to collect STATS globally. This table was last analyzed in Sept 2015. As of now following is Table statistics Actual Number...
Categories: DBA Blogs

DENSE_RANK function

Tom Kyte - Sat, 2016-07-16 12:46
Hi Tom, I have a problem with DENSE_RANK function. Let's see an example: <code>CREATE TABLE test_rank (val number); INSERT INTO test_rank VALUES(1); INSERT INTO test_rank VALUES(2); INSERT INTO test_rank VALUES(3); INSERT INTO test_rank VALUE...
Categories: DBA Blogs

DROP TABLE performance/delay

Tom Kyte - Sat, 2016-07-16 12:46
Hello Tom, We have a new application which has serious performance problems, and a reason for that might be the Oracle DB. Unfortunately, tests could not find the root cause yet. Now I saw a very strange behavior of the DROP TABLE performance: ...
Categories: DBA Blogs

count of distinct on multiple columns does not work

Tom Kyte - Sat, 2016-07-16 12:46
Hi, I am trying to count the number of distinct combinations in a table but the query gives error. For example, create table t(a varchar2(10), b varchar2(10), c varchar2(10)); insert into t values('a','b','c'); insert into t values('d','e'...
Categories: DBA Blogs

Creating and Executing a stored procedure that dynamically builds a table getting ORA-06550: PLS-00103

Tom Kyte - Sat, 2016-07-16 12:46
Receiving ORA-06550: PLS-00103 error when trying to execute a procedure that dynamically creates a table. I have created a pl/sql script to dynamically create a table: declare l_tablename varchar2(30) := 'TEST_3_'||to_char(sysdate, 'YYYYMMDD')...
Categories: DBA Blogs

Average of input two Dates

Tom Kyte - Fri, 2016-07-15 18:26
How Can i create a oracle sql Function which can take two input Dates and Display their Average.For Ex:- input :-28-Aug-2016,4-Sep-2016 output:- 1-Sep-2016
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs