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

Error in execution job fill data from a Database

Tom Kyte - Fri, 2017-01-27 03:06
I have a problem in the job execution in our routine application. This job fill data from a Database. The job name is #P1CPC02, it contains the next code: AGENT BCPORA17 USER root SCRIPTNAME - /xcom_rep/CREP/carga/bin/cargaNormal.sh ...
Categories: DBA Blogs

ORA-30927: Unable to complete execution due to failure in temporary table transformation - When using WITH + UNION

Tom Kyte - Fri, 2017-01-27 03:06
Basically when I run the below query or any like it (actually pulling data) I am getting the ORA-30927 error, I can run this query without the UNION and it will not give this error, however trying to run multiple SELECT with UNION (in order to displa...
Categories: DBA Blogs

Bulk collect into multiple collections

Tom Kyte - Fri, 2017-01-27 03:06
I need to populate two collection as output from a stored procedure. The only difference between the two is the filter used to select the data. Our current method requires two "select x bulk collect into y from z where filter" statements (as illust...
Categories: DBA Blogs

Links for 2017-01-26 [del.icio.us]

Categories: DBA Blogs

get row count from all the tables from different schemas and store in materialized view

Tom Kyte - Thu, 2017-01-26 08:46
Daily activity is to fetch row count from all the tables in different schemas using below query. But issue is its taking too much time [around 50 mins to fetch 50000 rows]. SELECT b.source_name, a.table_name, alh.A_ETL_LOAD_SET_KEY, to_numbe...
Categories: DBA Blogs

Procedure calling

Tom Kyte - Thu, 2017-01-26 08:46
Hello Tom, I have to call a procedure inside a procedure more than 35k times. Can I do this using a normal loop or bulk collect-forall will be better approach? Thanks in advance.
Categories: DBA Blogs

Kill session

Tom Kyte - Thu, 2017-01-26 08:46
I executed a stored procedure in Oracle 11g using utl_stmp that had a loop that was not properly ended. Needless to say, I received thousands of emails. I requested my DBA to kill the session so that I can stop receiving emails. After the session was...
Categories: DBA Blogs

Schema Creator Name

Tom Kyte - Thu, 2017-01-26 08:46
Hello Tom, Is there a way to know the schema/User creator in Oracle?
Categories: DBA Blogs

the best way to count distinct tuples

Tom Kyte - Thu, 2017-01-26 08:46
I was always afraid to ask, but probably you can tell me: what is the best way to count distinct tuples ? Why does just <code> select count(distinct a, b) from ( select 1 a, 1 b, 1 c from dual union all select 1 a, 1 b, 2 c from dual union...
Categories: DBA Blogs

Index fragmentation - REBUILD Vs SHRINK SPACE

Tom Kyte - Thu, 2017-01-26 08:46
Hi Chris/Connor, We have gather list of tables/Index along with Allocated space, Used space and %fragmentation. Could you please help to how do analysis on Indexes e.g. based on allocated/used space which index we may need to REBUILD or SHRINK. ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs