DBA Blogs

Date range grouping for same price and stop sale option

Tom Kyte - Sun, 2016-10-30 14:26
Hi Tom, Please disregard my previous ticket as I am not able to edit and the query requirement has changed. Thanks for the link by I am struggling a bit. I have a table of daily rates for a service that contains a price and an Y/N field to sto...
Categories: DBA Blogs

Performance issue with the Query

Tom Kyte - Sat, 2016-10-29 20:06
Dear Tom, We have recently Upgraded form 11g to 12c. We have a small application(it is exactly same as SQL Developer) through which we can select a particular table from the list and we can view the data. All tables has less than 10...
Categories: DBA Blogs

Blogs to read in the Oracle DBA/Developer world

RDBMS Insight - Sat, 2016-10-29 16:23

:Earlier this month, I conducted a totally unscientific survey on Twitter, asking where people got their Oracle news from. Twitter and the NoCOUG Journal were two popular sources, along with a wide range of blogs. Here are some of the blogs that the Oracle DBA & Dev superstars in my Twitter like to read:

BTW, I notice that David Fitzjarrell’s blog was named one of the top 50 SQL blogs in 2016 by Ben’s DatabaseStar blog, itself a good blog with a strong focus on Oracle SQL for developers. I recommend checking out Ben’s list for more SQL blogs!

I also learned about a couple of terrific Oracle blog aggregators:

I’ve got a couple of my own to add to the list.

I also follow the articles on orafaq.com:
http://www.orafaq.com/articles

And there are some official Oracle blogs I like to keep my eye on:

And of course, there’s Ask Tom, which has an RSS feed for newest questions.

I’m experimenting with Feedly as my new RSS reader. Do you have a favorite Oracle blog, magazine or RSS reader? Let me know in the comments!

Categories: DBA Blogs

Identifying SQL Queries that are consuming Shared Pool Memory

Tom Kyte - Sat, 2016-10-29 01:46
Hello Tom, Is there a way to identify SQL queries that are consuming the majority of the shared pool for e.g Session #1 is using 56 kb session #2 is 10mb I would want to identify each query and the memory value it's consuming. Thanks in advan...
Categories: DBA Blogs

Efficient method in Oracle to continuously poll data from one database and move to another

Tom Kyte - Sat, 2016-10-29 01:46
Hi, I have a scenario where I have to continuously( in every 1 hour) poll updated data from tables of one database and move to another database. What do you think is the standard approach. (anything similar to DTS in Sql server in Oracle) or would...
Categories: DBA Blogs

getting error 0382

Tom Kyte - Sat, 2016-10-29 01:46
I have made stored procedure and executing through trigger.So my requirements is like that if we create a new issue first statement should execute and if we return the same issue number then 2 nd statement should execute. I have make like this but...
Categories: DBA Blogs

Pagination query and optimization

Tom Kyte - Sat, 2016-10-29 01:46
Hi Tom, I've been reading through a few posts regarding pagination. I'm using the following query template in my application, to implement a "previous", "next" feature to page through a search resultset: (J2EE front-end calls a stored proc returni...
Categories: DBA Blogs

Flashback Database Logs can exceed the Retention Target

Hemant K Chitale - Fri, 2016-10-28 18:58
The documentation on the Flashback Retention Target in 11.2 and 12.1 states that this parameter specifies an upper limit on how far the database may be flashed back.

However, if the FRA (db_recovery_file_dest_size) is actually large enough, Oracle may retain flashback logs for a much longer duration.

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select sysdate, l.* from v$flashback_database_log l;

SYSDATE OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET
------------------ -------------------- ------------------ ----------------
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------- ------------------------
29-OCT-16 07:42:44 6968261 28-OCT-16 22:35:50 180
157286400 86467584


SQL>
SQL> show parameter flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 180
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL> flashback database to timestamp trunc(sysdate);

Flashback complete.

SQL>


Thus, it is useful to check the V$FLASHBACK_DATABASE_LOG, V$FLASHBACK_DATABASE_LOGFILE and V$FLASHBACK_DATABASE_STAT and V$FLASH_RECOVERY_AREA_USAGE views from time to time.

See Oracle Support Doc# 1194013.1 for a discrepancy between the first two views.

Note : If you have Standby database configured, also see John Hallas's blog post.
.
.
.

Categories: DBA Blogs

Automatic conversion of cursor for loop into set based operation

Tom Kyte - Fri, 2016-10-28 07:26
Hi, We all know that doing things row-by-row ("Cursor For Loops") is a bad idea rather than a set-based approach, however I have read in a number of places that in certain circumstances Oracle will convert a cursor for loop into a set-based operatio...
Categories: DBA Blogs

Spanish trasnlation for amount in words

Tom Kyte - Fri, 2016-10-28 07:26
Hi Tom, I have a requirement by a client that the amount in number currently prnting on checks needs to be converted to spanish, ex one hundred us dollars should be printed as cien dolares americanos Could you please suggest how can w...
Categories: DBA Blogs

How can i incorporate a text to the existing values of a column

Tom Kyte - Fri, 2016-10-28 07:26
Hi, The query is as below, I have a table in which i have the column named as V_QTN_NAME, here in this column the values are of different in each row. Now i am trying to differentiate the values by incorporating the text 'OLD'to the existing va...
Categories: DBA Blogs

Instance and SGA relation

Tom Kyte - Fri, 2016-10-28 07:26
Hi Tom, I would like to know how many SGA Created inside instance when data base starts? Here instance mean instance only i.e to which we mount database. Also please let me know if you answer the questions posted in reviews,perhaps i wil get ...
Categories: DBA Blogs

Insert statement getting locked in database.

Tom Kyte - Fri, 2016-10-28 07:26
Hello Sir, I have a very simple question and I have searched all around but could not find it, can insert statements be locked by other DML sessions ?? And how ? Reference :- I have a process that updates/inserts multiple records into one table ...
Categories: DBA Blogs

RMAN Incremental

Tom Kyte - Fri, 2016-10-28 07:26
Hello Team, We have oracle 12c standard running on centos. Basic backup strategy is developed without using asm/ catalog database as suggested in requirements. Backup Plan is as below: Requirement says full backup has to run every night with ...
Categories: DBA Blogs

Users and Roles

Tom Kyte - Fri, 2016-10-28 07:26
Tom, in your apps 1)Do you normally have an Oracle user for each application user...or a table you create of the users for your app. I was creating real Oracle users w/forms and now use my own tables of users for web enabled apps...but I noticed t...
Categories: DBA Blogs

Links for 2016-10-27 [del.icio.us]

Categories: DBA Blogs

redo 12c and temporary table

Tom Kyte - Thu, 2016-10-27 13:06
Hello there, I am quite curious why redo is still generated when I set temp_undo_enabled=true, having parameter compatible 12.1.0.2.0. I expected redo to be close to 0. The same result I got years ago, when I first introduced with 12.1.0.1 I ...
Categories: DBA Blogs

Left join with filter condition is not working as supposed

Tom Kyte - Thu, 2016-10-27 13:06
Hello, One of my colleagues asked a question like "when I insert query result to table, I found records that meet my criteria, but when I put the same filter on the same query without inserting rows to table, I did not get those records". After li...
Categories: DBA Blogs

exception handling in select a query not pl/sql block

Tom Kyte - Thu, 2016-10-27 13:06
Query; update accounts set product_code=( select product from products); from the query the sub query return multiple rows. So i want to update it with null value. so how could i do this exception handling from this query??
Categories: DBA Blogs

Script for increase sequence by table max pk value

Tom Kyte - Thu, 2016-10-27 13:06
We have sequences created by table name format as seq_<table_name>. Somehow sequence is lower than pl value and need to fix by increasing sequence. Here I just simulate the situation as example below: </> ORACLE@hist > create table t1 (a_id num...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs