Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 17 hours 22 sec ago

Special character constant package after deployment

Tue, 2020-07-07 16:06
Hi TOM i have a constant package where i define a variable constant whose value is '<b>#</b>' (pound), Now, running the package script in dev environment, package created with no errors and did not show any special character in constant variable, but running same package script in UT environment package created with no errors but constant value has '<b>A#</b>'. how can i resolve this issues ? came to know its different character set issue thanks in advance. Code: create or replace package pk_constants is lv_pound constant char(1) :='#'; -- for germany and europe countries ...... ..... end pk_constants; /
Categories: DBA Blogs

Need information on tuning a join

Tue, 2020-07-07 16:06
Hi Tom, I need to know how the where clause is effecting my query. I have a table ABC where it has batch_id. It has 1L rows. Its daily truncated table. so it will have only one batch_id. table1 = ABC table2 = XYZ select * from ABC t1, XYZ t2 where batch_id = 111 and t1.col1 = t2.col2 the above query is taking 20+ minutes to retrieve data. the below query is taking hardly 6s. select * from ABC t1, XYZ t2 where t1.col1 = t2.col2; Just want it understand why its taking time with just batch_id in where clause.
Categories: DBA Blogs

Hidden paramater: No_Invalidate

Tue, 2020-07-07 16:06
Hi Tom, from couple of months some queries in production environment are intermittently producing 2 hash plan values. Obviously one is better and other is bad. We run stats gather Job daily in the morning. Have check many other platforms and understood that there are many reasons for this behaviour. Some of them reasons for above behaviour, that I have check are below 1. Parsing a SQL statement with bind variable defined as wrong type. 2. Not running stats gather Job daily. above two reasons are not valid for my case. Other than this there are some more reasons that I wanted to ask you. a. Invalidation of cursors. I understand that, this is handled by parameter 'NO_INVALIDATE'. This is set to AUTO_INVALIDATE for me. So even though my stats gather Job has produced a bad hash plan value, this will not be effective immediately. b. Adaptive cursor sharing. How to make sure that because of this feature my SQL is producing multiple Hash plan value? Could you please help me with above understanding and suggest me for point a and b. Thanks a lot.
Categories: DBA Blogs

SQL TO_CHAR returns differently between 2 Servers

Tue, 2020-07-07 16:06
Hello, I have two server with version 12.2.0.1.0. When I run the same SQL on each I don't have the same result on dates when I used then with TO_CHAR... SQLPLUS: <code>SELECT CONTRACTDATE, TO_CHAR(CONTRACTDATE,'YYYY/MM/DD') FROM MYTABLES WHERE CONTRACTNO=101010;</code> COLUMN CONTRACTDATE is a DATE. SERVER1 CONTRACTD TO_CHAR(CO --------- ---------- 05-DEC-19 2019/12/05 SERVER2 CONTRACTD TO_CHAR(CO --------- ---------- 05-DEC-19 <b>00</b>19/12/05 As you can see on the second server it use <b>0019</b> for the year instead of the expected <b>2019</b> Any idea why I have such behaviour? Thank you PS: I have checked the NLS and they are identical. <code>SELECT DB.PARAMETER, DB.VALUE "DATABASE", I.VALUE "INSTANCE", S.VALUE "SESSION" FROM NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S WHERE DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+) ORDER BY 1;</code>
Categories: DBA Blogs

Redo Log query

Tue, 2020-07-07 16:06
I understand instead of log generation rate , we should consider amount of redo generated. My question is - How can we identify any database bottleneck using amount of redo? Is there any way to compare the amount of redo generation between two days or months probably and see if there is need of tuning or re-sizing?
Categories: DBA Blogs

COLLECTION ITERATOR PICKLER FETCH

Tue, 2020-07-07 16:06
Hi Tom, I am using DBMS_XPLAN.DISPLAY to display the execution plan of a query. After using EXPLAIN PLAN SET STATEMENT ID .... I am giving the following query: <code>SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','SAM','ALL')); Execution Plan ---------------------------------------------------------- Plan hash value: 3013799171 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 32744 | 65488 | 49 (3)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | | | ---------------------------------------------------------------------------------------------</code> can you please help me to know how to avoid this "COLLECTION ITERATOR PICKLER FETCH" and get the proper explain plan? Thank you very much Sarayu K.S.
Categories: DBA Blogs

PL\SQL to GetDDL for all user account except system account using cursor and loop

Tue, 2020-07-07 16:06
I am not sure what I am making wrong with this statement, <code>Declare cursor cur_dba_users IS select username from dba_users where username not in ('ANONYMOUS','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','BI','CTXSYS','DBSNMP','DIP','DVF','DVSYS','EXFSYS','FLOWS_FILES','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','HR','IX','LBACSYS','MDDATA','MDSYS','OE','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSBACKUP','SYSDG','SYSKM','SYSTEM','WMSYS','XDB','SYSMAN','RMAN','RMAN_BACKUP','OWBSYS','OWBSYS_AUDIT','APEX_030200','MGMT_VIEW','OJVMSYS'); Begin For cur_username in cur_dba_users Loop SELECT dbms_metadata.get_ddl('USER',cur_username) FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',cur_username) from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',cur_username) from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',cur_username) from dual; END LOOP; CLOSE cur_dba_users; End; Error report - ORA-06550: line 6, column 39: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 6, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 55: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 8, column 53: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 8, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 9, column 55: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 9, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:</code>
Categories: DBA Blogs

Not able to connect 18c XE from Delphi 2010

Sun, 2020-07-05 09:06
Dear sir Thank you for your support We are using 18c XE database and it is not able to connect from Delphi application. During it connection it says SQLORA8.DLL failure. These drivers are being used by Delphi for Oracle DB connection and post...
Categories: DBA Blogs

Database with TDE tablespace encryption and RMAN encryption turned off (Doc ID 819167.1)

Sun, 2020-07-05 09:06
Hi Team, I would like to understand the scenario, where we have: - TDE enabled at tablespace level. - RMAN encryption turned off Will the backup be still encrypted? As per oracle document - Doc ID 819167.1 First paragraph in Solution se...
Categories: DBA Blogs

utlrp.sql recompile is very slow sometimes, like this one

Sun, 2020-07-05 09:06
Hi AskTom team, Originally I had a very nice question for you, but I forgot, but this one is equally important to me. My question is about why does my recompile "SQL> @?/rdbms/admin/utlrp.sql" take forever to return? there is no real users on my d...
Categories: DBA Blogs

How to get current schema

Fri, 2020-07-03 02:06
I used alter "session set current_schema = xx" to change schema in a block of code. It is not working and I suspect that the schema has not been changed for some reason. How can I query the system to return the current schema I am in? Is there a p...
Categories: DBA Blogs

Week of Year in SQL - Confusing

Fri, 2020-07-03 02:06
Hi ASK TOM Team! I am a bit confused with the iw option from to_char function in SQL. I'm using version 9i and was looking for function to find the week of the year. I've noticed that the week of year for 01-Jan-2016 is 53 which I expected to be ...
Categories: DBA Blogs

Global variables vs parameters

Fri, 2020-07-03 02:06
Hi Tom, could you explain advantages and disadvantages of using global variables and parameters in Forms in terms of efficiency of application and system resourses. What would you recommend for passing values between blocks and program units in...
Categories: DBA Blogs

Not being utilized all the processors capacity

Wed, 2020-07-01 13:26
Dear sir, Thank you for your support I have Oracle 11g Database server which has 2 dual core processors. It is very critical database and very frequently we are getting performance issues. We noticed that the CPU utilization is not crossing 5...
Categories: DBA Blogs

Understanding purpose of undo for Global Temporary Tables DML operation

Wed, 2020-07-01 13:26
Hi TOM, I know Global Temporary Table (GTT) will generate undo data while any DML operations performed on it within that session before commit. But I want to understand why undo is needed for DML operations on GTT. Is that to support following kind ...
Categories: DBA Blogs

why dba_extents has no answer but dba_segments has results

Tue, 2020-06-30 19:06
I have a test environment which has oracle 10.2.0.1 installed, today I want to resize a tablespace named ANNEX which has a size:347921216KB because there is no usable data in it.the usage of the datafile Corresponding this tablespace is 0.7%, ...
Categories: DBA Blogs

Interactive Report pagination automatically stick/unstick to the bottom

Tue, 2020-06-30 00:46
Hi, Is there any way to make the interactive report pagination automatically stick and unstick to bottom of page like interactive grid does? I tried the following but it didn't work: <code>$('.a-IRR-paginationWrap--bottom').stickyWidget({ togg...
Categories: DBA Blogs

RMAN backup on Azure Blob Storage

Tue, 2020-06-30 00:46
When I deploy Oracle Database on Amazon EC2, I can backup to Object Store with RMAN, using "Oracle Secure Backup Cloud Module for Amazon S3". This is great. When I deploy Oracle Database on Azure, I would like to use Azure Blob Storage with RMA...
Categories: DBA Blogs

db_16k_cache_size, db_block_size parameter setting

Mon, 2020-06-29 06:26
This is about the db_block_size, db_16k_cache_size parameters. Oracle document says, Statement 1: DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE...
Categories: DBA Blogs

Foreign Keys

Fri, 2020-06-26 23:46
Hi Tom, this question was put to me by a developer/designer and as I couldn't a) Find the Answer or b) make up a convincing answer I thought I would ask you. Basically it is .... HOW does Oracle make the changes to a child table when ...
Categories: DBA Blogs

Pages