DBA Blogs

Index column order and Partitioned table

Tom Kyte - Sun, 2017-02-19 19:06
We have a table with few hundred millions of rows. We perform INSERT, DELETE, SEARCH operation on this table. Column ID is unique and column CODE has very few distinct values (lets say 100 distinct values). So we partition table by LIST CODE column...
Categories: DBA Blogs

Masking bind values with Oracle Transparent Sensitive Data Protection

Tom Kyte - Sun, 2017-02-19 00:46
Hi, I'm trying to use the Oracle Transparent Sensitive Data Protection feature on a database ver. 12.1.0.2.0. The plan is to use the predefined REDACT_AUDIT policy in order to hide bind variables that refer sensitive columns. I've followed the i...
Categories: DBA Blogs

Data to be displayed grouped by start and end date

Tom Kyte - Sun, 2017-02-19 00:46
I have a table t I have a table t Create table t (employee varchar2(10), (job varchar2(5), start_date date, end_date date, FTE int ); with this data in it: insert into t values (1111,1,19-May-2008,18-May-2010,100); insert into t v...
Categories: DBA Blogs

Regarding expressions around columns in SQL

Tom Kyte - Sun, 2017-02-19 00:46
Hi Tom, i havae a query that i given below SELECT 2 tab_type , ID,LVNAME,LV_NAME,APPL_FROM,GRANT_FROM,GRANT_TO, APPLICABLE,LWP,LVSTR,TYPE,EFFECT_ON,AGNST_FROM, HALFPAID,UNPAID,QTRPAID,0,IS_PRORATA,SYSDATE FROM UHO.LVOPEN W...
Categories: DBA Blogs

Shifting / Replacing RAC nodes

Tom Kyte - Sun, 2017-02-19 00:46
Greetings, Hope you are doing well. I wanted to ask a short question/advice for one of our issues. We are replacing some nodes in a production rac cluster with newer ones. Is adding/deleting nodes the best way to go about? Or are there any ...
Categories: DBA Blogs

Updating Materialized Views - Unable to render MATERIALIZED VIEW DDL ...with DBMS_METADATA attempting internal generator

Tom Kyte - Sun, 2017-02-19 00:46
I know it was not efficient, but due to my privileges on an Oracle database I had tablespace on, I had been running an SSIS package to drop and recreate about 20 tables daily. Tables used for reporting queries. It took 2 hours. I was running SSIS f...
Categories: DBA Blogs

Parse a valid WHERE clause syntax using PL/SQL

Tom Kyte - Sun, 2017-02-19 00:46
I have a predicate column in a table which stores a text which must be a valid WHERE clause SEQ PREDICATE --- --------- 1 EMPNO in ( select mgr from emp) AND ( COMISSION is null or SAL > 100 ) 2 RTRIM(LAST_NAME) LIKE '%KYTE%' How to ...
Categories: DBA Blogs

Log File Switches Twice as Often as ARCHIVE_LAG_TARGET

Tom Kyte - Sat, 2017-02-18 06:26
I have an odd issue occurring on my primary pre-production database server. In both my production and pre-production environments, we have the ARCHIVE_LAG_TARGET parameter set to force a log switch at regular intervals. I know this is not a modern-...
Categories: DBA Blogs

UTL_MAIL.SEND issue with Senders email domain

Tom Kyte - Sat, 2017-02-18 06:26
I am not getting any error in the application when I use sender's email domain as @yahoo.com and email is not delivered to recipient. recipient will get email If I use sender's domain as @gmail.com or @abc.com or any other valid domains. So how to tr...
Categories: DBA Blogs

after update count of executed updates

Tom Kyte - Sat, 2017-02-18 06:26
I have a script executed in SQL*PLUS command line that should give out the summ of all real updated rows from the WHERE clause after all update executes simiilar as on execute update command directely in SQL*Plus with "1 row updated". But the fol...
Categories: DBA Blogs

View peformance

Tom Kyte - Sat, 2017-02-18 06:26
I have composite partitioned FACT table that list-range partitioned and it currently houses 1756515660 rows. In order to incorporate dynamic partitioning/sub-partitioning, we want to switch to list-list style of table. Given the large size of the t...
Categories: DBA Blogs

Regexp_Substr negative position

Tom Kyte - Fri, 2017-02-17 12:06
Hi, I would like to use regexp_substr to treat comma as delimiter and search the string backwards. For example: String: a, b, c, d e, f, g, h, j c, c, c I should expect the output from the following query d j c Sele...
Categories: DBA Blogs

Match Recognize Examples (SQL Pattern Matching)

Tom Kyte - Fri, 2017-02-17 12:06
Hi Ask Tom Team, Recently I came across MATCH_RECOGNIZE clause. I checked the Oracle documentation but it's not easy to digest. Looking at OLL webinars, youtube videos and some of Ketih's article on oracle blog helped me understanding a little of i...
Categories: DBA Blogs

Performance on using oracle Table of an object type

Tom Kyte - Fri, 2017-02-17 12:06
Hi I am working on a reporting tool that extracts data from Oracle Stored Procedures base and generates reports using Jasper. Please find below a sample of the procedure written. --Creating Object CREATE OR REPLACE TYPE my_row_type AS OBJECT ...
Categories: DBA Blogs

Apex idle user goes to login

Tom Kyte - Fri, 2017-02-17 12:06
Hello When my users log in and idle like 15 minutes then some users are loged out. How can i prevent that? That user will be never logged out only when they do it. it is very important. I hope you can help me with that. Kristjan
Categories: DBA Blogs

char (10) data type hosting numeric data for natural keys

Tom Kyte - Fri, 2017-02-17 12:06
Hi, Can you please provide some guidance on the possible performance issue one might have in creating a char(10) data type that hosts natural keys? Also, I'm baffled by the following results.. THIS WORKS FINE even char(10) blank pads the ...
Categories: DBA Blogs

Skip scripts based on a variable value during run

Tom Kyte - Fri, 2017-02-17 12:06
Hi, We run the same script package to each environment (production, acceptance test, etc.). We have some of these scripts which shouldn't run e.g. into PRO db. Is there any way to define which script can run in which environment if environment type ...
Categories: DBA Blogs

Bind variables in a View?

Tom Kyte - Fri, 2017-02-17 12:06
It is possible to use bind variables in a view? It is possible to replace the values ??10, 11, 12 and VISOR by BindVariables or how can I optimize this view? CREATE OR REPLACE VIEW VIEW1 AS SELECT a.column1, a.column2 FROM TABLA1 a WHERE a...
Categories: DBA Blogs

Tuning

Tom Kyte - Fri, 2017-02-17 12:06
Hi there! I want to know if it possible to get information about queries in database (like v$active_session_history contains) without diagnostic and tuning packs? This datadictionary view v$active_session_history consists of x$kewash and x$ash fi...
Categories: DBA Blogs

How RowProcessed works and updated in GV$SQL

Tom Kyte - Fri, 2017-02-17 12:06
How row_processed,disk read and buffer gets actually works while executing any query. I came across two scenarios while executing two different queries.(My sql arrayfetch size is 50) 1. Disk read and buffergets increased till the entire executio...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs