Tom Kyte

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

Fetch and pga

10 hours 45 min ago
I run in sqlplus a query with huge result set. It has to return million records Set arraysize to 5000 Monitoring v$session, v$process, v$sqlstats while query is running I can see after some minutes executions changes from 0 to 1. Then fetch count begin to be incremented at each stats read cycle and so is doing rows processed count (row counter is incremented by multiple of 5000) At this point I expected fetched rows to be unloaded, step by step, from process pga but I'm surprised to see that process pga grows continuously. So why process is still keeping in memory rows that are already been fetched ? No way to free pga step by step after each fetch ?
Categories: DBA Blogs

I want to compate Table A with Table B store difference in C and update A To B

10 hours 45 min ago
I need to compare 2 table A and B I want to store the difference in C and after I want to update B to A. The B table could be updated every second so , I wondering what is the right approach to ensure when I update B to A I m using same value as when I compare B and A Hope this is clear enough !!!
Categories: DBA Blogs

Optimal ways to Capture Data Changes in Oracle 19c

10 hours 45 min ago
Hi Tom, Greetings, we have a requirement to capture data changes for multiple tables and push to Kafka topic in the following format similar to GoldenGate provides. "op_type":"Insert or Update","op_ts": "timestamp","current_ts": "timestamp", "before" :"Row Data before change", "after" :"Row Data after change" Since we are not able to use GG at this point in time, exploring combination of PL/SQL and java based solution to push the data to Kafka topic. Please suggest an optimal solution to capture data changes in Oracle 19c. Thanks in advance!
Categories: DBA Blogs

"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

Pages