Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 3 hours 11 min ago

'out blob' vs 'out nocopy blob'

Fri, 2016-12-02 09:26
Say I have a table: create table emp( empid number(5), name varchar2(30), photo blob ); create or replace procedure get_photo(p_empid in number, p_name out varchar2, p_photo OUT BLOB) as begin select name, photo into p_name, p_ph...
Categories: DBA Blogs

list agg function - Custom defined

Fri, 2016-12-02 09:26
Hello, I am fairly new to Oracle and quite frankly a noob at writing code. I am writing a query to pull in data. One of the fields needs to be concatenated. However there a multiple duplicate records in the field resulting in many rows of data....
Categories: DBA Blogs

DBMS_JOB deprecated in 12c Release 2 in favor of DBMS_SCHEDULER

Fri, 2016-12-02 09:26
The 12c2 database upgrade guide (http://docs.oracle.com/database/122/UPGRD/deprecated-features-oracle-database-12c-r2.htm#UPGRD-GUID-C34B4093-97BE-4237-9BE4-F45450F23BA3) identifies DBMS_JOB as deprecated and notes that it may be desupported in a fut...
Categories: DBA Blogs

Database performance problem

Fri, 2016-12-02 09:26
Hi team, I want to ask you some questions. 1. My office using oracle database. We have 9 schemas in one database. Each schemas having branches. Sometimes the database application running heavy so much. When I see the CPU usage of server database i...
Categories: DBA Blogs

Compare two tables

Thu, 2016-12-01 15:06
I want to compare two tables which have exactly same columns. Tables contains 200 columns each and have million of records in them. My query is I want to compare these two tables and find out which columns have different data and then I need to po...
Categories: DBA Blogs

Converting a GUID to specific number of characters.

Thu, 2016-12-01 15:06
Hi Experts, I have major experience in SQL Server and I am quite new to Oracle and trying to implement something . I need a help from you guys. I have converted a varchar value into a GUID which looks like this 7171D6557B34C0BF47F166092EE93E06...
Categories: DBA Blogs

Bad Performance of the PLSQL block when it is being executed by Execute Immediate.

Thu, 2016-12-01 15:06
I have 50 tables, each with millions of data rows in the Staging Table, that need to be validate by Business Rules. each record and each field have n number of validation, Example Validation Description- 1. (STAGING_FACT_ORG_ID IS NULL) To va...
Categories: DBA Blogs

About IN keyword in Oracle

Thu, 2016-12-01 15:06
Dear Tom, Can i ask question. If i have a array of varchar2 with name is arr_val in oracle. How can i use "where in arr_val" instead of "where in (select column_name from table_name)".And if it can be done. Can y explain which way is better? Thank...
Categories: DBA Blogs

Truncate Load gather stats

Thu, 2016-12-01 15:06
Hi, I have a DW activity. <b>Steps include</b>. Drop index on Table A Truncate Table A. Load table A with millions of records. Create indexes back on table A. Drop index on Table B Truncate Table B. Load table B with millions of records....
Categories: DBA Blogs

Partitioned Sequence

Thu, 2016-12-01 15:06
Is sequence partitioning available in Oracle Database version 12.1.0.2.0 ? When I query dba_sequences, I see the column partition_count but I was not able to find any documentation related to it, is this feature available for production use ?
Categories: DBA Blogs

Y2038 issue

Wed, 2016-11-30 20:46
Hello AskTom team, Would this be an issue with Oracle? https://en.wikipedia.org/wiki/Year_2038_problem Thanks!
Categories: DBA Blogs

Archivelog backups taking too long

Wed, 2016-11-30 20:46
I am expriencing slowness in archivelog backups. In the last two days we have seen long running archive log backups despite the archive log generation being very low (less than 1gb every day). On Nov 29th it ran for 11 hours (4 am - 3 pm). Even lvl 0...
Categories: DBA Blogs

Compare varchar column with number

Wed, 2016-11-30 20:46
Hi Team, I need to update values for a column in my table to null, which are not numeric & greater than 999. Here is sample table - create table checknum ( col1 varchar2(10), col2 varchar2(10) ); Insert into CHECKSUM (COL1,COL2) valu...
Categories: DBA Blogs

PL SQL Functions that work with Read-Only Access

Wed, 2016-11-30 20:46
Tom! I've enjoyed your point of view and the clarity of your interactions for a couple of years now. I?ve been using SQL Developer off and on for years but I just recently started running into more complicated needs and I?m hoping you can point...
Categories: DBA Blogs

Global Temporary table use to avoid execution of same SQL's twice

Wed, 2016-11-30 20:46
Hi, I have one procedure which has two OUT ref cursors. First REF CURSOR returns below SQL output - SELECT * from tab1 , tab2 , tab3 , tab4 , tab5 WHERE <<certain join conditions>> AND value IN (SELECT VALUE from...
Categories: DBA Blogs

Oracle Subquery Bug?

Wed, 2016-11-30 20:46
Hi all, per random I came to this problem. My PL/SQL-Procedure was parsed without any ERROR, whereby the column "tb_id" itself did not exist in "suh_tb_test_1". (If I use an alias in the subquery then the ERROR is thrown!) Here the (strong s...
Categories: DBA Blogs

Database Resource Manager, v$active_session_history p1text=location p2text=consumer group id

Wed, 2016-11-30 02:46
Hi Tom, Wish you all the best. 1] I have a 12c database 2] Not created as a CDB SQL> select cdb from v$database; CDB --- NO 3] No resource manager is configured SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN NAME ...
Categories: DBA Blogs

Combining WITH clauses

Wed, 2016-11-30 02:46
I've tried to combine an SQL defined in a WITH clause with an inline PL/SQL in 12c. <code>with function is_number(p_str varchar2) return number is l_number number; begin l_number := to_number(p_str); return l_number; exception when v...
Categories: DBA Blogs

VPD and DBMS_SQLTUNE

Wed, 2016-11-30 02:46
I have several different applications using multiple schemas in a single database which used VPD and an after logon trigger to establish initial VPD driving context variables. A second db call, secmgr.my_appl_env spec listed below, is required by al...
Categories: DBA Blogs

Fine-grained Access Control (FGAC) vs. Row Level Security (RLS) vs. Virtual Private Database (VPD) vs. Oracle Label Security (OLS)

Wed, 2016-11-30 02:46
Good Evening, Throughout my career, I heard about Fine-grained Access Control (FGAC), Row Level Security (RLS), Virtual Private/Policy Database (VPD) and Oracle Label Security (OLS). These all sound very similar. I've only heard of OLS since it ...
Categories: DBA Blogs

Pages