Tom Kyte

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

"alter session sync with primary" with Maximum Performance Protection Mode

Wed, 2024-02-28 02:26
"alter session sync with primary" raises ORA-03173 for us. <code>SQL> select database_role, open_mode, db_unique_name from v$database; DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME ---------------- -------------------- ------------------------------ PHYSICAL STANDBY READ ONLY WITH APPLY mdpams SQL> alter session sync with primary; ERROR: ORA-03173: Standby may not be synced with primary</code> Is this expected behaviour in protection mode "Maximum Performance" or do we maybe hit some bug ? dgmgrl shows nothing suspicious <code>DGMGRL> show configuration Configuration - fsc Protection Mode: MaxPerformance Members: mdpfra - Primary database mdpams - Physical standby database mdpdev - Snapshot standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 61 seconds ago) DGMGRL> show database mdpams Database - mdpams Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 4.94 MByte/s Real Time Query: ON Instance(s): mdpams1 (apply instance) mdpams2 Database Status: SUCCESS DGMGRL> </code>
Categories: DBA Blogs

How to do update (replace values) in a table contains 50+ million records?

Wed, 2024-02-28 02:26
Hi, I have a table contains 50+ million records, and I am writing a procedure to replace the bad data to the correct values(about 1500 records). <i><b>K_V</b></i> is the array of bad data and target correct value,like <code>K_V('bad data1') := 'correct value1'</code> when I loop the <i><b>K_V</b></i>, do <code>'update table set xx=replace(xx,bad data,correct value);'</code> This procedure run whole night but still can not finish. So how can deal with this problem? Seems I can not write the procedure that way. Thanks.
Categories: DBA Blogs

what is lobsegment,lobindex

Wed, 2024-02-28 02:26
I query table 'user_segments' sql>select segment_name, segment_type, tablespace_name, bytes, max_extents from user_segments where segment_type like 'LOB%' result is SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SYS_IL0000012099C00002$$ LOBINDEX CPOCKET_DATA SYS_LOB0000012099C00002$$ LOBSEGMENT CPOCKET_DATA => I don't know what is lobsegement, lobindex. What is lobsegment, lobindex and why need it. Can't I delete it? Please explain detail about lobsegement, lobindex. Thank you.....
Categories: DBA Blogs

Capacity Planning

Tue, 2024-02-27 08:06
Hi Tom, I have a some questions regarding the capacity planning.Thanks in advance. 1.is there any way we can match LIOs & PIOs to the no of CPUs & no of disks ? 2.is there any place , i can find documents to do capacity planning for the oracle database/sun solaris environment? 3.I am very much confused about the sort_area_size my understanding is -- sort_area_size is the max threshold to do sort on memory and only one only sort_area_size per session .Alloc from UGA --sort_area_retained is to store the result set from SAS and it can be many per session at a time.is it correct? Alloc from PGA. When we do first sorting which is lesser than sort_area_size,the memory allocated from PGA or UGA? is it sort_area_size or sort_area_reatined? Thanks in advance Regards Jeyaseelan.M
Categories: DBA Blogs

move table to new tablespace

Tue, 2024-02-27 08:06
Hi TOM i have oracle cluster database EE with 2 nodes, and i have a big table with a big LOBs row, so after compressing the LOBs files i did move them to a new tablespace,so the principal table became just about 300MB but it still taking space of 1.2Tb, my concerns are about the space why i cant reclaim this space, i created a new tablespace and i did table move but it doesn't work it took so much time and i did shrink but it doesn't work too, i think there is a problem with high watermark? what i have to do please to gain this space and thanks. The lobs were saved in the same tablespace with other data caled DATA having 1.2 Tb, after that i did move them to a new tablespace i created caled LOB_DATA the problem is the shrink space for table space DATA did nothing and the table move also doesn't work so how to reclaim the free extents in DATA 1.2Tb tablespace.
Categories: DBA Blogs

Record / Check Login Information for Standby DBs

Tue, 2024-02-27 08:06
Hello We want to housekeep our user accounts and remove unsed and locked accounts. As far as I understand, the information in dba_users is from the primary DB. Users are not allowed to logon to the primary to query data, they must logon the read only standby (regulated by a trigger). When I look in dba_users on the standbys I can see several users that have not or never logged on: <code>select username, account_status, nvl(to_char(last_login),'never logged on') "Last Login" from dba_users where oracle_maintained = 'N' and username not in ('AAAAAAAAAAA','BBBBBB','CCCCCCC') and username not like '%READ%' and username not like '%Exxx%' order by "Last Login" desc;</code> USERNAME ACCOUNT_STATUS Last Login ------------------------------ --------------- ---------------------------------------- Pxxxxxxx OPEN never logged on Pxxxxxxx_03 LOCKED never logged on Pxxxxxxx_05 LOCKED never logged on Pxxxxxxx_04 LOCKED never logged on Pxxxxxxx_01 LOCKED never logged on BRxxxxxxx OPEN never logged on Pxxxxxxx_02 LOCKED never logged on Sxxxxxxx EXPIRED never logged on Jxxxxxxx EXPIRED never logged on Mxxxxxxx OPEN 2020-09-05:19:48:06 GMT+01:00 Bxxxxx OPEN 2020-09-05:19:19:52 GMT+01:00 Axxxxxx OPEN 2016-05-20:09:17:33 GMT+01:00 Pxxxxxxxxxx_01 OPEN 2016-04-21:10:48:34 GMT+01:00 Kxxxxx OPEN 2016-04-19:13:50:33 GMT+01:00 Pxxxxxxxxxx_01 OPEN 2016-04-13:14:18:17 GMT+01:00 However, this information from dba_users is identical on primary and standby DBs. The users told me that they have logged to the standby recently. As far as I understand the information in dba_users, also on the standby has been inherited from the primary as normal catalogue tables are not updated on the standby. Is this correct? How can I see last logins on the standby, preferably witthout using auditing which could cause a performance degredation and this is a production system where performance is key. Many thanks Alison We are using active dataguard, and our idea at the moment is to record logins to the standby using a trigger which checks if standby or primary and then writes logon data acroos a DB link into a table on primary. Many thanks
Categories: DBA Blogs

View with pivot and group by grouping sets work in 12c but not in 19. Error ORA-56903

Tue, 2024-02-27 08:06
Views with group by grouping sets and pivot directly or in referenced view which worjk in Oracle 12c fall with ORA-56903 error sys_op_pivot function is not allowed here in Oracle 19. Bit in view or referenced view don't have explicit call sys_op_pivot. Mybe Oracle use it during execution of views. Thanks in advance. Best regards. According sugestion I have put: alter session set optimizer_features_enable = '12.2.0.1' but error persists. It appeare in all view with grouping sets and with pivot clause in subview as base view or directly in current view. If view with pivot clause is subview subview work correctly.
Categories: DBA Blogs

UTL_FILE.FGETATTR can not find an existing file

Mon, 2024-02-26 13:46
I created a text file on oracle database server. The name of the file is 'TestFile' and it is located in C:\TestFolder\TestFile.txt . All C drive and 'TestFolder' folder and 'TestFile.txt' file have full control permission for everyone OS users. I create a directory: Create directory CheckFileExist as 'C:\TestFolder'; I grant read and write permissions on CheckFileExist directory to SYS oracle user: Grant read, write on directory CheckFileExist to SYS; I wrote a query so that Oracle can find the 'TestFile.txt' file or not: Declare V_File_Exists Boolean; V_File_Length Number; V_File_Size Number; Directory_Name Nvarchar2(255):='CheckFileExist'; Begin UTL_FILE.FGETATTR (Directory_Name, 'TestFile', V_File_Exists, V_File_Length, V_File_Size); If V_File_Exists Then DBMS_OUTPUT.PUT_LINE('File exists'); Else DBMS_OUTPUT.PUT_LINE('File does not exist'); End if; End; When I execute the query, the result is that File does not exist. What is the problem?
Categories: DBA Blogs

UTL_HTTP

Mon, 2024-02-26 13:46
Is Oracle working on the Oracle Database PL/SQL package UTL_HTTP to add support for http_versions: HTTP/2 and HTTP/3?
Categories: DBA Blogs

Object Dependency with RPC-Signature Dependency Mode

Mon, 2024-02-26 13:46
Dear AskTom team, I am happy that you again available for questions :-) I was studying the 'Database Development Guide - 26 Understanding Schema Object Dependency' and focused on the topic '26.10.2 RPC-Signature Dependency Mode'. There is written: 'Changing the data type of a parameter to another data type in the same class does not change the RPC signature, but changing the data type to a data type in another class does.' After studying I tried it out on LiveSQL. Sadly the dependent object always gets invalid after I changed the parameter of the referenced object to another data type in the same class (eg from 'number' to 'integer') - refer to my LiveSQL link. I tried to understand it but I didn't. What do I wrong here? Or did I got the documentation wrong? Thanks for your support! Greetings, Walter
Categories: DBA Blogs

Different lists of dependencies

Mon, 2024-02-26 13:46
As part of a migration effort, I'm researching dependencies and am confused by the different results displayed by SQL Developer's Dependencies tab versus running something like the following: <code>SELECT * FROM user_dependencies WHERE name = 'USP_COMPANYIMPORT';</code> The former displays 19 rows, whereas the latter displays only 15 rows, including two where the REFERENCED_OWNER is SYS. Q1: Why the difference? Q2: Is it possible to view the code SQL Developer runs to obtain its results? Thank you.
Categories: DBA Blogs

DBLINK CONNECTION INETRUPTED_ROLLBACK NOT HAPPENS

Mon, 2024-02-26 13:46
User I HAVE SUCCESSFULLY RUNNED A JOB WHICH HAS STORED PROCEDURE OF TRUNCATE AND INSERT STATEMENTS USING DBLINK, IF ERROR OCCURS, ROLLBACK STAEMENTS ALSO THERE IN ORACLE PLSQL. WHILE DOING SO, TRUNCATED DONE AND DBLINK COMMUNICATION INTERUPPTED AND GOT END. SO, DATA TRUNCATED BUT NEITHER INSERTED NOR ROLLBACK. NOW THERE IS NO DATA IN THE TABLE. IF I RUN THE SP IMMEDIATE I DONT FIND CONNECTION PROBLEM IN DBLINK AND DATA ARE INSERTED. WHAT COULD BE THE PROBLEM? create or replace sp_dataload begin execute immediate 'TRUNCATE TABLE TABLE1'; INSERT INTO TABLE1 ( ID ,NAME) SELECT * FROM TABLE2@DBLINK; COMMIT; dbms_output.put_line('complete'); Exception when others then dbms_output.put_line("error" || SQLERRm); Rollback; end /
Categories: DBA Blogs

Dropping and purging table does not release space back to the tablespace

Mon, 2024-02-26 13:46
Dear Tom, Oracle 4 node RAC version 19c In my tablespace I have total of 570 partitioned tables that are zero rows. Their initial extent is 8M for each partition, so collectively the empty tables are occupying 2286.03 GB. As they are not needed, I have started to drop them. After dropping some 300 tables, I wanted to check the space released. But this query shows the occupied space is not released. I always thought that if I drop a table with purge, the space would immediately be released back to the tablespace. What am I doing wrong? select round(sum (bytes/1024/1024/1024),2) GB from dba_segments Where tablespace_name='TOPREP_DAT' and owner ='SAMSUNGLTE'; GB --- 2286.03
Categories: DBA Blogs

Formatting numbers to group exponent by 3

Thu, 2024-02-22 18:06
Hi All It's my first question here :) I read about formatting number in scientific notation, but I don't find a way to group exponent by 3 For instance, I would have this result Number -> result 1 -> 001.0E0 12 -> 012.0E0 123 -> 123.0E0 1234 -> 001.2E3 12345 -> 012.3E3 123456 -> 123.4E3 1234567 -> 001.2E6 ... is this a way to do this in a to_char function with the format clause ? Regards Eddy
Categories: DBA Blogs

Query values from v$.. View in a view

Wed, 2024-02-21 23:46
Dear Tom, As User System connected I want to query a V$ table in a view. When creating the view I get the error ORA-00942: table or view does not exist. Example: <code>create or replace view myview_datafile as select file#, bytes/1024/1024||' MB' as "SIZE" from V$datafile; * ERROR at line 1: ORA-00942: table or view does not exist</code> Executing the same query with sqlplus works. Is a special privilege required to make this possible? Many thanks in advance for a reply. Hubert
Categories: DBA Blogs

get current cursor position in TinyMCE Oracle Apex 23.1

Wed, 2024-02-21 23:46
I need to have current cursor position in TinyMCE Oracle Apex. I use this js code for another type of page items and it work correctly. var curPos = document.getElementById("P7_TEXT").selectionStart; but when "P7_TEXT" is a Rich Text Editor (TinyMCE) , it will return "undefined" what can I do to get the current position of cursor in rich text page item? APEX version is 23.1.
Categories: DBA Blogs

Regarding the Current Role of PL/SQL in Modern Technology Stacks

Wed, 2024-02-21 05:26
Dear Team, I hope this message finds you well. I have been reflecting on the current landscape of PL/SQL and its role in contemporary technology stacks. I would greatly appreciate your insights on a few points that have been on my mind. <b> PLSQL for Business Logic ? </b> While it's widely acknowledged that "as long as there is Oracle, there will be PL/SQL," I am eager to explore forward-looking scenarios where PL/SQL remains a prominent choice for business logic. In today's context, it seems that business logic is predominantly implemented using modern object-oriented languages such as Java or .NET, leveraging features like Streams and Lambda functions. Could you provide examples or use cases where PL/SQL excels and is considered integral, especially in comparison to these object-oriented approaches? <b> PLSQL for Data Engineering ? </b> The ETL landscape has witnessed a significant shift towards technologies like Spark for seamless integration with data warehouses and data lakes. In this evolving scenario, I am curious to understand how PL/SQL continues to play a vital role in ETL processes. Are there specific use cases or examples where PL/SQL is still the preferred choice in modern data engineering stacks? I understand the historical significance of PL/SQL in minimizing network calls and maintaining code proximity to databases, as highlighted in research papers advocating for a thick database approach. However, I am keen to bridge the gap between theoretical advantages and practical implementations. Are enterprise projects aligning with this approach, or is the trend shifting towards business logic predominantly residing in Java/.NET environments? In essence, <b>could you kindly furnish examples and use cases illustrating</b> where PL/SQL stands out as a core, integral component in modern data engineering or application development stacks? <b>I am particularly interested in understanding if PL/SQL is now primarily considered a supplementary or exception-use language, driven by compliance requirements rather than intrinsic value in data movement scenarios.</b> I appreciate your time and insights into this matter, and I look forward to hearing from you soon.
Categories: DBA Blogs

Query taking very long.

Tue, 2024-02-20 10:46
Hi Tom, I'm facing an issue somewhat strange and to which a have no clear answer. The database version that I'm using is 12.1.0.1.0 on Windows 64 (both Standard and EE). With 11G (I believe) Oracle started using the Unified Audit Trail. The default "rules" for my version (12c) inserts a new record in the audit table every time a user connects to the DB. Because these databases are not mine, the size of the tablespace (SYSAUX), was not under surveillance and has grown to a considerable size without anyone noticing it. At the moment the tablespace and table are around 16GB and 14GB. We have tried to remove the information from the table and that process is ongoing. My question is: In the DBs where this table and tablespace have grown to such sizes, any (or many) query run against a object in that tablespace take a huge amount of time. Of course, I know that if a object is very large, then that means it's going to take a long time to read, but here we are talking about a really large amount of time. When I'm executing a query against that tablespace the disk subsystem (SSD disks) starts to read about 130 MBs a second. In a symplistic way, one could say that it should read the necessary 16GB in a bit less than 200 seconds, but the system takes more than 10 minutes (I never allowed it to actually finish because these are PROD systems). What I would like to know is if there is anything specific about objects inside the SYSAUX tablespace (namely the unified audit trail objects and the scheduler job objects) that could explain such a delay in execution while having such a huge disk access (reads). Thank you very much, hugo
Categories: DBA Blogs

High db block gets for inserting into reference partitioned table

Thu, 2024-02-15 01:46
Hello Tom, Could you please advise why I'm getting so huge difference in db block gets and redo for insert between range and reference partitioned table? Db block gets are like 100x more for reference partitioned table and insert is 2-3 times slower. <code> DB01> create table t1 (id number(19) primary key, ts date) 2 partition by range (ts) interval (numtodsinterval(1, 'DAY')) (partition P0001 values less than (to_date('2024-01-01' ,'YYYY-MM-DD'))); Table created. DB01> DB01> insert into t1 (id, ts) values (1, sysdate); 1 row created. DB01> DB01> DB01> -- range interval DB01> create table t2 (id number(19), t1_id number(19) not null, constraint t2_fk foreign key (t1_id) references t1 (id)) 2 partition by range (t1_id) interval (1) (partition values less than (1)); Table created. DB01> set autotrace trace exp stat DB01> insert into t2 (id, t1_id) select level, 1 from dual connect by level <= 2000000; 2000000 rows created. Execution Plan ---------------------------------------------------------- Plan hash value: 1236776825 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | T2 | | | | |* 2 | CONNECT BY WITHOUT FILTERING| | | | | | 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(LEVEL<=2000000) Statistics ---------------------------------------------------------- 105 recursive calls 51252 db block gets 7237 consistent gets 0 physical reads 147628492 redo size 123 bytes sent via SQL*Net to client 391 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 2000000 rows processed DB01> set autotrace off DB01> commit; Commit complete. DB01> DB01> DB01> -- reference DB01> create table t3 (id number(19), t1_id number(19) not null, constraint t3_fk foreign key (t1_id) references t1 (id)) 2 partition by reference (t3_fk); Table created. DB01> set autotrace trace exp stat DB01> insert into t3 (id, t1_id) select level, 1 from dual connect by level <= 2000000; 2000000 rows created. Execution Plan ---------------------------------------------------------- Plan hash value: 1236776825 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows ...
Categories: DBA Blogs

SQL loader not loading all the needed rows due to new line character and enclosement character

Mon, 2024-02-12 18:06
I have a problem with how SQL loader manage the end of a column value. I was hoping to manage CR LF, the enclosement character and the separator character but it seems I can't find a solution! The data I receive from the .csv file looks like this: <code>"C","I","FLAGS","LASTUPDATEDATE","BOEVERSION","C_OSUSER_UPDATEDBY","I_OSUSER_UPDATEDBY","C_OSUSER_PWF","DESCRIPTION","DURATION","ENDDATE","I_OSUSER_PWF","LASTSTATUSCHA","STARTDATE","DURATIONUNIT","TYPE","STATUS","C_BNFTRGHT_CONDITIONS","I_BNFTRGHT_CONDITIONS","C_CNTRCT1_CONDITION","I_CNTRCT1_CONDITION","EXTBLOCKTYPE","EXTBLOCKDURATIONUNIT","EXTBLOCKDURATION","EXTBLOCKDESCRIPTION","PARTITIONID" "7680","423","PE","2015-07-06 11:42:10","0","1000","1506","","No benefits are payable for a Total Disability period during a Parental or Family-Related Leave, for a Total Disability occurring during this period. ","0","","","","","69280000","69312015","71328000","7285","402","","","","","","","1" "7680","426","PE","2015-07-06 11:42:10","0","1000","1506","","""Means to be admitted to a Hospital as an in-patient for more than 18 consecutive hours. "" ","0","","","","","69280000","69312021","71328000","7285","402","","","","","","","1"</code> My ctl file is as follows: <code>Load Data infile 'C:\2020-07-29-03-04-48-TolCondition.csv' CONTINUEIF LAST != '"' into table TolCondition REPLACE FIELDS TERMINATED BY "," ENCLOSED by '"' ( C, I, FLAGS, LASTUPDATEDATE DATE "YYYY-MM-DD HH24:MI:SS", BOEVERSION, C_OSUSER_UPDATEDBY, I_OSUSER_UPDATEDBY, C_OSUSER_PWF, DESCRIPTION CHAR(1000), DURATION, ENDDATE DATE "YYYY-MM-DD HH24:MI:SS", I_OSUSER_PWF, LASTSTATUSCHA DATE "YYYY-MM-DD HH24:MI:SS", STARTDATE DATE "YYYY-MM-DD HH24:MI:SS", DURATIONUNIT, TYPE, STATUS, C_BNFTRGHT_CONDITIONS, I_BNFTRGHT_CONDITIONS, C_CNTRCT1_CONDITION, I_CNTRCT1_CONDITION, EXTBLOCKTYPE, EXTBLOCKDURATIONUNIT, EXTBLOCKDURATION, EXTBLOCKDESCRIPTION, PARTITIONID)</code> Here is what I tried in the control file: CONTINUEIF LAST != '"' CONTINUEIF THIS PRESERVE (1:2) != '",' "str X'220D0A'" Here is the result I currently have with "CONTINUEIF LAST != '"' <code>Record 2: Rejected - Error on table FNA_FNTFO2.TOLCONDITION, column DESCRIPTION. second enclosure string not present Record 3: Rejected - Error on table FNA_FNTFO2.TOLCONDITION, column C. no terminator found after TERMINATED and ENCLOSED field Table FNA_FNTFO2.TOLCONDITION: 1 Row successfully loaded. 2 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.</code> Is there any way to manage line break and enclosement character in SQL Loader? I dont understand why we can`t change how it sees rows. Instead of seeing a new row when there is a CR LF, can we tell it to concacenate values until the last enclosement character (chr34 in my case) + the separator character (y, in my case) has been seen. I really ho...
Categories: DBA Blogs

Pages