Tom Kyte

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

Garbage question mark appear from time to time when insert clob via jdbc

Wed, 2018-02-07 21:26
Hello, We are using JAVA JRE 5 and we add rows using jdbc thin client that connect to 12.1 server We call a store procedure. The problem was also ocurred when we used oci driver and in previous servers 10.xxx We found that when we insert a ...
Categories: DBA Blogs

Spool path with correct quarternr

Wed, 2018-02-07 21:26
I have problem to determine the quarter dynamically for the spool path in the form W:\SG-323\TeamZentraleKatalogredaktion\Regelupdate\Q4_2017\Sonderlocken\dst_polizei-GDP.csv SQL Developer Execution: @test set echo on column filename new_val f...
Categories: DBA Blogs

When does STALE_STAS changes from NO to YES

Wed, 2018-02-07 21:26
Hi, I'm trying to understand the trigger that changes the column value STALE_STATS in DBA_TAB_STATISTICS from NO to YES. I have duplicated a table, gathered stats, deleted 35% of the records, and the stats aren't STALE='YES'. I'd appreciate your ...
Categories: DBA Blogs

Using SYS_GUID() has primary key in large table

Wed, 2018-02-07 21:26
Hey, I have a large table 600 million rows, approximately 70gb, not partitioned (yet), a few indices, etc. I have reports that query this table often but they're very slow (20-60 minutes at times). I want to create this materialized view but ...
Categories: DBA Blogs

please help understanding this MERGE JOIN CARTESIAN

Wed, 2018-02-07 21:26
Hi Gurus, I have below query and exec plan. all tables E-rows are correct. as you can see table ACCT_STAT join to table ACCT and ACCT_DTL, but in plan, looks like MERGE JOIN CARTESIAN happened between table ACCT_STAT and ACCT_TYP table. Do I misunde...
Categories: DBA Blogs

Development DBA certificate

Wed, 2018-02-07 21:26
Hi Team, I am working as Development DBA and PLSQL developer. I wanted to complete Certificate in development DBA. didnt find any development certificate in Oracle website. Could you please advise which certificate is suitable for Oracle develo...
Categories: DBA Blogs

Help understanding SQL exec plan

Wed, 2018-02-07 21:26
Hi Gurus, I have a query it runs really long. after adding hint use_hash, it runs less than one minutes. data in table: det ---239968 bal -- -239968 inst -- 244713 PT_INST --168745 the query as below: below 3 plans are 1, using hash hint, t...
Categories: DBA Blogs

discovering when plans change

Wed, 2018-02-07 03:26
hi , in my environment i found some of query plans often gets changed, i used 10053 to figure out for plan change , but which part to check and how to conclude what is causing plan change that i am not able to figure out can you please help h...
Categories: DBA Blogs

difference between Cardinality feedback and dynamic sampling

Wed, 2018-02-07 03:26
hi Team, i want to know difference between Cardinality feedback and dynamic sampling . additionally cardinality feedback is provides more near by accurate estimation of cardinality on basis of new plan is generated and used in next execution...
Categories: DBA Blogs

Debugging a PLSQL application

Wed, 2018-02-07 03:26
Help me bro..plaes...i didnt understand this requirement an exactly.... This code is located in the Account_Servicing_Fact_Load SSIS package. The final container contains the code components needed to run both aggregation data. You will have...
Categories: DBA Blogs

Range Partition on Virtual column - CTAS.. syntax is not working

Wed, 2018-02-07 03:26
Hi Tom, I have a requirement to create a range partition for an existing table. Lets say the table name is Orders. The table has Order_Date column of timestamp datatype and the data is in UTC timezone. [We get the data in UTC format in a ...
Categories: DBA Blogs

how can alter table

Wed, 2018-02-07 03:26
Can we add a new column between two columns.If yes,please tell me command
Categories: DBA Blogs

SELECT with WITH Clause(subfactoring) and ORA-4031 errors.

Wed, 2018-02-07 03:26
Tom, I want to validate following reasons to keep away from using "WITH CLAUSE" in the select statement: a) Each time the WITH query clause is run, a global temporary table is created and used in the query. Even if the SQL is using bind variabl...
Categories: DBA Blogs

Materialized views and Synonyms

Tue, 2018-02-06 09:06
Good day. I need help with refresh materialized view. I created synonym MySynonym: <code>CREATE OR REPLACE SYNONYM "MyScheme"."MySynonym" FOR "MyScheme2"."SomeTable";</code> I created materialized view: <code>CREATE MATERIALIZED VIEW MyMView T...
Categories: DBA Blogs

Cast to varchar2 with utl_raw

Tue, 2018-02-06 09:06
Hi Below query converts the string into lower case. i would want to keep the case as it is .. Can you please suggest a solution for this. <code>select utl_raw.cast_to_varchar2(nlssort('PRAshantE', 'nls_sort=binary_ai')) from dual; </code> ...
Categories: DBA Blogs

Function based unique index is not used in select

Tue, 2018-02-06 09:06
Hi guys, I'm curious why the function based index is not used in the following SELECT statement. <code> SELECT USERNAME FROM USERS WHERE USERNAME = 'MyUser' AND IS_DELETED = 0; </code> Or <code> SELECT USERNAME FROM USERS WHERE USERNAME = 'M...
Categories: DBA Blogs

Accessing dynamically generated pivot columns through a cursor

Tue, 2018-02-06 09:06
Hi Tom, I have a PL/SQL report wherein the columns change dynamically (refer sample SQL: https://livesql.oracle.com/apex/livesql/s/f8gbm32e2acju7jzvuzjjh3a1) based on the parameter. Suppose if the report is run for the Q1 2018, then it will have...
Categories: DBA Blogs

how to create VIEW in a schema, have to retrieve LOB data over db_link

Tue, 2018-02-06 09:06
ORA-22992: cannot use LOB locators selected from remote tables 22992. 00000 - "cannot use LOB locators selected from remote tables" *Cause: A remote LOB column cannot be referenced. *Action: Remove references to LOBs in remote tables. ...
Categories: DBA Blogs

Constraint to allow either NULL or distinct values

Mon, 2018-02-05 15:06
Hi guys, I wonder if it is possible to create a constraint on a table which does either allow NULL in a specified column for a group of items *or* a unique value. I set up an example. Imagine you can either set a jedi-unit to manage themselves...
Categories: DBA Blogs

Avoiding multiple json traversals

Mon, 2018-02-05 15:06
Good morning folks I'm trying to consume JSON documents that basically follow this structure: <code> { "items": [ { "id": "111A", "someAttributes": [ { "name": "anAttribute", "value": "A Value" }, { "nam...
Categories: DBA Blogs

Pages