Tom Kyte

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

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

I/O of Database

Wed, 2016-11-30 02:46
Hi, I am reading a lot on AWR reports, and also posts about this subject. But everyone say something different. My client asked me for: "I want to know the I/O of the database" Is there a metric, so I can execute a script on a time interva...
Categories: DBA Blogs

Imcomplete CSV File

Wed, 2016-11-30 02:46
Hi, Iam exporting a simple select command into a csv file. but the resulting csv file is incomplete ie. amon 44 columns it is displaying only 3 columns. please find my code below, set headsep on set term off set newpage 0 set space 0...
Categories: DBA Blogs

How to check in apex, if there is new data

Wed, 2016-11-30 02:46
Hello Tom, I'm a bit new to apex and I wonder if there is a way to check in apex, if new data was inserted in my DATA table. I want to fire a dynamic action that refreshes the region only when new data has arrived. For now, this works for me: ...
Categories: DBA Blogs

Unable to connect to database using sql developer

Wed, 2016-11-30 02:46
Hi , I am unable to connect to oracle database using SQL DEVELOPER but I am able to connect through windows cmd command. It gives me error i/o error network adapter could not establish connection. vendor code 17002. Please let me know what are the...
Categories: DBA Blogs

LOB data type comparisons

Wed, 2016-11-30 02:46
Hi Team We have migrated data from SQL server to Oracle,As part of data validation we want to compare the data from both databases. We have some tools which compare data but that tool will not compare lob columns. Please note we had migrated dat...
Categories: DBA Blogs

Insert All with sequence - feature or featurette?

Tue, 2016-11-29 08:26
I posted this problem elsewhere and we had a bit of a discussion. However I would like to know whether what we see is a feature (i.e. it can be relied upon to be somewhat consistent in future releases) or whether it is a "featurette" - i.e. unintende...
Categories: DBA Blogs

Generating excel report with multiple tabs from Oracle APEX

Tue, 2016-11-29 08:26
Hi, I'm new to Oracle Application Express and currently working on an APEX application. We have several reports which can be downloaded as csv files in various modules of the application. What I need is to have a master report(excel file with ...
Categories: DBA Blogs

Decoding from base64

Tue, 2016-11-29 08:26
Hi, I'm in need to encode and decode big xml, however when i'm decoding from base64 after some iterations it fails to decode and i get symbols like this, "S??FS???S??FS???S?". Here's the script to reproduce the problem i'm facing: <code>declare...
Categories: DBA Blogs

Drop a partition of reference partitioned tables with the UPDATE INDEXES clause

Tue, 2016-11-29 08:26
I have a set of 3 tables that are reference partitioned (a parent and two child tables). The parent table is range partitioned by date (TST_ADM_DTE) and contains 1,152 partitions, ranging in value from 3/1/14 to 12/31/16. The first seven (7) partitio...
Categories: DBA Blogs

Pages