DBA Blogs

how to get regular updates on database space left and database used space in my table

Tom Kyte - Wed, 2016-10-19 21:46
how to get regular updates on database space left and database used space in my table . i have created a table with 3 columns , dbspacetotal & dbspaceused,dbspaceremaining. how to get data into these columns when i insert update or delete in my...
Categories: DBA Blogs

running application with an app_user instead of schema owner user

Tom Kyte - Wed, 2016-10-19 21:46
Hi I have a java application that requires access to oracle database with an app_user instead of schema owner user and this is for security purposes. - The schema owner user is the user that owns oracle objects that need to be accessed from j...
Categories: DBA Blogs


Tom Kyte - Wed, 2016-10-19 21:46
i learn all sql and pl/sql concept very well. but still not working on any project. so my question is please give me project or idea to develop project in oracle(SQL and PL/SQL) which helps to improve my knowledge very well... please give me the prj...
Categories: DBA Blogs

Need help on dbms_scheduler

Tom Kyte - Wed, 2016-10-19 21:46
Hi Tom, I have a scheduler which is linked to my package. The package was running for long and hence I cancelled the task. Now when I try to run the package back, the scheduler is not running. I checked in "USER_SCHEDULER_JOB_LOG" AND THE...
Categories: DBA Blogs

Quickly built new Python graph SQL execution by plan

Bobby Durrett's DBA Blog - Wed, 2016-10-19 17:51


I created a new graph in my PythonDBAGraphs to show how a plan change affected execution time. The legend in the upper left is plan hash value numbers. Normally I run the equivalent as a sqlplus script and just look for plans with higher execution times. I used it today for the SQL statement with SQL_ID c6m8w0rxsa92v. It has been running slow since 10/11/2016.

Since I just split up my Python graphs into multiple smaller scripts I decided to build this new Python script to see how easy it would be to show the execution time of the SQL statement for different plans graphically. It was not hard to build this. Here is the script (sqlstatwithplans.py):

import myplot
import util

def sqlstatwithplans(sql_id):
    q_string = """
ELAPSED_TIME_DELTA/(executions_delta*1000000) ELAPSED_AVG_SEC
where ss.sql_id = '""" 
    q_string += sql_id
    q_string += """'
and ss.snap_id=sn.snap_id
and executions_delta > 0
order by ss.snap_id,ss.sql_id,plan_hash_value"""
    return q_string

database,dbconnection = 
util.script_startup('Graph execution time by plan')

# Get user input


mainquery = sqlstatwithplans(sql_id)

mainresults = dbconnection.run_return_flipped_results(mainquery)


date_times = mainresults[0]
plan_hash_values = mainresults[1]
elapsed_times = mainresults[2]
num_rows = len(date_times)

# build list of distict plan hash values

distinct_plans = []
for phv in plan_hash_values:
    string_phv = str(phv)
    if string_phv not in distinct_plans:
# build a list of elapsed times by plan

# create list with num plans empty lists     
elapsed_by_plan = []
for p in distinct_plans:
# update an entry for every plan 
# None for ones that aren't
# in the row

for i in range(num_rows):
    plan_num = distinct_plans.index(str(plan_hash_values[i]))
    for p in range(len(distinct_plans)):
        if p == plan_num:
# plot query
myplot.xlabels = date_times
myplot.ylists = elapsed_by_plan

myplot.title = "Sql_id "+sql_id+" on "+database+
" database with plans"
myplot.ylabel1 = "Averaged Elapsed Seconds"


Having all of the Python code for this one graph in a single file made it much faster to put together a new graph. Pretty neat.


Categories: DBA Blogs

Tim Gorman at AZORA meeting tomorrow in Scottsdale

Bobby Durrett's DBA Blog - Wed, 2016-10-19 10:34
#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
Arizona Oracle User Group – October 20, 2016

Thursday, Oct 20, 2016, 12:30 PM

Republic Services – 3rd Floor Conference Room
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

16 AZORAS Attending

Change In Plans -Tim Gorman comes to Phoenix! Stephen Andert had a sudden business commitment making it impossible for him to speak at Thursday’s meeting.Fortunately, Tim Gorman of Delphix will be coming from Denver to speak instead. Tim is an internationally-renowned speaker, performance specialist, member of the Oak Table, Oracle Ace Director, …

Check out this Meetup →

Phoenix area readers – I just found out that Oracle performance specialist and Delphix employee Tim Gorman will be speaking at the Arizona User Group meeting tomorrow in Scottsdale.  I am looking forward to it.


Categories: DBA Blogs

SQL group by query shenanigans

Tom Kyte - Wed, 2016-10-19 03:26
Hi Chris or Connor, Saw you guys at OOW so I thought I toss a basic SQL query to you. I?ll use the HR.EMPLOYEES table to represent my problem so forgive me if it's a bit contrived. I?ll like to construct a query to sum the salaries grouped by ...
Categories: DBA Blogs

Grant Access on Table

Tom Kyte - Wed, 2016-10-19 03:26
I've given grant to one user on a table but user unable to access the table though I'm getting output as grant succeeded. The scenario is 1. There is one database A on remote location. 2. A is trying to access some table on database B(place on othe...
Categories: DBA Blogs

View that opens and runs once. The next time it's opened it hangs.

Tom Kyte - Wed, 2016-10-19 03:26
I have a view that when you open the view in say something like TOAD, or MS Access, or SMSS it opens fine the first time. The next time you open it or select from it it hangs. Creating the view I get no errors or warnings. If I open the view i...
Categories: DBA Blogs

Bulk Collection Save Exception

Tom Kyte - Wed, 2016-10-19 03:26
Dear Tom, Please help me on the below... we are having BULK COLLECT option which will save exception like below FORALL i in 1tab.count SAVE EXCEPTIONS INSERT INTO table values(obj(i)); exception when excep_bulk_err then ...
Categories: DBA Blogs

Oracle RAC without ASM

Tom Kyte - Wed, 2016-10-19 03:26
Hi My aim is to install oracle rac 11gR2 without ASM. What's required steps to achieve this and this system (rac installation) and what's pros / cons for this kind of installation . Regards, Oussema
Categories: DBA Blogs

DBLink for Local Tables

Tom Kyte - Wed, 2016-10-19 03:26
Nice day i'm from peru so my english is not the best. The database is a: Oracle Database 11g Release - 64bit Production PL/SQL Release - Production "CORE Production" TNS for Linux: Version - Product...
Categories: DBA Blogs

Database PL/SQL developer

Tom Kyte - Wed, 2016-10-19 03:26
what are the roles and responsibilities of PL/SQL developer? what kind of knowledge required for oracle PL/SQL developer? what will be the future of PL/SQL developer?
Categories: DBA Blogs

PL/SQL Database Programming Question

Tom Kyte - Wed, 2016-10-19 03:26
I am struggling to figure out which LOOP statement to use. Here's the question: Each day, starting on Monday, the price will drop 5% from the previous day?s price. Monday?s sale price will be 5% less than what is stored in the database in the BB...
Categories: DBA Blogs

Oracle IaaS Workshop for EMEA Partners

Oracle Cloud Platform: Infrastructure as a Service Workshop for Partners ...

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

Massive Update

Tom Kyte - Tue, 2016-10-18 09:06
Dear Team, Kindly help me for below problems 1) Needs to update all the records of 1 billion record table. Only one column values to be updated to reverse the order (abcde -> edcba) 2) Needs to update records based on a column condition (column ...
Categories: DBA Blogs

Extract Logical Operators (AND/OR) from String

Tom Kyte - Tue, 2016-10-18 09:06
I want to help to extract Logical Operators (AND/OR) from string having n numbers of operators For Example: ((1=1 AND 1=1) OR 1=1) AND (1=1 AND 1=1)
Categories: DBA Blogs


Tom Kyte - Tue, 2016-10-18 09:06
Hi? this my query I have a dictionary table create table D_CONFIG_TST ( row_name VARCHAR2(1500), -- text field with name of row row_number VARCHAR2(50), -- text field with row number for order row_array VARCHAR2(15...
Categories: DBA Blogs

Change Data Capture to get modified records of a table

Tom Kyte - Tue, 2016-10-18 09:06
Hello Tom, Hope you are doing good! Request your suggestions for the following scenario. We have a requirement wherein we have to migrate modified/delta data on a table(Modified by DML statements) from Oracle to MongoDb. To do this i have su...
Categories: DBA Blogs

DBMS_OUTPUT to Query output

Tom Kyte - Tue, 2016-10-18 09:06
Hello Tom, First thanks for helping me out. Few days ago you explained how to insert dbms_output into temporary tables or nested table. This was the example: This example works perfectly if I have write permission in the database, Is there a wa...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs