Tom Kyte

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

Best Practice to convert 400 million rows table with 3 TB of CLOB to partition table

Thu, 2018-02-08 16:06
Hi, I am converting a non-partitioned table to Partitioned(range or Ref). For test scenario, I have a table with 50 GB data (300 million rows) and 100 GB CLOB column data. I tested below options and timings are not as per my satisfaction level. 1. ...
Categories: DBA Blogs

external jobs with dbms_scheduler ORA-27369 exit code Bad address

Thu, 2018-02-08 16:06
Hello, I have this job (EXECUTABLE) that exec a bash script. Here's a resume of what it does connect to a FTP server, get a file, call a pkg via sqlplus using the files as external table in order to import data. The job fails with 'ORA-27369:...
Categories: DBA Blogs

Does a sys ref cursor have performance overheads

Thu, 2018-02-08 16:06
Hello, Currently I'm working on a procedure which returns data (big volume of data) to a calling procedure (java). The pl/sql procedure opens a sys ref cursor like pr_get_transactions ( i_from_date IN DATE ,o_transactions...
Categories: DBA Blogs

https request failed

Thu, 2018-02-08 16:06
Hi Team, We are using http request which is working fine now we are planning to move into https for that i have performed below steps On 12c- Create a wallet - $ orapki wallet create -wallet /u01/app/oracle/wallet -pwd password@123 -auto_login...
Categories: DBA Blogs

With Spool command generate output from two different query and if it match create dummy column "result" as match or successful

Thu, 2018-02-08 16:06
Hello Tom, Required your help in below scenario: Requirement : we have primary and DR server where we need to monitor synchronization between this two server. Method used: I have planned to use SPOOL command which connect to primary server ...
Categories: DBA Blogs

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

Pages