Tom Kyte

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

Can you shed some light on this ora error [session idle bit]

2 hours 36 min ago
SYS@XYZ> select sum(ksmchsiz) ||' bytes' "ToSHRPOOLMem" from x$ksmsp; ^C ^C select sum(ksmchsiz) ||' bytes' "ToSHRPOOLMem" from x$ksmsp * ERROR at line 1: ORA-00603: ORACLE server session ...
Categories: DBA Blogs

How to restore a packages from rman backups

2 hours 36 min ago
Hello, Is it possible to restore packages from rman backups? I know the export method that can do it, but I want to know if extended rman can do such a thing?
Categories: DBA Blogs

Encryption

2 hours 36 min ago
HI Tom - facing this issue of incorrect string sizing while decrypting.Usage of trim doesnt solve the issue. Below are the codes. 1.CREATE OR REPLACE FUNCTION CRYPT( P_STR IN VARCHAR2 ) RETURN VARCHAR2 AS ...
Categories: DBA Blogs

Oracle database backup

Wed, 2018-02-21 15:46
Hi Developers, I am using Oracle 10g. I need to take backup of my database. I can take a back-up of tables, triggers etc using sql developers' Database Backup option but there are multiple users created in that database. Can you please support ...
Categories: DBA Blogs

How do you purge stdout files generated by DBMS_SCHEDULER jobs?

Wed, 2018-02-21 15:46
When running scheduler jobs, logging is provided in USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS. And stdout is provided in $ORACLE_HOME/scheduler/log. The database log tables are purged either by default 30 days (log_history attribute)....
Categories: DBA Blogs

V$SQL history

Wed, 2018-02-21 15:46
How many records/entry are there in v$sql,v$ession. and how they flush like Weekly or Space pressure. Thanks
Categories: DBA Blogs

Dynamic SQL in regular SQL queries

Wed, 2018-02-21 15:46
Hi, pardon me for asking this question (I know I can do this with the help of a PL/SQL function) but would like to ask just in case. I'm wondering if this doable in regular SQL statement without using a function? I'm trying to see if I can write a ...
Categories: DBA Blogs

Adding hash partitions and spreading data across

Wed, 2018-02-21 15:46
Hi, I have a table with a certain number of range partitions and for each partitions I have eight hash subpartitions. Is there a way to increase the subpartitions number to ten and distributing evenly the number of rows? I have tried "alter tabl...
Categories: DBA Blogs

Bug when using 1 > 0 at "case when" clause

Wed, 2018-02-21 15:46
Hello, guys! Recently, I've found a peculiar situation when building a SQL query. The purpose was add a "where" clause using a "case" statement that was intented to verify if determined condition was greater than zero. I've reproduced using a "wit...
Categories: DBA Blogs

Difference between explain and execute plan and actual execute plan

Wed, 2018-02-21 15:46
Hi, I have often got questions around explain plan and execute plan. As per my knowledge, explain plan gives you the execute plan of the query. But I have also read that Execute plan is the plan which Oracle Optimizer intends to use for the query and...
Categories: DBA Blogs

Strange dependency in user_dependency: view depends on unreferenced function

Tue, 2018-02-20 21:26
Dear Team, I will try to simplify the scenario we have, using a simple test case: <code> SQL> create table test_20 ( a number) 2 / Table created. SQL> SQL> create or replace function test_function (p_1 in number) 2 return num...
Categories: DBA Blogs

Report for employee attendance

Tue, 2018-02-20 21:26
I am sorry for asking this seemingly trivial question, but I have been struggling with it for some time, my deadline is approaching and I can't find any answers for it. I have 3 tables: Calendar table: <code>CREATE TABLE "CJ_CAL" ( "CAL_ID...
Categories: DBA Blogs

Using SELECT * combined with WITH-CLAUSE - Bad Practice? View gets compiled with static columns list

Tue, 2018-02-20 21:26
Hey guys, I have a question regarding clean SQL Code / Bad Practice around the use of wildcards in SELECT-Statements. In the provided example I have a base-query with a huge list of columns selected and two (or more) sources I need to have combin...
Categories: DBA Blogs

ORA-01502 with hardcoded index hints

Tue, 2018-02-20 03:06
We have EBS 12.1.3, which has several indexes, that we disable for bulk loading at night and rebuild them once done. While they are disabled some queries get ORA-01502 because of hard coded index hint on these unusable indexes, although we have skip...
Categories: DBA Blogs

SUBPARTITION ELIMINATION

Tue, 2018-02-20 03:06
Table with 10 years worth of data Has two key date columns. Queries are always by one of the dates. <b>But never both</b>. So how to get partition elimination to work for both when really can only partition by 1 column. **Thought was - since ...
Categories: DBA Blogs

Issue with DBMS_CRYPTO Encryption method for larger dataset

Tue, 2018-02-20 03:06
Table Structure <code> CREATE TABLE TEST_PAYLOAD ( CNTNT_PAYLOAD_OID NUMBER(20) NOT NULL, CNTNT_PAYLOAD CLOB, BLOB_CNTNT_PAYLOAD BLOB, ENCRYPT_BLOB BLOB, DECRYPT_BLOB BLOB, CLOB_CNT_PAYLO...
Categories: DBA Blogs

data encryption package

Tue, 2018-02-20 03:06
Tom: Would you please provide some examples for how to use dbms_obfuscation_toolkit ? -Thx -Reji
Categories: DBA Blogs

Primary and foreign key in temporal tables

Tue, 2018-02-20 03:06
I have product and supplier table. Both tables have start and end date columns (temporal validity) Relation between product and supplier table is managed via a link table product_supplier_lnk table. I need to know how to manage primary and foreign...
Categories: DBA Blogs

When case counting statement query issue

Tue, 2018-02-20 03:06
hello Oracle Team I have this test query right now and I have this table. <code>id | cost_center |hardware beni | aaaa |PC123 beni | aaaa |PC234 ari | bbbb |PC456 cari ...
Categories: DBA Blogs

Select query on two calculated fields, one depends on other calculated field

Mon, 2018-02-19 08:46
SELECT A, B, A+B AS C, CASE C (I don't want to use again A+B here its lengthy query) ------ ----- ------ END As D FROM TABLE So here C is a calculated field, I want use result of C to calculate other field D. How can I do this without ha...
Categories: DBA Blogs

Pages