DBA Blogs

getting string

Tom Kyte - Fri, 2017-02-17 12:06
i have a table A and in that column B. column b has below data. sdkjksdfFDOR:1234; wwerwerrFDOR:12344; how to read the data after FDOR: and upto ';' my output should be 1234 12344 Please help me. Regards. T.prasad
Categories: DBA Blogs

Links for 2017-02-16 [del.icio.us]

Categories: DBA Blogs

Return blob from remote database

Tom Kyte - Thu, 2017-02-16 17:46
Hi, I have a question on how to get Blob from remote database with out using Temporary table and MVIEW. Can you please provide a way to deal with blob.
Categories: DBA Blogs

Data copy from Staging to Application table

Tom Kyte - Thu, 2017-02-16 17:46
Hi Team, Can you pleas eheve alook ate below scenario and suggest any better alternative to achive it I have Staging table - Tb_Staging_0001 Columns - Card_No, Address_1, .. Address_5, Credit_amount, Debit_amount, .... so on.. And Applicati...
Categories: DBA Blogs

How to apply for a job with Oracle

Tom Kyte - Thu, 2017-02-16 17:46
I need a job in oracle. How would I apply?
Categories: DBA Blogs

Commit more aften

Tom Kyte - Thu, 2017-02-16 17:46
Hi ask tom team. what is the effect on Oracle Database if i commit a lot in a loop like following. <code> begin for i in 1..10000000 loop commit; end loop; end; </code>
Categories: DBA Blogs

"Next" Clause when creating a tablespace

Tom Kyte - Thu, 2017-02-16 17:46
Hello, I was recently wondering exactly what the limits of the "next" clause are in a create table space statement. If I create a tablespace with the "Next" clause value of 50mb and maxsize unlimited (with autoextend on), does that mean that the m...
Categories: DBA Blogs

The Cost is too high while selecting the records from the Staging Table named as JDSU_RPR_ORDER_HEADERS_STG

Tom Kyte - Thu, 2017-02-16 17:46
<code>The table JDSU_RPR_ORDER_HEADERS_STG has currently has 584436 recrods. So while i am invoking records from this table.it is giving me cost up to 8,505 SELECT NVL (MAX (TO_NUMBER (stg_row_id)), -1), NVL (MAX (TO_N...
Categories: DBA Blogs

Cannot Create Fast Refreshable On-Commit MV using new SQL JOIN Syntax

Tom Kyte - Thu, 2017-02-16 17:46
The subject says it all, really. Using the old-style comma joins, succeeds. Trying to create a fast refreshable on-commit materialized view using the new SQL JOIN syntax fails. The docs. do not expect such behaviour. Any guidance on the mat...
Categories: DBA Blogs

Optimizer Dynamic Sampling at OPTIMIZER_DYAMIC_SAMPLING level 2

Tom Kyte - Thu, 2017-02-16 17:46
At my Oracle Oracle Standard Edition database, at OPTIMIZER_DYNAMIC_SAMPLING = 2 which is the default, I see Dynamic Sampling is happening for some of the SQLs for which all tables already have already gathered table statistics. Due to this,...
Categories: DBA Blogs

Simple Python for Oracle database tuning example

Bobby Durrett's DBA Blog - Thu, 2017-02-16 16:14

I ran across a stackoverflow question and it gave me an idea for a simpler use of Python to graph some Oracle database performance information. I looked at my PythonDBAGraphs scripts and I’m not sure that it is worth modifying them to try to simplify those scripts since I like what they do. But they may make people think that Python scripts to graph Oracle performance data are difficult to write.  But, I think if someone just wants to put together some graphs using Python, Matplotlib, and cx_Oracle they could do it more simply than I have in my PythonDBAGraphs scripts and it still could be useful.

Here is an example that looks at db file sequential read waits and graphs the number of waits per interval and the average wait time in microseconds:

import cx_Oracle
import matplotlib.pyplot as plt
import matplotlib.dates as mdates

con = cx_Oracle.connect(connect_string)
cur = con.cursor()

(after.total_waits-before.total_waits) "number of waits",
(after.total_waits-before.total_waits) "ave microseconds"
where before.event_name='db file sequential read' and
after.event_name=before.event_name and
after.snap_id=before.snap_id+1 and
after.instance_number=1 and
before.instance_number=after.instance_number and
after.snap_id=sn.snap_id and
after.instance_number=sn.instance_number and
(after.total_waits-before.total_waits) > 0
order by after.snap_id


datetimes = []
numwaits = []
avgmicros = []
for result in cur:

title="db file sequential read waits"

fig = plt.figure(title)
ax = plt.axes()


# Format X axis dates

ax.fmt_xdata = mdates.DateFormatter('%m/%d/%Y %H:%M')
datetimefmt = mdates.DateFormatter("%m/%d/%Y")

# Title and axes labels

plt.xlabel("Date and time")
plt.ylabel("num waits and average wait time")

# Legend

plt.legend(["Number of waits","Average wait time in microseconds"],
loc='upper left')


The graph it produces is usable without a lot of time spent formatting it in a non-standard way:

It is a short 68 line script and you just need matplotlib and cx_Oracle to run it. I’ve tested this with Python 2.


Categories: DBA Blogs

Comment on Index Rebuild: Magic or Voodoo? by Andrew Sayer

Oracle Riddle Blog - Thu, 2017-02-16 12:07

Is this the sort of column that gets inserted into not-null as a flag that some task needs to be run against it? The task would then set this column to null to mark it as processed. If you had a big batch to run, or the task hadn’t run in some time (looking at your 41K buffers, that must be quite a lot of rows!) then the index would have grown – setting the column back to null isn’t going to shrink it again on it’s own but will mean the index can shrink if asked to.

The same sort of thing happens with AQ tables, except the rows get deleted (although that can some time after processing)


Categories: DBA Blogs

Partner Webcast – Oracle Storage Cloud Services: Enabling Enterprises Evolve

In 2013, the digital universe consumed 4.4 zettabytes of data, and this is expected to grow to 44 zettabytes by 2020. What’s more, 32 billion devices—from cars and airplanes to dishwashers...

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

External Tables with multiple datafiles

Tom Kyte - Wed, 2017-02-15 23:26
Tom, I suspect this will be a simple matter to answer, but I haven't found this discussed elsewhere. Given an external table with multiple datafile locations: <code> create table multi_loc_ext ( ... ) organization external ( ...
Categories: DBA Blogs

Creating dynamic stored procedure problem

Tom Kyte - Wed, 2017-02-15 23:26
Here is my first dynamic stored procedure and I have some errors when trying to compile it. Please help. I am new in ORACLE. Thanks a lot Line 12 PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exceptio...
Categories: DBA Blogs

create SQL to fetch all records of table and chunk them to write in flat files.

Tom Kyte - Wed, 2017-02-15 23:26
Hi, I want to create SQL to fetch all records of table and chunk them to write in flat files. Suppose take an example,if table contents 2000 records then write sql to fetch all of records and write 1000 records in one flat file, other 1000 recor...
Categories: DBA Blogs

Measuring stored procedure with AUTOTRACE ON? (followup question)

Tom Kyte - Wed, 2017-02-15 23:26
Hi Team, I posted a question regarding dynamic update SP earlier last week: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533310000346559369 And as a result of that, I have 2 versions of Stored Procedure for each SP i'm m...
Categories: DBA Blogs

pl/sql program to increment/decrement sequences

Tom Kyte - Wed, 2017-02-15 05:06
my question is i am a shopkeeper and i am provide a token number for every customers in database if two and three customers are removed in the database after token number is automatically arrange in sequence.so how to solve this problem in pl/sql
Categories: DBA Blogs

Automatic Memory Management

Tom Kyte - Wed, 2017-02-15 05:06
What is your stance on using AMM on a production system? Several Metalink notes and books recommend its use but that is not always what Oracle support recommends. If there are known issues and AMM is not recommended then I wonder why not have the...
Categories: DBA Blogs

data updaion using bulk collect

Tom Kyte - Wed, 2017-02-15 05:06
Hi Tom , I am bit new to the PLSQL world and trying to write a block that will read clob data and update(only first 4000 byte as we are still using 11g) to a varchar column . The block is ready but when I execute with some good number(close to 70 K) ...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs