Tom Kyte

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

NVARCHAR2 > 2000 - MAX_STRING_SIZE

Fri, 2022-08-05 15:46
Hello, I inherited from a schema that is using nvarchar2. I am trying to recreate that schema but I am struggling to create table with nvarchar2 that have a size of 4000 (> 2000). I have changed the max_string_size to "extended" but it does not works. I works varchar2 with e.g. 32676 bytes. Here are the operation I made. <code> sqlplus / as sysdba ALTER SESSION SET CONTAINER=CDB$ROOT; ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE; shutdown immediate; startup upgrade; ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE; EXIT; cd $ORACLE_HOME/rdbms/admin mkdir -p /home/oracle/tmp/log_utl32k $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/log_utl32k' -b utl32k_cdb_pdbs_output utl32k.sql sqlplus / as sysdba shutdown immediate; startup; ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE; EXIT; mkdir -p /home/oracle/tmp/utlrp $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'READ WRITE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/utlrp' -b utlrp_cdb_pdbs_output utlrp.sql sqlplus / as sysdba create table testvc (name varchar2(32676)); >>Table created. create table testnvc (name nvarchar2(4000)); >>ORA-00910: specified length too long for its datatype </code> I have checked several resources but I cannot find a solution. Any suggestion are welcomed.
Categories: DBA Blogs

Indexes with or without TRIM

Fri, 2022-08-05 15:46
If I have an index, for example, by CD_CLIENT, CD_COMMOD, CD_MERCAD, and there is a query that uses them with TRIM. So: ....... <code>WHERE TRIM( CUSTOMER_CD ) = ... AND TRIM(CD_COMMOD) = ... AND TRIM(CD_MARKET) = ...</code> Should I make another index that has the TRIM ? Or is that enough? Thank you very much!!
Categories: DBA Blogs

Your suggestions on OLAP technology

Fri, 2022-08-05 15:46
We are evaluating OLAP technology for implementing some analytics for our product. We are reviewing technologies from Oracle, Microsoft and other vendors. Correct my if I am wrong, OLAP technology with Oracle looks like an early stage feature. I got this assumption when comparing the features, ease of use etc with other vendors. Even Microsoft also well in advance in that area. I couldn?t see much customer references for Oracle OLAP. Please comment on the following issue as you have more expertise with the different technologies and industry? What are your suggestions on choosing OLAP technology? What are your suggestions on Oracle OLAP? Should we consider it? If yes, why?
Categories: DBA Blogs

Outer Join with OR - Ansi vs Oracle

Thu, 2022-08-04 21:26
hello I'm curious how you would deal with the following. Table t1 has mixture of usernames, some with format of email address, some with format of first_name.last_name. Table t2 lookup table has username in email format. Requirement to return all 3 rows <code> create table t1 (taskid number, username varchar2(50)) insert into t1 values (1,'john.smith@12]3.com'); insert into t1 values (2,'dave.jones@123.com'); insert into t1 values (3,'dave.smith'); commit; create table t2 (username varchar2(50)); insert into t2 values ('john.smith@123.com'); insert into t2 values ('dave.smith@123.com'); commit; </code> Via ansi syntax, quite forward: <code> select * from t1 left outer join t2 on t1.username = t2.username or t1.username = substr(t2.username,1,instr(t2.username,'@')-1) order by 1 </code> Via oracle syntax - As can't use outer joins with OR, do you have any (easier) alternatives than this? <code> select t1.taskid, t1.username, t2.username from t1, t2 where t1.username = t2.username union all select t1.taskid, t1.username, t2.username from t1, t2 where t1.username = substr(t2.username,1,instr(t2.username,'@')-1) union all select t1.taskid, t1.username, to_char(null) from t1 where not exists (select 1 from t2 where t1.username = t2.username or t1.username = substr(t2.username,1,instr(t2.username,'@')-1)) order by 1 </code> thanks
Categories: DBA Blogs

Shrink space row movement

Thu, 2022-08-04 21:26
I use the process below to shrink datafiles and reclaim operating system space. It works fine. My question is there any downside to ALWAYS leaving row movement enabled instead of disabling it when the process is completed? Below is a sample of what I'm currently doing. <code>create table t as select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 10000; / -- ALTER TABLE t SHRINK SPACE CHECK; select bytes from user_segments where segment_name = 'T'; / delete t where x <= 9900; / select bytes from user_segments where segment_name = 'T'; / alter table t enable row movement; / alter table t shrink space CASCADE; / select bytes from user_segments where segment_name = 'T'; </code>
Categories: DBA Blogs

KVM with an OnPrem Exadata X9-2 M

Thu, 2022-08-04 21:26
I have a client that is trying to implement KVM on an Exadata X9-2 M (21.2.11.0.0), and he was told that in the Exadata he could have a single VM running in a single DB Node in the exadata, not a cluster VM, but just a single VM in a single DB Node. To my understanding, that is not possible in the Exadata as they had to be VM clusters, 2 DB nodes, instead of 1 DB node. I'm I incorrect in what I know?
Categories: DBA Blogs

SQL Model

Thu, 2022-08-04 21:26
Tom, sorry about the silly question, but have been trying to do something with SQL Model without any success. I cannot understand how it works, could you do some simple sample? -- may be already exists. Thanks,
Categories: DBA Blogs

adding sysdate on the file name

Mon, 2022-08-01 20:06
Hi Tom When spooling the file on PL/SQL, i would like the file name to have sysdate attached to the filename and it should change dynamicaly.. e.g.PM-INT-20021129_FLASHNOTIFICATIONS.TXT Thanx Zoliswa
Categories: DBA Blogs

analytical vs aggregate functions

Mon, 2022-08-01 20:06
Are analytical functions replacement for aggregate functions? Some of the developers here are hurrying in replacing the group by aggregate functions with analytical functions just because they learnt something new :-).. Are the analytical functions faster always(compared to aggregate)?
Categories: DBA Blogs

FORALL issues...

Mon, 2022-08-01 20:06
Hi Tom, I'm using the FORALL statement to update tables in my applications. When the update raises an error, it can be saved using the SAVE EXCEPTIONS clause and then continuing with the update, or the update can be aborted without using that clause. In both these situations however, I am unable to pin down which specific record caused the error, i.e. the id of the record. More specifically, is there a way to store user defined data upon encountering an error during a FORALL execution? Small example: declare type intArray is table of number index by binary_integer; type charArray is table of varchar2 (2000) index by binary_integer; type recType is record (idArray intArray, array2, charArray); l_tabrec recType; lt_id intArray; begin ... ... /* populating the l_tabrec record of arrays here. */ ... begin forall i in 1..l_tabrec.idArray.count update table2 set value = l_tabrec.array2(i) where id = l_tabrec.idArray(i); exception when others then /* here is where I'd like to log the id of the record which fails. */ raise; end; end; I know the SAVE EXCEPTIONS gives us information about the type of error and the iteration on which it encountered the error, but I'm not sure how I can use that information to find which record actually caused the error. Hope my question is clear. Thanks in advance. Kashif
Categories: DBA Blogs

How can I suppress the other error messages from PLSQL errors

Fri, 2022-07-29 18:46
When we use raise_application_error procedure in exception part, the procedure returns customized as well as other error message. How can I suppress the other error message? For example... ======================================= SQL> execute error_mesg(60); BEGIN error_mesg(60); END; * ERROR at line 1: ORA-20001: No Department exists! ORA-06512: at "BHAVESH.ERROR_MESG", line 18 ORA-06512: at line 1 ======================================= is the message I get in my procedure bhavesh.error_mesg. I don't want all other ORA_065512. All I want in my error message is ORA-20001 which we defined using raise_application_error package. Thanks for the help. Regards, Bhavesh
Categories: DBA Blogs

dense_rank vs. rank

Fri, 2022-07-29 18:46
Hi, Tom. Could you please tell me more about how to determine when to use analyatic function dense_rank, and when to use rank? Thanks. Bo
Categories: DBA Blogs

Faster way to Pivot the data and insert into another table

Fri, 2022-07-29 00:26
Hi All, I have a table to 1.8b records and need to pivot this table and insert it into another table. The performance of this SQL is very bad and it took around 8hr but no results. Please find the sql below. <code>v_sql1 := q'{ INSERT INTO SCPOMGR.FCSTWIDE ( dmdunit, dmdgroup, loc, startdate, TYPE, fcstid, model, dmdcal, period1, period2, period3, period4, period5, period6, period7, period8, period9, period10, period11, period12, period13, period14, period15, period16, period17, period18, period19, period20, period21, period22, period23, period24, period25,period26, period27, period28, period29, period30, period31, period32, period33, period34, period35, period36, period37, period38, period39, period40, period41, period42, period43, period44, period45, period46, period47, period48, period49, period50, period51, period52, period53, period54, period55, period56, period57, period58, period59, period60, period61, period62, period63, period64, period65, period66, period67, period68, period69, period70, period71, period72, period73, period74, period75, period76, period77, period78, period79, period80, period81, period82, period83, period84, period85, period86, period87, period88, period89, period90, period91, period92, period93, period94, period95, period96, period97, period98, period99, period100, period101, period102, period103, period104, period105, period106, period107, period108, period109, period110, period111, period112, period113, period114, period115, period116, period117, period118, period119, period120, period121, period122, period123, period124, period125, period126, period127, period128, period129, period130, period131, period132, period133, period134, period135, period136, period137, period138, period139, period140, period141, period142, period143, period144, period145, period146, period147, period148, period149, period150, period151, period152, period153, period154, period155, period156, period157, period158, period159, period160, period161, period162, period163, period164, period165, period166, period167, period168, period169, period170, period171, period172, period173, period174, period175, period176, period177, period178, period179, period180, period181, period182, period183, period184, period185, period186, period187, period188, period189, period190, period191, period192, period193, period194, period195, period196, period197, period198, period199, period200, period201, period202, period203, period204, period205, period206, period207, period208, period209, period210, period211, period212, period213, period214, period215, period216, period217, period218, period219, period220, period221, period222, period223, period224, period225, period226, period227, period228, period229, period230, period231, period232, period233, period234, period235, period236, period237, period238, period239, period240, period241, period242, period243, period244, period245, period246, period247, period248, period249, period250, period251, period252, period253, period254, period255, period256, period257, period258, period259, period260, period261, period262, period263, period264, period265, period266, period267, period268, period269, period270, period271, period272, period273, period274, period275, period276, period277, period278, period279, period280, period281, period282, period283, period284, period285, period286, period287, period288, period289, period290, period291, period292, period293, period294, period295, period296, period297, period298, period299, period300, period301, period302, period303, period304, period305, period306, period307, period308, period309, period310, period311, period312, period313, period314, period315, period316, period317, period318, period319, period320, period321, period322, period323, period324, period325, period326, period327, period328, period329, period330, ...
Categories: DBA Blogs

Recursive query to calculate salary increases

Tue, 2022-07-26 17:26
Please tell me what condition of the problem can be thought up so that it can be solved only by recursion, not connect by. For example, you can do something like There is a list of employees, the coefficient of salary increase every year, his current salary For each of them, draw up a schedule for 20 years ahead of salaries, given that if the total salary of the entire department exceeds a million, then 10% is subtracted from each salary, and 10% is added to this million My table <code>create table scrubs5(name varchar2(100),cent number, salary number); insert into scrubs5 values ('John Dorian' ,10,50000); insert into scrubs5 values ('Eliot Reed',15 ,40000); insert into scrubs5 values ('Pesivald Cox',10, 35000); insert into scrubs5 values ('Bob Kelso' ,20,30000); insert into scrubs5 values ('Cris Terk',15, 25000);</code> Code <code>WITH data( sal, name, n, cent) AS ( SELECT salary as sal, name, 1 as n, cent FROM scrubs5 UNION ALL SELECT sal + sal*cent/100, name, n+1, cent FROM data WHERE n < 5 ), data1(sal, name, n, cent, t) as (SELECT sal, name, n, cent, 100000 as t FROM data union all select case when sum(sal) over (partition by n)> t then sal- sal*cent/100 else sal end, name,n+1, cent, case when sum(sal) over (partition by n)> t then t+t*10/100 else t end from data1 WHERE n < 5) select sal, name, n, cent, t from data1 order by n ;</code>
Categories: DBA Blogs

print the multiplication tables in PLSQL

Tue, 2022-07-26 17:26
Hi , I want print the multiplication table, if i enter 2 then need to display 2 multiplication , if i enter 50 then want print 50th multiplication. Can you give both sql and plsql 2 examples ?
Categories: DBA Blogs

TO_date function behaviour in select list and where clause

Tue, 2022-07-26 17:26
Dear Tom, I have column (C1) in varchar2 datatype, and it has around 10k records coming as 'dd-mon-yyyy'format (ex:'22-jun-2022'). when querying below is exucuted without error, select C1, CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022') and to_date('30-Jun-2022') then 1 else 0 end ---for checking it is giving 1 or 0 from T; where it as,if I write like below getting error as 'ora-01858 a non-numeric character was found where numeric expected'. select C1, CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date() and to_date() then 1 else 0 end from T where TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022') and to_date('30-Jun-2022'); --also tried select C1, CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date() and to_date() then 1 else 0 end from T where TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022','dd-mon-yyyy') and to_date('30-Jun-2022','dd-mon-yyyy'); In the select list it is not getting any error,but in the where clause it is showing error. Please share if any clue/references?
Categories: DBA Blogs

Quarter Date calculation

Tue, 2022-07-26 17:26
Hi Tom, I work in the insurance industry and I'm having trouble calculating the quarter. My insurance policy runs from 1-Feb-2022 to 31-Jan-2023, and I pay my premiums quarterly. Inst No Inst date 1 01-Feb-2022 ( Collect premium for Feb,Mar, Apr) 2 01-May-2022 ( Collect premium for May, Jun, Jul) 3 01-Aug-2022 ( Collect premium for Aug, Sep, Oct) 4 01-Nov-2022 ( Collect premium for Nov, Dec, Jan) Suppose in the above policy a new risk is added from 01-Jun-2022 to 31-Jan-2023, then the premium for this risk should be collected on the following dates Inst No Inst Date 2 01-Jun-2022 ( Collect premium for Jun, Jul) 3 01-Aug-2022 ( Collect premium for Aug, Sep, Oct) 4 01-Nov-2022 ( Collect premium for Nov, Dec, Jan) Using the above example, kindly guide me on how can I arrive the instalment dates for the newly added risk
Categories: DBA Blogs

Oracle Raptor

Tue, 2022-07-26 17:26
Tom, Just curious about your impressions of Oracle Raptor. I've installed the Early Adoptor Release and it seems to be a slick tool for SQL and PL/SQL application development. What group is Oracle trying to target with this free tool? How is it better then other development tools already out there. What are the limitations of Raptor - how isn't it meant to be used? As always, thanks for your insight! Regards, Craig
Categories: DBA Blogs

A packaging problem

Mon, 2022-07-25 23:06
I'm not new to programming, but I am new to PL/SQL, and would welcome your perspective on the task I've been given. I need to move about 50 AIX shell scripts (aka 'processes') to PL/SQL. The processes are similar in nature in that they each call a combination of SQL*Loader, existing PL/SQL procedures, and SQL (no dynamic SQL) statements. They all share one common characteristic; they will all be called from our enterprise scheduling application (Autosys). Some are scheduled and others are event-driven. They are not similar enough to make a central subprogram with parameters practical, although I do intend to use common/shared subprograms for routine tasks such as error logging, status logging, etc. My initial thought was to use one package with subprograms for logging, etc. and each process in a separate procedure in the package body, but after creating the pseudo-code for the simplest process, I think this will result in a very large package body, which seems undesirable, at least from a maintenance and documentation standpoint. I'm now thinking each process should be a separate package with another package for the common subprograms. I would welcome your thoughts on the best approach. Thank you for your consideration.
Categories: DBA Blogs

Auto Optimizer Stats Failures

Mon, 2022-07-25 23:06
Howdy, Trying to figure out why my auto stats jobs are failing perpetually. <code> select job_info from dba_autotask_job_history where client_name = 'auto optimizer stats collection' order by window_start_time desc; "ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "SYS.DBMS_STATS", line 51871 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24430 ORA-06512: at "SYS.DBMS_STATS", line 51859 " </code> Best I can tell this is happening on function based indexes applying trunc(<column_name>). I'm guessing this because if I run stats manually and pull up the real time monitor I can see big red X's for some indexes that are function based utilizing TRUNC to a date column. Checked oracle support and found Auto Statistics Advisor fails with ORA-01841: (full) Year Must Be Between -4713 And +9999 (Doc ID 2771841.1) but neither of the problem definitions seem to be my problem. <code> SELECT name, ctime, how_created FROM sys.wri$_adv_tasks WHERE owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK'); AUTO_STATS_ADVISOR_TASK 17-apr-2019 01:32:53 CMD INDIVIDUAL_STATS_ADVISOR_TASK 17-apr-2019 01:32:53 CMD </code> Any guidance would be greatly appreciated, thanks!
Categories: DBA Blogs

Pages