DBA Blogs

Partner Webcast – Oracle Application Development Cloud Platform: Proven Way for Supporting SDLC

As you build cloud first solutions, Oracle Cloud provides a platform to develop and deploy nearly any type of application, including enterprise apps, lightweight container apps, web apps, mobile...

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

Issue Frequent COMMIT Statements

Tom Kyte - Mon, 2017-05-08 16:06
Tom : I' ve recently read an article about Performance on Dbasupport.com an i couldn't believe what i was reading about commits I usually read your answers on this site and I' ve read your book as well and you always suggest to not commit fequently....
Categories: DBA Blogs

Performance tuning of delete from many tables

Tom Kyte - Mon, 2017-05-08 16:06
Hello, These are my tables: <code> -- This is the main container CREATE TABLE CONTAINER( Id VARCHAR2(18 CHAR) NOT NULL, ContainerName VARCHAR2(100 CHAR) NOT NULL, ContainerDescription VARCHAR2(500 CHAR) NULL ) NOCACHE PARAL...
Categories: DBA Blogs

12cR1 RAC Posts -- 10 : Video on Database Startup

Hemant K Chitale - Mon, 2017-05-08 09:38
I've created a short video on manually starting up a RAC database that has a PDB with two custom services.  If a database is shutdown before the cluster is shutdown, the cluster startup does not automatically startup the database instance, so I demonstrate a manual startup.

See the video at:  https://youtu.be/saFvo9QhYSI


.
.
.
Categories: DBA Blogs

VPD Policy Type and Binding

Tom Kyte - Sun, 2017-05-07 03:26
I have a couple of VPD issues that I'm trying to ensure I fully understand based on reading Chapter 7 of Oracle Database 12c Security. I've seen both examples for well respected Oracle authors. Regarding bind variable reference of the SYS_CONTEXT fun...
Categories: DBA Blogs

Error when trying to Import Schema Using IMP

Tom Kyte - Sat, 2017-05-06 09:06
Hi Tom, I am trying to import a schema that was exported using old export method (EXP), when importing the schema after some time i get below errors IMP-00019 row rejected due to ORACLE error 1400 IMP-0003 ORACLE error 1400 encountered OR...
Categories: DBA Blogs

Partitions in 11g

Tom Kyte - Sat, 2017-05-06 09:06
Hi team, Could you please help to get answer of One of my interview question - Consider a non-partition table x having date column with 1000 rows. How can we insert future rows i.e. from 1001 .. onward into partition (without modifying table st...
Categories: DBA Blogs

export error

Tom Kyte - Sat, 2017-05-06 09:06
Hi team, I have exported schema with expdp and import into to the development database all are fine but 5 tables are missing i checked on production with SELECT COUNT(*) FROM CPG_PROD.MDRT_20315$; -->>It shows 59 rows but when i try to export that...
Categories: DBA Blogs

wait event ' buffer busy wait' on sys.aud$

Tom Kyte - Sat, 2017-05-06 09:06
Hi, In our prod database we could see more buffer busy wait events on query "insert on sys.aud$" table? Can you explain why buffer busy wait occurred on sys.aud$ table and how to avoid ?
Categories: DBA Blogs

How to identify the total number of distinct blocks (LIO) read by a particular SQL? Is it possible at all?

Tom Kyte - Sat, 2017-05-06 09:06
Hi, There are various ways to easily identify the LIO for SQL execution as a primary measure for performance analysis. As many of the index and table blocks are usually read multiple times over and over again for SQL execution, is there a way t...
Categories: DBA Blogs

char vs varchar2 when end result is filxed format value

Tom Kyte - Sat, 2017-05-06 09:06
We have a temporary table with about 500 columns that is used to generate a fixed format file (.txt). If we use all char fields, we can just build the data as field1 || field2 || field3 ... field500 If we use varchar2 we have to rpad each fiel...
Categories: DBA Blogs

deadlock question

Tom Kyte - Fri, 2017-05-05 14:46
Hi, Forgive my pool english,I try my best to make my question clear. I think deadlock only accured in a tansaction ,etc, session 1 session 2 start transaction ...
Categories: DBA Blogs

how to concate rows into select

Tom Kyte - Fri, 2017-05-05 14:46
Hi Tom, <code>create table test3 (name varchar2(10), sub varchar2(10), marks number ); insert into test3 values('joe','maths',90); insert into test3(sub,marks) values('social',80); insert into test3(sub,marks) values('science',70); ...
Categories: DBA Blogs

Show rows where the running total is zero and all preceding rows

Tom Kyte - Fri, 2017-05-05 14:46
Hi Team, Data Setup: <code>create table test_item as with t1 as ( select 1 category, 'abc' Item_name,100 item_volume,12.3 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual union select 1,'abc' ,-100 ,1...
Categories: DBA Blogs

Tables not visible based on Role

Tom Kyte - Fri, 2017-05-05 14:46
Hi In my Project, we have created two users 1) admin 2) Report One Role: Reporting Role Reporting role is granted to Report user. Now all the tables are created using admin user and select access is granted to reporting Role. N...
Categories: DBA Blogs

Audit

Tom Kyte - Fri, 2017-05-05 14:46
Hi Team, I have a requirement to generate audit report on a parent-child table. We are capturing changes to the tables using trigger. But when I am using the audit tables to reconstruct the history I am getting phantom rows which never existed in...
Categories: DBA Blogs

Find agents with no transactions for 5 or more continuous days

Tom Kyte - Thu, 2017-05-04 20:26
Table1: TRANSACTION FIELDS : AGENT_ID, TRNX_AMT, INSERTED_ON <code>create table TRANSACTION (AGENT_ID varchar2(10), TRN_AMT number(4,2),INSERTED_ON date);</code> Table2: AGENT_DETAILS FIELDS: AGNET_ID,NAME,STATUS <code>create table AGENT_DE...
Categories: DBA Blogs

Selecting rows that have specific value

Tom Kyte - Thu, 2017-05-04 20:26
Hello, I am asking for help due to time constraint. With the example table given below, the requirement is to select Asset that has Src of only 10. For example, the output that I would expect from this table below would be Assets = 2000, 4000 Ass...
Categories: DBA Blogs

Combining multiple rows with a "priority"

Tom Kyte - Thu, 2017-05-04 20:26
I have a table: <code>create table mytab (ROLE_NAME VARCHAR2(30), MENU_TAB VARCHAR2(20), colA VARCHAR2(1), colB VARCHAR2(1), colC VARCHAR2(1));</code> I populate: <code>INSERT INTO MYTAB VALUES('TECH','MAIN','A','B', NULL); INSERT INTO MYTA...
Categories: DBA Blogs

DBMS_METADATA.GET_DDL - Output is getting Truncated

Tom Kyte - Thu, 2017-05-04 20:26
Hi Team, I am trying to get table & its dependent ddl, but it its not giving completed output - <code>SQL> set linesize 1000 SQL> set pages 1000 SQL> set pages 1000 SQL> set lines 1000 SQL> select DBMS_METADATA.GET_DDL('TABLE','TB_TEST') f...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs