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

Fast Refresh on Materialized View seems not working with OUTER JOIN in some conditions

Tom Kyte - Thu, 2017-05-04 20:26
Hi guys, it's a pleasure to write here for the first time. I'll try to explain my problem that seems simple. I create 2 tables, TableFather and TableChild, linked by foreign key. I create Materialized View Log for both. I create Materialized ...
Categories: DBA Blogs

What’s new in Oracle Application Container Cloud Service Release 17.2.1

Oracle Application Container Cloud Service instances are upgraded to use new features as they become available automatically to improve your ACCS experience. In this blog post we’d be looking at the...

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

unified_audit performance

Tom Kyte - Thu, 2017-05-04 02:06
Hi, I am currently writing some function that can output unified_audit_trail table and DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL and DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ... so that on next query the table is small and has great performance. Following...
Categories: DBA Blogs

how the data stored in partition table

Tom Kyte - Thu, 2017-05-04 02:06
Hi Tom, I have a partition table, Range partition by date, list sub-partition by region. my question is: Is it possible that same day but different regions' data can be stored at same data block. when running complicated query (the query join a...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs