Tom Kyte

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

MD5 Function with Large string having characters more than 4000

Thu, 2018-03-08 04:06
I am looking for a function to MD5 values for concatenated columns in the table. I plan to use this function on before insert/update trigger to store this values as one of the column (md5_hash) in the same table. The intention is to use this column f...
Categories: DBA Blogs

srvctl add service on standalone

Thu, 2018-03-08 04:06
Hi, I am working on 12cR1 version installed on my windows 8.1 laptop. Trying to learn EBR and so want to create additional services so that I can connect to two different editions if required. Using below command, but getting error. >srvctl a...
Categories: DBA Blogs

Oracale procedure execution is giving an error

Thu, 2018-03-08 04:06
I have a stored procedure in oracle. Whenever i try to execute it its throwing me an error mentioned below: Error starting at line 5 in command: exec usp_eventmgmt_get_events(TO_DATE('2018-07-18', 'YYYY-MM-DD'),'District',1) *Error report: ORA-06550:...
Categories: DBA Blogs

Mimicking Sql Server OUTPUT Clause

Thu, 2018-03-08 04:06
I am trying converting SQL Server T-SQL queries to Oracle based queries. In SQL Server there is an ability to use the OUTPUT Clause within a query. See example below. BEGIN TRANSACTION DELETE TableA OUTPUT "DELETED".* INTO "TESTARCHIVE"....
Categories: DBA Blogs

Line numbers in error messages do not match source lines

Thu, 2018-03-08 04:06
When running a PL/SQL script through sqlplus, eventual error messages contain a line number that does not match the source lines. For example : <code> ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 144 ORA-06512: at li...
Categories: DBA Blogs

Rman backup

Thu, 2018-03-08 04:06
I have a database - about 6.2Terabytes in size - has about 525G freespace. There are basically three tablespaces (other than the normal. FO_tables has about 50 tables. One of the tables there has two blob columns - each in it's own tablesapce. One...
Categories: DBA Blogs

view compiled with errors

Thu, 2018-03-08 04:06
I am trying to create a view and i am getting message as "<i>Error report: SQL Command: force view VIEW_REPORTEVENTS Failed: Warning: execution completed with warning"</i> <code>CREATE OR REPLACE FORCE VIEW VIEW_REPORTEVENTS AS SELEC...
Categories: DBA Blogs

Find the last time for consecutive rows by status

Wed, 2018-03-07 09:46
Hello, I need some help with an Oracle Sql Query. For each day i must calculate the time difference between each cycle IN & OUT and then sum the difference as total worked minutes. The main problem that i have it's that the input data is variabl...
Categories: DBA Blogs

How to exclude a list of tables in DBMS_STATS.GATHER_SCHEMA_STATS ?

Wed, 2018-03-07 09:46
Hello, Is there any way to exclude a list of tables while doing DBMS_STATS.GATHER_SCHEMA_STATS? DBMS_STATS.LOCK_TABLE_STATS doesn't answer my need exactly. Best regards, Amine
Categories: DBA Blogs

adding space between a number and a letter in a record

Wed, 2018-03-07 09:46
Hi Tom, I want to add space between number and a letter in a column. For eg: somestreet 22a --> Somestreet 22 A oldstreet 5d --> Oldstreet 5 D othernewtreet 134B --> Othernewstreet 134 B Similarly, for all suc...
Categories: DBA Blogs

regarding table partition

Wed, 2018-03-07 09:46
hi Team, We have set initrans value for partition table to 255 in our exadata box, this table have very high concurrency , after which we noticed huge archive generation , here can help us how this inittrans value is related with archive genera...
Categories: DBA Blogs

regexp_replace behavior

Wed, 2018-03-07 09:46
I'm trying to eliminate some parts of a string with regexp_replace and try a lot of regular expressions but I can't have the desire result. I'm using a code similar to the following: Declare w_times number; w_source varchar2(2000); w_t...
Categories: DBA Blogs

undo space issue

Wed, 2018-03-07 09:46
Hi tom, There was an doubt- If i check the total db used size with below query SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_SEGMENTS; SUM(BYTES/1024/1024/1024) ------------------------- 210.410156 -->>Total size with undo it ...
Categories: DBA Blogs

Shared Undo Quota - Can We Dedicate specific amount for a single user?

Wed, 2018-03-07 09:46
Hi, I am facing an issue, where one single process (eg process x) can consume most of the undo-quota, and then fails for rollback segment error. When I'll increase my database undo retention, and increase my undo tablespace size, it'll grow for a...
Categories: DBA Blogs

Compare the Indexes of tables from two different databases

Tue, 2018-03-06 15:26
hi Tom I would like to compare indexes of tables from defferent database. challenge is that the tables have index name different. example db1 schema1 table1--> --> index1 (col1,col2) --> index2 (col11,col22,col33) --> index3 (col111,col2...
Categories: DBA Blogs

ORA-01652: Unable to extend temp segment by 2048 in table space TEMP

Tue, 2018-03-06 15:26
Hi Team, We have facing the issue with 1 query is consuming 331GB of TEMP tablespace. WE have configured 340GB of TEMP TS and have some limitation to add more temp files on DB server. due to it the other session get starved with TEMP tablespace an...
Categories: DBA Blogs

Load External table trimmed with varchar2 without null

Tue, 2018-03-06 15:26
Hi all, i have this CTL FILE: <code>CREATE TABLE EXT_T_SI_EG_RAI_170630E1166A ( WAEHRUNG CHAR(3) ,GESCHAEFT_ID VARCHAR2(48) ,GESCHAEFTSKATEGORIE CHAR(1) ,UNTERGESCHAEFT_ID VARCHA...
Categories: DBA Blogs

Index size got doubled after rebuild

Tue, 2018-03-06 15:26
Hi, We did index rebuild for the partition table. Index is also partitioned one. Before rebuilding index, the size of the index was 170 GB. But after rebuilding the size got doubled and it was 327GB. Used below command: alter index index_na...
Categories: DBA Blogs

How to trace PL/SQL procedure for tuning

Tue, 2018-03-06 15:26
Hi tom, There is an procedure which is running very long/ taking time So, how can i trace that procedure to check where is the issue. like - tkprod or trace session
Categories: DBA Blogs

Need help to select dates in Israel time from a database server located in US

Tue, 2018-03-06 15:26
select example_dt from tab where tabid='123'--example_dt is a date column in the table 'tab' In this above query how can I get/convert example_dt in Israel time if I am running this query from my US database server?
Categories: DBA Blogs

Pages