DBA Blogs

ORA-29400: data cartridge error -- Windows

Tom Kyte - Mon, 2017-01-30 04:06
Hi, Ever since I have upgraded my database from 11.2.0.2 to 11.2.0.4 I started seeing these errors in my alert log: Sat Dec 10 06:00:12 2016 DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file. Errors in file D:\APP\ORAC...
Categories: DBA Blogs

"ORA-01733: virtual column not allowed here" from update on view when SELECT FOR UPDATE OF

Tom Kyte - Mon, 2017-01-30 04:06
<code>Tom, I have problem while updating or even selecting if the statement contains FOR UPDATE OF clause against a view where instead of trigger perform insert, update, delete. One of view column is a calculated column which contains decode functio...
Categories: DBA Blogs

Histogram in oracle

Tom Kyte - Mon, 2017-01-30 04:06
Hi Tom, Please tell me about Histogram in Oracle give an example?
Categories: DBA Blogs

restore single table from rman full backup

Tom Kyte - Mon, 2017-01-30 04:06
Hi I have a rman full backup and I want to restore single table due to drop that table how can I do this. I can not create a clone due to space and resources issue.. Thanks
Categories: DBA Blogs

Oracle Big Data Cloud Service - Compute Edition released!

Oracle Big Data Cloud Service - Compute Edition (BDCS-CE) has been released and is generally available in 4 different Oracle Cloud Data-centers (Chicago, Ashburn, Slough and Amsterdam). ...

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

Variable value pass as new line in shell script

Tom Kyte - Fri, 2017-01-27 21:26
Hi TEAM, I have code as below but parameter value displaying as new line so my code is failing please help me asap . STEP 1 : OUTPUT=$( SQLPLUS -S /NOLOG CONNECT USER/PASSWORD SELECT ENAME FROM EMP; EXIT EOF ) STEP2 : READING THE P...
Categories: DBA Blogs

Extended Statistic columns

Tom Kyte - Fri, 2017-01-27 21:26
We have a few tables that have just under 255 columns. According to Oracle documentation, the max number of columns that can be stored in a block is 255. If Extended stats are produced for this table, which creates additional hidden columns, could th...
Categories: DBA Blogs

Database connection closed on port

Tom Kyte - Fri, 2017-01-27 21:26
Hi team, One day before application not be able to connect with oracle database i have check the alert log it is showing error like - Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 11.2.0.1.0 - Productio...
Categories: DBA Blogs

Combine collection content by key

Tom Kyte - Fri, 2017-01-27 21:26
1. We receive data in the following format in a collection. We do not have control on this data. (1, 'abc') (1, 'def') (1, 'pqr') (2, 'xyz') (2, 'abc') (3, 'mno') (4, 'abc') (4, 'def') (4, 'pqr') (4, 'jkl') 2. rec_tab is the interm...
Categories: DBA Blogs

Query rewrite

Tom Kyte - Fri, 2017-01-27 21:26
Hi Team, Issue: Need improve performance of the query with temp as ( SELECT CLAIM_ACCOUNT, CLAIM_PATIENT, CLAIM_TOTALCHARGE,CLAIM_ACTPAYAMOUNT, CLAIM_PATIENTRESP, CLAIM_DATE, CL_SVC_DOS, SUM(SERVICE_ADJUSTAMOUNT) SERVICE_ADJUSTAMOUNT, SUM(...
Categories: DBA Blogs

Memory usage in heavily partitioned database

Tom Kyte - Fri, 2017-01-27 21:26
Hi From the depths of internet I have found sql-statement which I slighly modified. Purpose was to find out what objects exists in the buffer cache and many blocks they were using. </> with t1 as ( select o.owner, o.object_name ...
Categories: DBA Blogs

Performance and commits

Tom Kyte - Fri, 2017-01-27 21:26
Hi team, I have a simple question. Let's say i have 20,000 txn for one session and i have multiple sessions say 10. So is it good to mention commit frequency as 300-400 in code for better performance. Please let me know as what i am follo...
Categories: DBA Blogs

Find the roles that are currently granted to current users who have not logged in for more than 180 days

Tom Kyte - Fri, 2017-01-27 21:26
How do I find the roles that are currently granted to current users who have not logged in for more than 180 days? I want to revoke their user roles. Thanks.
Categories: DBA Blogs

Performace problems with query

Tom Kyte - Fri, 2017-01-27 21:26
Hi everyone, i have de query below, and i am having timeout issues running it. The table br_movimientoctacte has 205.000 files. I think is not a big number for oracle. This query is part of a view, but i analyze it an the problema is in the query...
Categories: DBA Blogs

Regarding truncate partition automation

Tom Kyte - Fri, 2017-01-27 21:26
Hi I have written a script to truncate oldest partition by inserting partition_name and high_value into a temporary table. DECLARE min_highval varchar(1000); part_name varchar(30); trunc_part varchar2(1000); create_part_temp varchar2(...
Categories: DBA Blogs

measuring the performance between 9i and 12c

Tom Kyte - Fri, 2017-01-27 03:06
Dears, We have been upgrading from 9i to 12c and trying to ensure 12c performs better than 9i as part of the upgradation process, so we selected 20 different queries, which covered multiple tables, joins, functions, and functionalities e.t.c., We ...
Categories: DBA Blogs

Looping for Nested dimensional objects in an array

Tom Kyte - Fri, 2017-01-27 03:06
I have CLIENT_ORDER_OBJ as OBJECT (order_id number, Order_type varchar2(25)); CLIENT_CITY_OBJ as OBJECT (city VARCHAR2(25)) TEST_OBJ is OBJECT with below attributes (client_id VARCHAR2(25), client_name ...
Categories: DBA Blogs

execution of store procedure

Tom Kyte - Fri, 2017-01-27 03:06
Dear Sir, Q 1 > I want to know how much time to take complete execution of store procedure either procedure or function with resource consumption. Q 2 > Can I know execution path of sql statement written in the store procedure? Q 3 > how we can ex...
Categories: DBA Blogs

insufficient privileges on SYS.DBMS_SESSION

Tom Kyte - Fri, 2017-01-27 03:06
I have this query. select count(*) from TABLE (fn_report_tin_con( 'profession_cd' /**P*/, 'organization_type_cd' /**P*/, 'CMM0016' /**P*/, '20161201' /**P*/, '20170126' /**P*/, '' /**P*/, '' /**P*/)); Error is: ORA-01031: insufficient pr...
Categories: DBA Blogs

SQL%ROWCOUNT in Java

Tom Kyte - Fri, 2017-01-27 03:06
Hi, This may sound Java question but I have to ask this. I want to know if there is a way to access sql%rowcount in Java to get no. of rows affected. My DML statements are native SQL calls from Java and want to know no. of rows affected by DML. ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs