<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Yaro Perventsev on Medium]]></title>
        <description><![CDATA[Stories by Yaro Perventsev on Medium]]></description>
        <link>https://medium.com/@yaroperventsev?source=rss-5feaf78a15c7------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*amW7TQgw2oKjEaHrV_UPHg.png</url>
            <title>Stories by Yaro Perventsev on Medium</title>
            <link>https://medium.com/@yaroperventsev?source=rss-5feaf78a15c7------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 07:01:37 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@yaroperventsev/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[ORA-03113 After the Connection Sat Idle: The Firewall Forgot It]]></title>
            <link>https://medium.com/@yaroperventsev/ora-03113-after-the-connection-sat-idle-the-firewall-forgot-it-050d4beaa37d?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/050d4beaa37d</guid>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[networking]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Sat, 07 Nov 2026 06:00:30 GMT</pubDate>
            <atom:updated>2026-11-14T15:02:02.290Z</atom:updated>
            <content:encoded><![CDATA[<p>The application works all day. Then the first request after a quiet spell fails: the first one in the morning, the first one after lunch, the first one on a rarely used screen. Retry it, and it works.</p><pre>ORA-03113: end-of-file on communication channel</pre><p>or</p><pre>ORA-03135: connection lost contact</pre><p>The database is fine. No other session noticed anything. The cause is a device between the application and the database: a firewall, a NAT gateway, a cloud load balancer. It tracks every TCP connection that passes through it, and it forgets the ones that stay idle longer than its timeout. A connection pool is designed to keep connections open and idle. The two meet, and the next query on a forgotten connection goes nowhere.</p><p>This post covers which error you get and why, what the database is left holding, the one setting that fixes it, and the settings that look like fixes and aren’t. It ends with a lab that puts a firewall between a client and Oracle Database Free 26ai and proves each case.</p><h3>What the device forgets</h3><p>A stateful firewall, a NAT gateway or a load balancer keeps a table of the TCP connections it lets through. An entry stays as long as packets keep flowing. When a connection sends nothing for longer than the device’s idle timeout, the entry is removed. Nothing is sent to either end. The client and the database both still think the connection is open.</p><p>The defaults are short compared with how long a pooled connection can sit idle:</p><ul><li><a href="https://learn.microsoft.com/en-us/azure/load-balancer/load-balancer-tcp-reset">Azure Load Balancer</a>: 4 minutes (configurable 4 to 100); the next packet is dropped silently, unless “TCP reset on idle” is turned on, which resets both ends.</li><li><a href="https://learn.microsoft.com/en-us/azure/nat-gateway/nat-gateway-resource">Azure NAT Gateway</a>: 4 minutes (configurable 4 to 120); the next packet is answered with a reset, toward the side that sent it.</li><li><a href="https://docs.aws.amazon.com/elasticloadbalancing/latest/network/network-load-balancers.html">AWS Network Load Balancer</a>: 350 seconds (configurable 60 to 6,000 for TCP listeners); the next packet is answered with a reset to the client.</li></ul><p>On-premises firewalls have their own timeouts, often an hour or more, and they’re usually set by a network team you’ll have to ask. Check each provider’s current documentation for your setup; these are the documented defaults at the time of writing.</p><h3>Which error, and why</h3><p>What the application sees depends on what the device does with the first packet of a forgotten connection.</p><p><strong>The device sends a reset.</strong> The client’s next query gets a TCP reset straight back, and fails at once. In the lab, on a 26ai client:</p><pre>ORA-03135: connection lost contact<br>Process ID: 333<br>Session ID: 51 Serial number: 3605</pre><p><strong>The device drops the packet silently.</strong> The client sends the query and gets no answer. Its TCP stack retransmits, waiting longer each time, until it gives up. On Linux that’s controlled by net.ipv4.tcp_retries2, 15 by default. In the lab, with the default, the query hung for <strong>951 seconds</strong>, almost 16 minutes, and then failed:</p><pre>ORA-03113: end-of-file on communication channel<br>Process ID: 377<br>Session ID: 213 Serial number: 13151</pre><p>A request that hangs for 16 minutes is often worse than one that fails. Threads and pool connections pile up behind it, and the application’s own timeouts may fire first, so the application log can show a generic timeout and no ORA- error at all.</p><p>So: an immediate ORA-03135 after a quiet period points to a device that resets. A long hang, then ORA-03113, points to one that drops silently. Either way the connection was idle longer than something on the path allows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/410/0*rNY8CmpBHcCq8tLj" /></figure><p><em>A pooled connection sits idle past the device’s timeout. The device forgets it. If it resets the next packet, the client gets ORA-03135 at once; if it drops it silently, the client hangs until TCP gives up and then gets ORA-03113. SQLNET.EXPIRE_TIME below the timeout keeps the entry alive.</em></p><h3>What the database is left holding</h3><p>The database often never finds out. In the lab, in both the reset case and the silent case, the client’s session stayed in V$SESSION as INACTIVE after the client had given up on it. The reset went only to the client, and in the silent case nothing reached the database at all. (An Azure Load Balancer with TCP reset turned on resets both ends, so there the database does find out.)</p><p>Those orphan sessions are real sessions. They count against SESSIONS and PROCESSES, and if the application had an open transaction, they hold its locks. A pool that replaces each broken connection with a new one leaves one orphan behind per failure.</p><p>The same happens when the client host itself disappears: a VM that’s deleted, a network that’s cut. In the lab, with no keepalive configured, the session of a client whose network was disconnected was still there after 240 seconds, when the drill stopped checking, and orphans from earlier tests stayed for more than 30 minutes. Nothing was ever going to remove them.</p><p>To look for them, find sessions idle for longer than the device’s timeout, from the application’s hosts:</p><pre>SELECT sid, serial#, username, machine, program, status,<br>       ROUND(last_call_et / 60) AS idle_minutes<br>FROM   v$session<br>WHERE  type = &#39;USER&#39;<br>AND    status = &#39;INACTIVE&#39;<br>AND    last_call_et &gt; 240          -- the idle timeout on the path, in seconds<br>ORDER  BY last_call_et DESC;</pre><p>A pool’s healthy idle connections show up here too, so this is a list to compare, not a list to kill. The signal is many more sessions from an app server than its pool can hold, or sessions from a host that no longer exists.</p><p>The alert log doesn’t help with the client’s errors: in the lab they left nothing there. The only network entries it had were two Fatal NI connect error 12170 entries with TNS-12535: TNS:operation timed out. Those are the database&#39;s own keepalive giving up on dead connections, which happens once SQLNET.EXPIRE_TIME is set.</p><h3>The fix: SQLNET.EXPIRE_TIME on the server</h3><p>SQLNET.EXPIRE_TIME goes in the <strong>database server&#39;s</strong> sqlnet.ora, and its value is in <strong>minutes</strong>:</p><pre>SQLNET.EXPIRE_TIME = 1</pre><p>Where the operating system supports it, Oracle Net uses it to set TCP keepalive on the server side of each connection: after that many minutes of idle time, the server sends a keepalive probe. The probe is traffic, so the firewall, the NAT gateway and the load balancer all see the connection as active and keep their entry. If the client really is gone, the probes go unanswered and the database closes the session itself.</p><p>In the lab, with a firewall that forgot connections after 90 seconds:</p><ul><li>SQLNET.EXPIRE_TIME = 1 (60 seconds): a connection idle for 110 seconds ran its next query normally.</li><li>A client whose network was cut: the database removed its session after about 115 seconds (112 to 117 across runs), instead of never.</li></ul><p>Three rules for using it:</p><ol><li><strong>It must be below the smallest idle timeout on the path.</strong> Behind an Azure Load Balancer or NAT gateway at the 4-minute default, use 1 to 3. Behind an AWS NLB at 350 seconds, up to 5. If you don’t know every device on the path, 1 or 2 is a safe choice; the probes are tiny.</li><li><strong>It applies to new connections.</strong> Server processes read sqlnet.ora when they start. Connections that are already open keep what they had, so recycle the pool after the change.</li><li><strong>It needs no listener restart</strong>, for the same reason: each new connection reads the file.</li></ol><h3>Fixes from the client side</h3><p>When you can’t change the database server’s sqlnet.ora, there are two client-side options. Both worked in the lab; both have a catch.</p><p><strong>(ENABLE=BROKEN) in the connect descriptor</strong> turns on TCP keepalive on the client&#39;s socket:</p><pre>(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=app)))</pre><p>On its own, it didn’t help in the lab. It switches keepalive on, but the operating system decides when the first probe goes out, and the Linux default (net.ipv4.tcp_keepalive_time) is 7,200 seconds: two hours. With the client host&#39;s keepalive time set to 60 seconds, the connection survived. That&#39;s a kernel setting for the whole host, so it&#39;s a conversation with whoever owns the app servers.</p><p><strong>SQLNET.EXPIRE_TIME in the client&#39;s </strong><strong>sqlnet.ora</strong> also worked in the lab, where the client is 26ai. The <a href="https://docs.oracle.com/en/database/oracle/oracle-database/26/netrf/parameters-for-the-sqlnet.ora.html">26ai Net Services Reference</a> says the parameter is meant primarily for the server but can be used on clients too. I tested only a 26ai client, so check an older one before relying on it.</p><p>The application’s pool can help too. Most pools can retire connections that have been idle for longer than a set time, or validate a connection before handing it out. A maximum idle time below the device’s timeout avoids the problem. Validation on borrow doesn’t avoid it: it finds the broken connection and replaces it, which is better than failing a request, but against a device that drops silently the validation itself can hang, and each replaced connection can leave an orphan session behind. I didn’t test pool settings in the lab.</p><h3>What doesn’t fix it</h3><p><strong>SQLNET.EXPIRE_TIME higher than the timeout.</strong> It&#39;s the most common one. Oracle&#39;s own <a href="https://docs.oracle.com/en/database/oracle/oracle-database/26/netrf/parameters-for-the-sqlnet.ora.html">reference</a> gives 10 as the recommended value, and 10 does nothing behind a 4-minute load balancer. In the lab, EXPIRE_TIME = 2 (120 seconds) behind the 90-second firewall failed exactly like no setting at all: the first probe came after the firewall had already forgotten the connection.</p><p><strong>(ENABLE=BROKEN) alone.</strong> See above: with the OS default, the first keepalive comes after two hours.</p><p><strong>SQLNET.RECV_TIMEOUT on the client.</strong> It limits how long the client waits for an answer. With it set to 30 seconds, the silent drop in the lab ended in a different error after about 60 seconds (60 to 61 across runs, about two timeouts) instead of 16 minutes:</p><pre>ORA-12609: TNS: Receive timeout occurred</pre><p>That’s better than a 16-minute hang, and worth having, but the connection is still broken and the request still fails. It shortens the failure; it doesn’t prevent it.</p><p><strong>Raising the device’s idle timeout.</strong> It works until a connection sits idle a little longer than the new value. It also costs something: Microsoft’s NAT gateway documentation warns that longer idle timeouts hold SNAT ports longer and raise the risk of running out of them. And it only fixes the device you changed, not the next one someone adds to the path.</p><h3>Prove it</h3><p>The lab runs Oracle Database Free in a container. There’s no firewall box: the database container’s network namespace gets connection tracking that forgets an idle TCP connection after 90 seconds, and one iptables rule that drops, or answers with a reset, any packet of a connection it has forgotten. Each client runs in its own container, runs a query, sits idle for 110 seconds, then runs a second query.</p><pre>drill     case                                                   second query<br>reset     firewall sends a reset                                 ORA-03135 at once; the session stays INACTIVE<br>drop      firewall drops silently, client TCP retries cut to 5   ORA-03113 after ~14 s; the session stays INACTIVE<br>          same, SQLNET.RECV_TIMEOUT = 30 in the client           ORA-12609 after ~60 s<br>fix       SQLNET.EXPIRE_TIME = 1 on the server                   runs<br>toolate   SQLNET.EXPIRE_TIME = 2 on the server                   ORA-03113<br>client    (ENABLE=BROKEN), OS keepalive time 7,200 s             ORA-03113<br>          (ENABLE=BROKEN), OS keepalive time 60 s                runs<br>          SQLNET.EXPIRE_TIME = 1 in the 26ai client&#39;s sqlnet.ora runs<br>dead      client network cut, no EXPIRE_TIME                     session still there after 240 s<br>          client network cut, EXPIRE_TIME = 1                    session removed after ~115 s</pre><p>The drills cut the client’s TCP retries from 15 to 5 so a silent drop fails in seconds instead of 16 minutes. That changes how long the hang lasts, not which error you get. The 951-second hang with the Linux default was measured once by hand.</p><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/idle-connection-drop"><em>idle-connection-drop lab</em></a><em> </em><strong><em>asserts</em></strong><em> every row: the error for each firewall mode, and that the database still has the session afterwards; ORA-12609 with </em><em>RECV_TIMEOUT; the second query running with </em><em>EXPIRE_TIME = 1 and failing with 2; </em><em>ENABLE=BROKEN failing on its own and working with a 60-second keepalive time; client-side </em><em>EXPIRE_TIME working; and a vanished client&#39;s session kept without </em><em>EXPIRE_TIME and removed with it. If any of that doesn&#39;t reproduce, the run fails. Proven on every CI push.</em></blockquote><h3>What teams get wrong</h3><ul><li><strong>Treating ORA-03113 as a database crash.</strong> It can be one, but when it comes after idle time, from one application, with nothing in the alert log, it’s the network path.</li><li><strong>Setting </strong><strong>SQLNET.EXPIRE_TIME without knowing the timeout.</strong> It&#39;s in minutes and it has to be below the smallest idle timeout on the path. 10 doesn&#39;t work behind a 4-minute load balancer.</li><li><strong>Expecting the change to fix open connections.</strong> It applies to new ones. Recycle the pool.</li><li><strong>Trusting </strong><strong>(ENABLE=BROKEN) on its own.</strong> Without a shorter OS keepalive time, the first probe goes out after two hours.</li><li><strong>Calling </strong><strong>RECV_TIMEOUT the fix.</strong> It makes the failure faster, not less likely.</li><li><strong>Ignoring the orphans.</strong> Every failed connection can leave a session on the database. Without EXPIRE_TIME, nothing removes it.</li></ul><h3>Frequently asked questions</h3><p><strong>What causes ORA-03113 end-of-file on communication channel after a connection has been idle?</strong></p><p>Usually a stateful device between the application and the database, such as a firewall, NAT gateway or cloud load balancer, that removed the idle connection from its table. When the application uses the connection again, the device drops the packets silently, the client’s TCP stack retransmits until it gives up, and the client reports ORA-03113. In the companion lab, with Linux’s default TCP retries, that took 951 seconds. The database itself is fine.</p><p><strong>What is the difference between ORA-03113 and ORA-03135?</strong></p><p>Both mean the client lost its connection to the database. In the companion lab on a 26ai client, a device that answered the forgotten connection with a TCP reset produced ORA-03135 connection lost contact at once, and a device that dropped the packets silently produced a long hang followed by ORA-03113 end-of-file on communication channel. So an immediate ORA-03135 points to a reset, and a long hang followed by ORA-03113 points to a silent drop.</p><p><strong>What should SQLNET.EXPIRE_TIME be set to?</strong></p><p>A number of minutes below the smallest idle timeout of any firewall, NAT gateway or load balancer between the application and the database. Azure Load Balancer and Azure NAT Gateway default to 4 minutes, so use 1 to 3; an AWS Network Load Balancer defaults to 350 seconds, so up to 5. In the companion lab, behind a 90-second timeout, EXPIRE_TIME = 1 kept idle connections working and EXPIRE_TIME = 2 did not.</p><p><strong>Where do I set SQLNET.EXPIRE_TIME, and does it need a restart?</strong></p><p>In the database server’s sqlnet.ora. It applies to connections made after the change, because each server process reads the file when it starts; connections that are already open are not affected, so recycle the application’s connection pool. No listener or database restart is needed.</p><p><strong>Does ENABLE=BROKEN fix idle connection timeouts?</strong></p><p>Not on its own. (ENABLE=BROKEN) in the connect descriptor turns on TCP keepalive on the client’s socket, but the operating system decides when the first keepalive is sent, and the Linux default is 7,200 seconds. In the companion lab it failed on its own and worked when the client host’s net.ipv4.tcp_keepalive_time was set to 60 seconds.</p><p><strong>Why are there INACTIVE sessions left on the database after ORA-03113 or ORA-03135?</strong></p><p>Because the database never learned that the client was gone. In the companion lab, the reset went only to the client and the silent drop reached neither end, so the session stayed in V$SESSION as INACTIVE. It holds a process and any locks of an open transaction. With SQLNET.EXPIRE_TIME set, the database’s keepalive probes go unanswered and the database removes such a session itself; in the lab that took about 115 seconds with EXPIRE_TIME = 1.</p><p>ORA-03113 after idle time isn’t a database fault. It’s a connection that sat quiet longer than something on the network allows. Find the smallest idle timeout on the path, set SQLNET.EXPIRE_TIME on the database server below it, and recycle the pool. If clients can&#39;t reach the database at all, rather than losing it after a while, that&#39;s a different problem: start with <a href="https://uptimearchitect.com/blog/oracle-ora-12514-listener-service/">ORA-12514 and the listener</a>. Then prove it with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/idle-connection-drop">idle-connection-drop lab</a>, where a 90-second firewall breaks every idle connection and one line in sqlnet.ora keeps them alive.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-ora-03113-idle-connection-firewall/"><em>https://uptimearchitect.com</em></a><em> on November 7, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=050d4beaa37d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ORA-01000 Maximum Open Cursors Exceeded: Don’t Raise the Limit, Find the Leak]]></title>
            <link>https://levelup.gitconnected.com/ora-01000-maximum-open-cursors-exceeded-dont-raise-the-limit-find-the-leak-9d91a48bed93?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/9d91a48bed93</guid>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[java]]></category>
            <category><![CDATA[database-performance]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Sun, 01 Nov 2026 05:00:52 GMT</pubDate>
            <atom:updated>2026-11-13T17:21:36.936Z</atom:updated>
            <content:encoded><![CDATA[<p>The application has been fine for days. Then one request fails, then a few more, all from the same app server:</p><pre>ORA-01000: maximum open cursors exceeded</pre><p>The usual reaction is to raise OPEN_CURSORS. It&#39;s one ALTER SYSTEM, it needs no restart, and the errors stop. Then they come back a week later.</p><p>ORA-01000 is almost never a limit that’s too low. It’s an application that opens cursors and doesn’t close them: a <strong>cursor leak</strong>. The limit is just where the leak becomes visible. This post covers what the limit counts, how to find the leaking session and the exact statement, the query that makes a healthy session look like the culprit, and why raising the limit doesn’t help the connections that are already failing. It ends with a lab that proves each step on Oracle Database Free 26ai.</p><h3>What the limit counts</h3><p>OPEN_CURSORS is the maximum number of cursors <strong>one session</strong> can have open at once. It is not a database-wide pool. A session at the limit gets ORA-01000; every other session carries on. On 26ai the message reads:</p><pre>ORA-01000: maximum open cursors for session exceeded</pre><p>A cursor is the server-side handle for a statement the client is still working with. In application code it’s a Statement or ResultSet in JDBC, a cursor object in Python, a data reader in .NET. It stays open on the server until the client closes it, or the session ends. If the code never closes it, it stays open for as long as the connection lives. With a connection pool, that can be days.</p><p>That’s why ORA-01000 shows up late. A code path that leaks one cursor per call is harmless on the first call. Each pooled connection collects its leaked cursors over hundreds or thousands of requests, and fails when it reaches the limit. The request that fails is just the one that needed the next cursor.</p><p>The parameter’s documented default is 50. Many databases run with something higher; the container in this post’s lab runs with 300.</p><h3>What the error tells you on 26ai</h3><p>On 26ai the first ORA-01000 in a session carries a clue:</p><pre>ORA-01000: maximum open cursors for session exceeded Potential Leaked SQL_ID:<br>4cgc7syjwjw3y</pre><p>In the lab, that SQL_ID was the statement the session held open the most, and <strong>not</strong> the statement that failed. A session leaked 298 result sets from one procedure and then called a different one. The different call failed, and the message named the <strong>leaked</strong> statement. In a second test the session leaked 200 result sets from one statement, then 98 from another, and the message still named the one with 200. That’s what you want: the application log now points at the code that leaks, not the innocent request that happened to need the next cursor.</p><p>Two things to know:</p><ul><li><strong>Only the first error in the session names it.</strong> In the lab the next 21 errors in the same session said only “maximum open cursors for session exceeded”. Find the first one in the application log.</li><li><strong>It may be wrapped.</strong> When the failing statement needs internal SQL first (parsing a new statement, for example), the application sees ORA-00604: Error occurred at recursive SQL level 1 with ORA-01000 underneath. The lab got both forms.</li></ul><p>Look the SQL_ID up in V$SQL (it stays in the shared pool after the session ends, until it ages out), then find that statement in the code.</p><p>The database itself logs nothing. After a run that raised ORA-01000 22 times, the lab found no ORA-01000 in the alert log. If the application swallows its errors, you won’t see this one.</p><h3>Find the session</h3><p>If you don’t have the SQL_ID, or want to confirm it, start with the session. The count that the limit applies to is the statistic opened cursors current:</p><pre>SELECT s.sid, s.serial#, s.username, s.module, s.machine, st.value AS open_cursors<br>FROM   v$sesstat st<br>JOIN   v$statname n ON n.statistic# = st.statistic#<br>JOIN   v$session  s ON s.sid = st.sid<br>WHERE  n.name = &#39;opened cursors current&#39;<br>AND    s.username IS NOT NULL<br>ORDER  BY st.value DESC<br>FETCH FIRST 10 ROWS ONLY;</pre><p>A leaking session sits near OPEN_CURSORS. A healthy one holds a handful. MODULE and MACHINE tell you which application and which app server. Run this on a schedule and you&#39;ll see a leak as a number that grows with uptime long before it reaches the limit.</p><h3>Find the statement, and don’t count cached cursors</h3><p>V$OPEN_CURSOR lists cursors per session with their SQL text. It also lists cursors that are <strong>closed</strong> and kept in the session cursor cache so the next parse of the same statement is cheap. Cached cursors don&#39;t count against OPEN_CURSORS. If you count rows in V$OPEN_CURSOR without filtering, a busy, healthy session can look like the problem.</p><p>The lab ran two sessions side by side. One leaked 250 result sets. The other ran 450 different queries four times each, closing every one, with SESSION_CACHED_CURSORS set to 1,000:</p><pre>session      opened cursors current    rows in V$OPEN_CURSOR<br>leaky-app    252                       265<br>busy-app     2                         458</pre><p>By raw row count, the healthy session has more “open cursors” than the leaking one, and more than the 300 limit. It had two open. 454 of its rows were SESSION CURSOR CACHED. (The lab set the cache to 1,000 to make the point; at the default of 50, expect a few dozen extra rows per session. Some shops set it much higher.)</p><p>The CURSOR_TYPE column separates them. Filter on the types that start with OPEN, and group by statement:</p><pre>SELECT o.sql_id, MAX(o.sql_text) AS sql_text, COUNT(*) AS open_count<br>FROM   v$open_cursor o<br>WHERE  o.sid = :sid<br>AND    o.cursor_type LIKE &#39;OPEN%&#39;<br>GROUP  BY o.sql_id<br>ORDER  BY open_count DESC;</pre><p>In the lab that returned a single row:</p><pre>SQL_ID         OPEN_COUNT  SQL_TEXT<br>4cgc7syjwjw3y  250         SELECT ID, CREATED FROM APP.ORDERS WHERE CUSTOMER_ID = :B1</pre><p>One statement open 250 times in one session is a leak. Nothing legitimate holds the same query’s result open 250 times at once. If instead you see many <em>different</em> statements, each open a few times, the session may genuinely need more cursors (see the sizing section below).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/431/0*EObmcRjKWckU8I6H" /></figure><p><em>ORA-01000 is a per-session limit. Find the session by ‘opened cursors current’, then the statement by V$OPEN_CURSOR filtered to CURSOR_TYPE LIKE ‘OPEN%’. One statement open hundreds of times is a leak to fix in code; many different statements may be a real need.</em></p><h3>Raising OPEN_CURSORS: what it does and doesn’t do</h3><p>OPEN_CURSORS can be changed with ALTER SYSTEM, without a restart, and in a PDB. The lab raised it from 300 to 400 in the PDB while one session was stuck at the limit:</p><pre>session                                         result after the raise<br>stuck at the limit before the raise             55 more calls, 55 x ORA-01000<br>connected before the raise, leaking after it    first ORA-01000 at call 299 (the old limit)<br>connected after the raise, same leak            first ORA-01000 at call 399</pre><p>Two things went wrong with the “fix”:</p><ol><li><strong>It didn’t reach the connections that were already open.</strong> On 26ai in the lab, every session connected before the change kept the old limit, stuck or not. Only new connections got 400. In a pooled application, that means the raise does nothing until the pool reconnects. When the errors stop after a raise, it’s often the app restart that went with it.</li><li><strong>The leak failed again, just later.</strong> The new connection hit ORA-01000 at call 399 instead of 299. The leak rate didn’t change.</li></ol><p>So raising the limit is a way to buy time while the code is fixed, and it only buys it after the connections are recycled. Each open cursor also holds memory on the server, so a high limit on a leaking app means more memory held before it fails.</p><h3>The fix</h3><p><strong>Right now:</strong> the leaked cursors belong to the session. Ending the session releases all of them. Recycle the affected pool’s connections or restart that app server. Capture opened cursors current and the session&#39;s V$OPEN_CURSOR rows first, because they&#39;re gone when the session is.</p><p><strong>In the code:</strong> close what you open, on every path, including the error path. That means try-with-resources in Java for every Statement, PreparedStatement, CallableStatement and ResultSet, including result sets returned as ref cursors from stored procedures. In Python, use with blocks for cursors; in .NET, using. In PL/SQL, close explicit cursors and DBMS_SQL cursors in the exception handler too. The leak in the lab is a stored procedure that returns a result set to the caller (a SYS_REFCURSOR). The caller owns that cursor, and the caller never closed it. The same 400 calls with each result set read and closed ran with no error, at the original limit of 300.</p><h3>When the limit really is too low</h3><p>Some sessions do need more than the default: an application that keeps many statements prepared at once, or a client-side statement cache. Oracle’s JDBC driver, for example, can cache statements per connection, and each cached statement keeps its cursor open on the server. That’s by design, and the cursors are spread over many different statements, not one.</p><p>Size OPEN_CURSORS from what the application uses at peak, not from the last error:</p><pre>SELECT s.module, MAX(st.value) AS peak_open_now<br>FROM   v$sesstat st<br>JOIN   v$statname n ON n.statistic# = st.statistic#<br>JOIN   v$session  s ON s.sid = st.sid<br>WHERE  n.name = &#39;opened cursors current&#39;<br>AND    s.username IS NOT NULL<br>GROUP  BY s.module<br>ORDER  BY peak_open_now DESC;</pre><p>Run it over a normal business day. If the healthy peak is close to the limit, raise it with headroom and recycle the connections. If one module keeps climbing, it’s a leak, and a higher limit only delays the next outage.</p><h3>Prove it</h3><p>The lab runs Oracle Database Free in a container. The “application” is SQL*Plus calling a procedure that returns a result set, and holding each result set in its own variable without closing it: the same thing a ResultSet that&#39;s never closed does on the server. Then it breaks and fixes things five ways:</p><pre>drill   what happens                                             result<br>leak    320 calls, no result set closed (OPEN_CURSORS 300)       ORA-01000 from call 299; first error names the SQL_ID<br>blame   298 leaked, then a different call                        the other call fails, message names the leaker;<br>                                                                 another session runs that call fine<br>find    a leaking session next to a busy healthy one             raw V$OPEN_CURSOR rows: 265 vs 458;<br>                                                                 &#39;OPEN%&#39; filter finds the leak, 250 x one SQL<br>raise   OPEN_CURSORS 300 -&gt; 400 during the outage                stuck and pre-existing sessions keep 300; new: call 399<br>fix     400 calls, each result set closed                        no ORA-01000</pre><p>Why call 299 and not 301? The session already had two cursors open before the first call: SQL*Plus’s own statement and one recursive cursor. 298 leaked plus those two is 300.</p><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/open-cursors"><em>open-cursors lab</em></a><em> </em><strong><em>asserts</em></strong><em> every row: the failing call number from the limit; that the first error, and only the first, names the leaked statement’s SQL_ID; nothing in the alert log; the leaked statement named even when a different call fails, and a second session unaffected; the healthy session’s </em><em>V$OPEN_CURSOR row count above the limit while it has almost nothing open; the leaked statement found 250 times by the </em><em>OPEN% filter; both sessions opened before the raise keeping the old limit and a new one failing at 399; and no error once each result set is closed. If any of that doesn&#39;t reproduce, the run fails. Proven on every CI push.</em></blockquote><h3>What teams get wrong</h3><ul><li><strong>Raising </strong><strong>OPEN_CURSORS as the fix.</strong> It moves the failure, and only for new connections. Find the statement first.</li><li><strong>Counting rows in </strong><strong>V$OPEN_CURSOR.</strong> That includes cached cursors, which are closed. Use opened cursors current, or filter CURSOR_TYPE LIKE &#39;OPEN%&#39;.</li><li><strong>Blaming the statement that failed.</strong> It’s whichever call needed the next cursor. On 26ai, read the SQL_ID in the first error; on any release, look at what the session holds open.</li><li><strong>Closing the </strong><strong>ResultSet on the happy path only.</strong> An exception between open and close leaks it just the same. Close in finally, or let try-with-resources do it.</li><li><strong>Treating ORA-01000 as a database problem.</strong> It’s per session, the database logs nothing, and every other session is fine. The fix is in the application.</li></ul><h3>Frequently asked questions</h3><p><strong>What does ORA-01000 maximum open cursors exceeded mean?</strong></p><p>It means one database session tried to open more cursors at the same time than the OPEN_CURSORS parameter allows. The limit is per session, not for the whole database, so other sessions keep working. It is almost always caused by a cursor leak: the application opens statements or result sets and doesn’t close them, so each connection accumulates open cursors until it reaches the limit. Less often, the application genuinely needs more cursors at once than the limit allows.</p><p><strong>How do I find which SQL is causing ORA-01000?</strong></p><p>On Oracle Database 26ai the first ORA-01000 in a session includes ‘Potential Leaked SQL_ID’ followed by the statement the session holds open most; look it up in V$SQL. On any release, find the session with the highest ‘opened cursors current’ in V$SESSTAT, then query V$OPEN_CURSOR for that SID with CURSOR_TYPE LIKE ‘OPEN%’, grouped by SQL_ID. One statement open hundreds of times is the leak. In the companion lab that returned one statement open 250 times.</p><p><strong>Should I just increase OPEN_CURSORS?</strong></p><p>Only if the application genuinely needs more cursors at once, which shows up as many different statements each open a few times. For a leak, raising the limit only delays the error: in the companion lab, raising it from 300 to 400 moved the first ORA-01000 from call 299 to call 399. And the new value applied only to new connections; sessions connected before the change kept the old limit, so a connection pool has to reconnect before a raise has any effect.</p><p><strong>Why does V$OPEN_CURSOR show more cursors than OPEN_CURSORS without an error?</strong></p><p>Because V$OPEN_CURSOR also lists cursors that are closed and kept in the session cursor cache (CURSOR_TYPE ‘SESSION CURSOR CACHED’), and cached cursors don’t count against OPEN_CURSORS. In the companion lab a healthy session with SESSION_CACHED_CURSORS set to 1,000 showed 458 rows in V$OPEN_CURSOR against a limit of 300, while its ‘opened cursors current’ was 2. Filter on CURSOR_TYPE LIKE ‘OPEN%’ or use the ‘opened cursors current’ statistic.</p><p><strong>Does increasing OPEN_CURSORS require a restart?</strong></p><p>No. OPEN_CURSORS can be changed with ALTER SYSTEM while the database is running, and in a PDB. But in the companion lab on 26ai the new value applied only to sessions that connected after the change; sessions that were already connected, including the one stuck at the limit, kept the old limit. Recycle the application’s connections after changing it.</p><p><strong>How do I fix a cursor leak in Java?</strong></p><p>Close every Statement, PreparedStatement, CallableStatement and ResultSet on every path, including when an exception is thrown. try-with-resources does this automatically. Result sets returned from stored procedures as ref cursors are cursors too and must be closed by the caller. To release leaked cursors immediately, recycle the affected connections; ending a session closes all of its cursors.</p><p>ORA-01000 is a leak that took days to fill up, reported by whichever request came last. Before you raise anything, find the session by opened cursors current, find the statement by V$OPEN_CURSOR with the cached cursors filtered out, and fix the code that opens it. It&#39;s the same family of problem as <a href="https://uptimearchitect.com/blog/oracle-bind-variables-hard-parse/">the hard-parse storm</a>: application habits showing up as a database error. Then prove it with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/open-cursors">open-cursors lab</a>, where one leaked result set per call takes a session down at call 299, and closing it runs 400 calls clean.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-ora-01000-maximum-open-cursors/"><em>https://uptimearchitect.com</em></a><em> on November 1, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9d91a48bed93" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/ora-01000-maximum-open-cursors-exceeded-dont-raise-the-limit-find-the-leak-9d91a48bed93">ORA-01000 Maximum Open Cursors Exceeded: Don’t Raise the Limit, Find the Leak</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SYSDATE Is Hours Off After the Move to the Cloud. Nothing Errors.]]></title>
            <link>https://levelup.gitconnected.com/sysdate-is-hours-off-after-the-move-to-the-cloud-nothing-errors-c5fdafbc79a7?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/c5fdafbc79a7</guid>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[cloud-computing]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Sat, 31 Oct 2026 05:00:52 GMT</pubDate>
            <atom:updated>2026-11-09T16:23:53.866Z</atom:updated>
            <content:encoded><![CDATA[<p>The migration went well. The database moved from the server room to the cloud over the weekend, the row counts matched, the application connected, and nobody saw an error. On Monday afternoon someone asks why the “today’s orders” report has stopped showing the evening’s orders. On Tuesday the nightly export picks up rows from the wrong day. The audit log and the application log disagree by five hours.</p><p>Nothing is broken, and that’s why it takes days to find. Every SYSDATE in the database now returns <strong>UTC</strong>, and every column with DEFAULT SYSDATE is filling with UTC times, in the same column as years of local times.</p><p>SYSDATE doesn&#39;t come from the database. It comes from the <strong>operating system&#39;s clock and time zone</strong>, as the database server process sees them. The on-prem server ran in local time. The new host, like most cloud servers and nearly every container image, runs in UTC. This post shows exactly what moves, what doesn&#39;t, the fixes that look right and change nothing, and the two that work. All of it is reproduced in a lab.</p><h3>What moves, and what doesn’t</h3><p>Oracle has several “now” functions, and they don’t all read the same clock:</p><pre>function                                         where its time zone comes from<br>SYSDATE, SYSTIMESTAMP                            the server process&#39;s OS time zone (TZ)<br>CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP  the session time zone, which the client sets<br>DBTIMEZONE                                       a database setting, used for TIMESTAMP WITH LOCAL TIME ZONE columns</pre><p>The first row is the one that matters, because it’s what applications use: DEFAULT SYSDATE on a column, WHERE order_date &gt;= TRUNC(SYSDATE) in a report, SYSDATE - 1 in a purge job. When the database moves to a host in another time zone, all of it moves with the host.</p><p>An order placed at 8pm in Chicago is stored as 1am the <strong>next day</strong> (2am in winter). Every report that groups by TRUNC(order_date) puts the evening&#39;s orders on tomorrow.</p><h3>Prove it</h3><p>The lab runs Oracle Database Free in a container and “moves” it the only way that matters for SYSDATE: it starts the instance with TZ=America/Chicago (the on-prem server), then restarts the same instance in UTC (the cloud host). An application schema has an ORDERS table with ORDER_DATE DATE DEFAULT SYSDATE. Before and after the move, it checks what the application&#39;s session and a scheduler job see, and places one order:</p><pre>                                      on-prem (Chicago)    moved (UTC)<br>app session, SYSDATE                  07:28                12:28<br>SYSDATE offset from UTC               -5 h                 0 h<br>scheduler job, SYSDATE offset         -5 h                 0 h<br> <br>order 1  on-prem         2026-10-31 07:28<br>order 2  in the cloud    2026-10-31 12:28     &lt;- placed seconds later</pre><p>Two orders placed seconds apart are stored <strong>5 hours apart</strong> (6 in winter, when Chicago is UTC-6). The second one is in UTC, in a column full of Chicago times, and nothing in the database records which is which.</p><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/sysdate-timezone"><em>sysdate-timezone lab</em></a><em> </em><strong><em>asserts</em></strong><em> every lab result in this post: the jump to UTC for the app and for a scheduler job, the 5-hour gap, each fix below that doesn’t work, the two that do, and the DST trap. Chicago’s offset is read from the database at run time, so it holds all year. If any of it stops reproducing, the run fails. Proven on every CI push.</em></blockquote><h3>Four fixes that change nothing</h3><p><strong>Setting the database time zone.</strong> The first thing most people try:</p><pre>ALTER DATABASE SET TIME_ZONE = &#39;America/Chicago&#39;;</pre><p>In the lab, after reopening the PDB, DBTIMEZONE is America/Chicago and the application&#39;s SYSDATE is <strong>still UTC</strong>. DBTIMEZONE only applies to TIMESTAMP WITH LOCAL TIME ZONE data. SYSDATE ignores it (unless you turn on the setting in fix 2 below).</p><p><strong>Checking from a DBA shell.</strong> A DBA logs in to the new host, sets TZ=America/Chicago in their shell, runs sqlplus / as sysdba, and sees Chicago time. Fixed? In the lab, at the same moment, the application&#39;s session was still on UTC. A local / as sysdba connection&#39;s server process is started by your sqlplus, so it takes the TZ of <strong>your shell</strong>. The application&#39;s connections come in over the listener and don&#39;t. Always check over the application&#39;s connection: SELECT SYSDATE, SYSTIMESTAMP FROM dual through the same service the app uses.</p><p><strong>Setting </strong><strong>TZ on the listener.</strong> Many runbooks say the listener&#39;s environment is what server processes inherit. On 26ai in the lab, restarting only the listener with TZ=America/Chicago changed nothing; the application&#39;s server processes took the <strong>instance&#39;s</strong> TZ. Older releases may behave differently, and setting it on both is harmless, but the listener alone didn&#39;t do it here.</p><p><strong>Switching the code to </strong><strong>CURRENT_DATE.</strong> CURRENT_DATE uses the session&#39;s time zone, which the <strong>client</strong> sets. With an app server in UTC (and in the cloud, the app servers usually are), it&#39;s UTC too. In the lab it returned Chicago time only when the client set ORA_SDTZ=America/Chicago. That works, but it means every client, batch job and tool has to set it, and the first one that doesn&#39;t writes UTC again.</p><h3>The scheduler is fine. Except for one thing.</h3><p>I expected the 2am jobs to start running at 9pm. They didn’t. DBMS_SCHEDULER stores a time zone with each job (in START_DATE, or the scheduler&#39;s default_timezone, which the database keeps), so after the move the lab&#39;s 2am job was still due at 2am Chicago:</p><pre>NIGHTLY_REGION   next run due 02:00 Chicago (after the move)</pre><p>The trap is a job whose START_DATE was written as a <strong>fixed offset</strong>. SYSTIMESTAMP returns one (-05:00 in summer), and so does any start_date =&gt; SYSTIMESTAMP. A fixed offset doesn&#39;t know about daylight saving time. In the lab, a 2am job stored with -05:00:</p><pre>                           next run, Chicago time<br>                           summer     after the November DST change<br>start_date ... -05:00      02:00      01:00<br>start_date ... Chicago     02:00      02:00</pre><p>It runs an hour early from November to March, every year, whether or not you move the database. Find them:</p><pre>SELECT owner, job_name, TO_CHAR(start_date, &#39;TZR&#39;) AS tz<br>FROM   dba_scheduler_jobs<br>WHERE  REGEXP_LIKE(TO_CHAR(start_date, &#39;TZR&#39;), &#39;^[+-]&#39;);</pre><p>and recreate them with a region name, TO_TIMESTAMP_TZ(&#39;... America/Chicago&#39;, &#39;... TZR&#39;).</p><h3>Fix 1: give the instance its time zone back</h3><p>SYSDATE follows the instance&#39;s TZ, so set TZ in the environment that <strong>starts the instance</strong>, and restart it. In the lab, the instance restarted with TZ=America/Chicago (the listener left in UTC) put the application&#39;s sessions and the scheduler&#39;s jobs back on Chicago time.</p><p>Where that environment lives depends on how the database starts:</p><ul><li><strong>A start script or systemd unit:</strong> Environment=TZ=America/Chicago in the unit, or export TZ=... in the script. Not the DBA&#39;s .bash_profile; the database has to get it when it starts without you.</li><li><strong>Grid Infrastructure / Oracle Restart:</strong> srvctl setenv database -db &lt;db&gt; -env &quot;TZ=America/Chicago&quot;, then restart it with srvctl.</li><li><strong>A container:</strong> TZ in the container&#39;s environment (environment: in Compose, env in Kubernetes).</li><li><strong>A managed database service:</strong> you can’t set the OS environment there. Check the provider’s time zone setting.</li></ul><p>The last three aren’t tested in the lab; the lab sets TZ the way the first does, in the environment of the process that runs STARTUP. It needs an instance restart. It also has a cost: the database server is now the one server in your cloud that doesn&#39;t run in UTC, and everyone who works on it has to know that.</p><h3>Fix 2: let the database answer in its own time zone (26ai)</h3><p>Oracle AI Database 26ai (the release that was 23ai, now version 23.26) added TIME_AT_DBTIMEZONE; Oracle&#39;s reference says it&#39;s available &quot;starting with Oracle AI Database 26ai&quot;. With it set to USER_SQL, SYSDATE and SYSTIMESTAMP in SQL return the time in <strong>DBTIMEZONE</strong>, whatever the OS runs in. The host stays in UTC:</p><pre>ALTER DATABASE SET TIME_ZONE = &#39;America/Chicago&#39;;<br>ALTER SYSTEM SET time_at_dbtimezone = USER_SQL SCOPE = SPFILE;<br>-- then reopen the PDB (or restart the database)</pre><p>In the lab (run in the PDB, then a PDB close and open), the application’s SYSDATE was Chicago time again with the OS still in UTC, so was a scheduler job&#39;s, and a new order&#39;s DEFAULT SYSDATE matched the on-prem one:</p><pre>app sees: SYSDATE=07:28  SYSTIMESTAMP=07:28 AMERICA/CHICAGO   (OS clock: 12:28 UTC)</pre><p>Two things to know before you try it:</p><ul><li><strong>ALTER DATABASE SET TIME_ZONE fails with ORA-30079</strong> while any TIMESTAMP WITH LOCAL TIME ZONE column holds data. In the lab one row in such a column was enough; an empty column didn&#39;t block it. Check first:</li></ul><pre>SELECT owner, table_name, column_name FROM dba_tab_columns<br>WHERE  data_type LIKE &#39;TIMESTAMP(%) WITH LOCAL TIME ZONE&#39; AND owner NOT IN (&#39;SYS&#39;, &#39;SYSTEM&#39;);</pre><ul><li><strong>It’s 26ai.</strong> On 19c, or an older 23ai Release Update without the parameter, you have fix 1. (You may also see SYSDATE_AT_DBTIMEZONE. Oracle documents it for Autonomous Database Serverless only; on the lab&#39;s 26ai Free it can&#39;t be changed, ORA-60490.)</li></ul><p>The parameter is set per PDB and only takes effect at the next open. Oracle also documents a DATABASE value, which applies DBTIMEZONE to all time-dependent operations, not just SYSDATE and SYSTIMESTAMP in SQL. The lab tests USER_SQL only.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/420/0*gRQ3VTTb90MWH5sP" /><figcaption>Which fix fits: the release decides whether the database can keep its own time zone; otherwise the instance has to start in it. Either way, the rows written before the fix need correcting.</figcaption></figure><h3>The rows written in between</h3><p>Neither fix touches data. In the lab, the order placed between the move and the fix was still 5 hours off after both fixes. Every DEFAULT SYSDATE column, and everything the application wrote with SYSDATE, is UTC for that window.</p><p>Find the window by key, not by time: the first and last order ID (or audit row, or batch run) after the move and before the fix. Then convert those rows from UTC to local time, letting the database handle daylight saving:</p><pre>UPDATE orders<br>SET    order_date = CAST(FROM_TZ(CAST(order_date AS TIMESTAMP), &#39;UTC&#39;)<br>                         AT TIME ZONE &#39;America/Chicago&#39; AS DATE)<br>WHERE  id BETWEEN :first_id_after_move AND :last_id_before_fix;</pre><p>(For a UTC value of 2026-11-03 01:30 that gives 2026-11-02 19:30, six hours back and on the previous day.) Do it once, for each table, and keep the list of what you changed.</p><h3>Before the next move</h3><ol><li><strong>Record what the app sees</strong> on the old server: SELECT SYSDATE, SYSTIMESTAMP, SESSIONTIMEZONE FROM dual over the application&#39;s own service. That&#39;s the answer the new server has to give.</li><li><strong>Decide the fix before cutover</strong>: TZ for the instance, or TIME_AT_DBTIMEZONE on 26ai. Put it in the build, not in a ticket for after.</li><li><strong>Check over the app’s connection</strong> after the move, not from a DBA shell on the host.</li><li><strong>Fix the scheduler’s fixed offsets</strong> while you’re there; that one bites every November regardless.</li></ol><p>Longer term, the cleanest answer is the one cloud teams already use: store times in UTC, or in TIMESTAMP WITH TIME ZONE, and convert for display. Then the server&#39;s time zone stops mattering. That&#39;s an application change, though, and it won&#39;t be done by Monday.</p><h3>What teams get wrong</h3><ul><li><strong>Testing the move by “does it connect and do the counts match.”</strong> Neither notices a 5-hour shift. Compare a SYSDATE taken over the application&#39;s connection on both sides.</li><li><strong>Trusting </strong><strong>ALTER DATABASE SET TIME_ZONE.</strong> It&#39;s the setting with &quot;time zone&quot; in the name, and on its own it does nothing for SYSDATE.</li><li><strong>Checking the fix as SYSDBA from the host.</strong> Your shell’s TZ is not the application&#39;s.</li><li><strong>Fixing the clock and forgetting the data.</strong> Everything written between the move and the fix is still wrong, and the first report run over that range will say so.</li><li><strong>Moving the problem to every client</strong> by switching to CURRENT_DATE and relying on each one to set its session time zone.</li></ul><h3>Frequently asked questions</h3><p><strong>Why is Oracle SYSDATE showing the wrong time after a cloud migration?</strong></p><p>SYSDATE and SYSTIMESTAMP return the operating system’s time in the time zone (TZ) of the database server process, not a database setting. On-prem servers usually run in local time, while cloud hosts and container images usually run in UTC, so after the move SYSDATE, and every column with DEFAULT SYSDATE, returns UTC. In the companion lab, two orders placed seconds apart before and after the move were stored 5 hours apart.</p><p><strong>Does ALTER DATABASE SET TIME_ZONE change SYSDATE?</strong></p><p>No. DBTIMEZONE is used for TIMESTAMP WITH LOCAL TIME ZONE data. In the lab, after setting it to America/Chicago and reopening the PDB, SYSDATE was still UTC. The exception is Oracle AI Database 26ai with TIME_AT_DBTIMEZONE set to USER_SQL, which makes SYSDATE and SYSTIMESTAMP in SQL return the time in DBTIMEZONE.</p><p><strong>What is the difference between SYSDATE and CURRENT_DATE in Oracle?</strong></p><p>SYSDATE uses the database server process’s operating system time zone. CURRENT_DATE uses the session time zone, which the client sets (for example with the ORA_SDTZ environment variable or ALTER SESSION SET TIME_ZONE). With a client in UTC, CURRENT_DATE is UTC too; in the lab it returned local time only when the client set ORA_SDTZ.</p><p><strong>How do I make SYSDATE return local time on a UTC server?</strong></p><p>Either set TZ (for example TZ=America/Chicago) in the environment that starts the database instance and restart it, or, on Oracle AI Database 26ai, set DBTIMEZONE to the local time zone and TIME_AT_DBTIMEZONE to USER_SQL, then reopen the PDB. Changing DBTIMEZONE fails with ORA-30079 while any TIMESTAMP WITH LOCAL TIME ZONE column holds data.</p><p><strong>Do Oracle scheduler jobs run at the wrong time after moving to a UTC server?</strong></p><p>Not if they were created with a time zone region: DBMS_SCHEDULER stores the time zone with the job, and in the lab a 2am America/Chicago job was still due at 2am Chicago after the move. Jobs whose START_DATE has a fixed offset such as -05:00 (what SYSTIMESTAMP returns) don’t follow daylight saving time, and in the lab such a job’s next run after the November DST change evaluated to 1am instead of 2am.</p><p><strong>How do I fix rows that were stored in UTC after the migration?</strong></p><p>Identify the rows written between the move and the fix, preferably by key range, and convert them once with CAST(FROM_TZ(CAST(col AS TIMESTAMP), ‘UTC’) AT TIME ZONE ‘America/Chicago’ AS DATE), which also handles daylight saving time. Neither fix changes existing data.</p><p>SYSDATE being hours off isn&#39;t a database bug. It&#39;s the database telling you what the new server&#39;s clock says, and the new server runs in UTC. Decide before cutover whether the instance keeps its old time zone or the database answers in its own, check it over the application&#39;s connection, and correct the rows from the gap. It belongs on the cutover checklist with the other things that pass every count and still break the morning after, like <a href="https://uptimearchitect.com/blog/oracle-sequence-cutover-ora-00001/">sequences after cutover</a> and <a href="https://uptimearchitect.com/blog/oracle-character-set-migration-data-loss/">character-set conversion</a>, and with choosing the <a href="https://uptimearchitect.com/blog/oracle-cloud-migration-methods/">migration method</a> in the first place. Then prove it the way that ends the argument — with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/sysdate-timezone">sysdate-timezone lab</a>, where the same instance stores two orders seconds apart five hours apart, and the two fixes put them back.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-sysdate-timezone-cloud-migration/"><em>https://uptimearchitect.com</em></a><em> on October 31, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c5fdafbc79a7" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/sysdate-is-hours-off-after-the-move-to-the-cloud-nothing-errors-c5fdafbc79a7">SYSDATE Is Hours Off After the Move to the Cloud. Nothing Errors.</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ORA-01017 After a Password Change: Rotate It With Zero Downtime]]></title>
            <link>https://systemweakness.com/ora-01017-after-a-password-change-rotate-it-with-zero-downtime-c4bfac240da4?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/c4bfac240da4</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[cybersecurity]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Wed, 28 Oct 2026 05:00:41 GMT</pubDate>
            <atom:updated>2026-11-04T15:51:04.728Z</atom:updated>
            <content:encoded><![CDATA[<p>The security team wants the application account’s password rotated. It’s a one-line change:</p><pre>ALTER USER app_user IDENTIFIED BY &quot;Summer_Passw0rd2&quot;;</pre><p>The new password goes into the secrets store, and the app servers pick it up as they restart. Twenty minutes later the application is down, and the error is:</p><pre>ORA-01017: invalid credential or not authorized; logon denied</pre><p>followed by:</p><pre>ORA-28000: The account is locked</pre><p>ORA-01017 on its own just means a wrong username or password. After a password change it means something more specific: <strong>some clients still have the old one.</strong> Their retries use up the account’s failed-login limit, the account locks, and from then on it refuses <em>every</em> login, including from the servers that already have the new password. The change was correct. The way it rolled out caused the outage.</p><p>Oracle has a feature for exactly this: <strong>gradual password rollover</strong>, in 19c from Release Update 19.12 and in 21c and later. For a window you choose, the old and the new password both work. This post shows the outage, the feature, the query that tells you which clients are still on the old password, and the two traps that come with it. All of it is reproduced in a lab.</p><h3>Why one password change takes everything down</h3><p>Three things combine.</p><p><strong>Existing connections don’t notice.</strong> A password is checked only at login. Every session that was open before the change keeps working. So the change looks fine at first. The failures start later, when a connection pool recycles, a server restarts, or traffic grows and the pool opens new connections.</p><p><strong>Not every client changes at the same moment.</strong> App servers restart one at a time. A batch job reads its password from a different file. A reporting tool has the password saved in a connection profile nobody remembers. Some of them are on the old password for minutes, some for days.</p><p><strong>Failed logins lock the account, not the client.</strong> The profile’s FAILED_LOGIN_ATTEMPTS (10 in a stock DEFAULT profile) counts failures for the <em>account</em>. Once a few clients have retried with the old password, the account is LOCKED(TIMED) for PASSWORD_LOCK_TIME (1 day by default), and the correct password gets ORA-28000 too.</p><p>So the incident looks like this: a password change, a quiet period, then ORA-01017 from a few servers, then ORA-28000 from all of them.</p><h3>Prove it</h3><p>The lab runs Oracle Database Free in a container. It creates APP_USER on a profile with FAILED_LOGIN_ATTEMPTS 5, and audits its logins. Then it changes the password while &quot;app servers&quot; still log in with the old one:</p><pre>password change, no rollover               result<br>old password, attempts 1-5                  ORA-01017: invalid credential or not authorized<br>old password, attempt 6                     ORA-28000: The account is locked<br>new password                                ORA-28000: The account is locked<br>connection opened before the change         still alive, still querying</pre><p>Then the same change with a one-day rollover window:</p><pre>password change, PASSWORD_ROLLOVER_TIME 1   result<br>account status                              OPEN &amp; IN ROLLOVER<br>old password, logins 1-7                    connected (7 logins, limit is 5 failures)<br>new password                                connected</pre><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/password-rollover"><em>password-rollover lab</em></a><em> </em><strong><em>asserts</em></strong><em> every row: ORA-01017 five times, then ORA-28000 for the old </em>and<em> the new password without rollover; both passwords working with it; the audit trail telling them apart; a typo still locking the account during rollover; a second change dropping the middle password; and ending the window early. If any of that doesn’t reproduce, the run fails. Proven on every CI push.</em></blockquote><h3>Gradual password rollover</h3><p>Rollover is a <strong>profile</strong> limit, not an option of ALTER USER. Set it on the profile the application account uses, <em>then</em> change the password:</p><pre>ALTER PROFILE app_prof LIMIT password_rollover_time 1;   -- days: 1/24 = one hour</pre><pre>ALTER USER app_user IDENTIFIED BY &quot;Summer_Passw0rd2&quot;;</pre><p>From that moment until the window ends, both passwords log in, and DBA_USERS shows it:</p><pre>SELECT username, account_status FROM dba_users WHERE username = &#39;APP_USER&#39;;</pre><pre>USERNAME   ACCOUNT_STATUS<br>APP_USER   OPEN &amp; IN ROLLOVER</pre><p>The window is in days, from <strong>one hour</strong> (1/24) to <strong>60 days</strong>; anything outside that is refused with ORA-02377 (the lab checks 30 minutes and 61 days). 0, the DEFAULT profile&#39;s value, turns it off. The window starts at the password change, so a profile changed <em>after</em> the password doesn&#39;t help that change.</p><p>Pick a window longer than your slowest client takes to pick up the new password, including the weekly batch job and the server that only restarts at patch time. A day is short. A week is common.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/449/0*7IQRW1dtkF87QbeQ" /></figure><p><em>A password rotation with rollover. The window keeps the old password working while clients move; the audit trail tells you when they all have; then you end the window instead of waiting for it.</em></p><h3>Who is still on the old password?</h3><p>This is the question the rollover window gives you time to answer, and the database can answer it. With logins audited, every unified audit record for a successful login says which password it used, in AUTHENTICATION_TYPE:</p><pre>(TYPE=(DATABASE));(CLIENT ADDRESS=((PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=56796)));<br>(LOGON_INFO=((VERIFIER=12C-OLD)(CLIENT_CAPABILITIES=O5L_NP,O7L_MR,O8L_LI)));</pre><p>12C-OLD is the old password, 12C-NEW the new one. (That&#39;s what the lab&#39;s 26ai database records; check that your release writes LOGON_INFO before you rely on it. If it doesn&#39;t, the same query without the verifier still lists every host and program that logged in since the change, which is your list of clients to check.) Count them since the change:</p><pre>SELECT regexp_substr(authentication_type, &#39;VERIFIER=([^)]*)&#39;, 1, 1, NULL, 1) AS verifier,<br>       userhost, client_program_name, COUNT(*) AS logons<br>FROM   unified_audit_trail<br>WHERE  dbusername = &#39;APP_USER&#39;<br>AND    action_name = &#39;LOGON&#39;<br>AND    return_code = 0<br>AND    event_timestamp &gt;= (SELECT CAST(password_change_date AS TIMESTAMP)<br>                           FROM dba_users WHERE username = &#39;APP_USER&#39;)<br>GROUP  BY regexp_substr(authentication_type, &#39;VERIFIER=([^)]*)&#39;, 1, 1, NULL, 1),<br>          userhost, client_program_name<br>ORDER  BY verifier, userhost;</pre><p>In the lab (all clients on one host, so grouped by verifier alone), after seven logins with the old password and one with the new:</p><pre>VERIFIER=12C-NEW LOGONS=1<br>VERIFIER=12C-OLD LOGONS=7</pre><p>Every -OLD row is a client you still have to update, with its host and program next to it. This needs logins to be <strong>audited</strong>. The lab creates a policy for the account:</p><pre>CREATE AUDIT POLICY app_logons ACTIONS LOGON;<br>AUDIT POLICY app_logons BY app_user;</pre><p>In the lab’s database nothing audited logins until that policy existed; check AUDIT_UNIFIED_ENABLED_POLICIES on yours. Successful logins can be a lot of rows for a busy pool, so scope the policy to the accounts you&#39;re rotating, and enable it before the change. The same trail answers the outage question, too: without rollover, the failed logins showed up as ORA-01017 x5 and ORA-28000 x2, with the client host, and that&#39;s how you find the server that locked the account. See <a href="https://uptimearchitect.com/blog/oracle-unified-auditing/">unified auditing without the noise</a> for keeping policies like this small.</p><h3>Ending the window</h3><p>When the audit trail shows no more -OLD logins for long enough to cover your slowest client, end the window instead of waiting it out:</p><pre>ALTER USER app_user EXPIRE PASSWORD ROLLOVER PERIOD;</pre><p>In the lab the status goes back to OPEN, the old password gets ORA-01017, the new one connects, and a connection opened earlier with the old password <strong>is still alive and querying</strong>. Ending the rollover, like the change itself, only affects new logins.</p><p>That’s also the reason to end it deliberately. While the window is open, anyone with the old password can still log in. If the rotation was because the old password leaked, a long window keeps the leak open, so pick the window to match the reason for the change.</p><h3>Two traps</h3><p><strong>A wrong password still counts.</strong> Rollover accepts <em>two</em> passwords, not any password. In the lab, five logins with a typo during the rollover window got ORA-01017, and then the account was locked: the new password and the old one both got ORA-28000. A client with a mistyped secret locks the account just as it did before. Rollover removes the outage caused by timing; it doesn’t remove the lockout.</p><p><strong>A second change drops the middle password.</strong> If the password is changed again inside the window (a rerun of the rotation job, a second ticket, someone “fixing” a typo in the new password), the window continues, but only the <strong>original</strong> and the <strong>latest</strong> password work:</p><pre>original password (Spring)        connected<br>first new password (Summer)       ORA-01017<br>latest password (Autumn)          connected<br>account status                    OPEN &amp; IN ROLLOVER</pre><p>Any client that already moved to the middle password is now failing, and the account status looks exactly the same as it did a minute earlier. If you need a second change, let the first rollover finish (or end it) first.</p><h3>The rotation, step by step</h3><ol><li><strong>Know the clients.</strong> Audit logins for the account for a while before the change. Each distinct USERHOST / CLIENT_PROGRAM_NAME is a client that needs the new password.</li><li><strong>Set the window</strong> on the account’s profile: ALTER PROFILE ... LIMIT password_rollover_time 7. If the profile is shared with accounts you don&#39;t want to roll over, give this account its own profile.</li><li><strong>Change the password once.</strong> ALTER USER ... IDENTIFIED BY .... Check OPEN &amp; IN ROLLOVER.</li><li><strong>Push the new password</strong> to every client, at whatever pace your deployments allow.</li><li><strong>Watch the audit trail</strong> until there are no -OLD logins, and chase the ones that remain.</li><li><strong>End the window</strong>: ALTER USER ... EXPIRE PASSWORD ROLLOVER PERIOD.</li></ol><p>If the account is already locked when you start reading this: ALTER USER app_user ACCOUNT UNLOCK, find the client that&#39;s still failing (the audit trail has its host, if failed logins are audited), and fix it before it locks the account again.</p><h3>What teams get wrong</h3><ul><li><strong>Changing the password and the clients “at the same time.”</strong> There’s no such moment. Some client is always last, and until it moves, it’s failing logins against a shared limit.</li><li><strong>Setting the rollover limit after the password change.</strong> The window starts at the change. Set the profile first.</li><li><strong>Reading </strong><strong>OPEN &amp; IN ROLLOVER as &quot;the old password works.&quot;</strong> After a second change it&#39;s the original password that works, not the one most clients were just given.</li><li><strong>Raising </strong><strong>FAILED_LOGIN_ATTEMPTS to UNLIMITED</strong> so rotations stop locking the account. That removes a defence against password guessing for good, to fix a problem that lasts an hour. Use rollover, and keep the limit (see the <a href="https://uptimearchitect.com/blog/oracle-database-hardening-checklist/">hardening checklist</a>).</li><li><strong>Leaving the window open for weeks after everyone has moved.</strong> It’s two valid passwords. End it once the audit trail is clean.</li></ul><h3>Frequently asked questions</h3><p><strong>Why do I get ORA-01017 after changing a user’s password?</strong></p><p>Because some client is still logging in with the old password. Sessions opened before the change keep working, so the failures appear later, when a connection pool opens new connections or a server restarts. Each failed login counts against the profile’s FAILED_LOGIN_ATTEMPTS, and once the limit is reached the account locks and every login, including ones with the new password, fails with ORA-28000.</p><p><strong>What is gradual password rollover in Oracle?</strong></p><p>A profile limit, PASSWORD_ROLLOVER_TIME, available in Oracle 19c from Release Update 19.12 and in 21c and later. When an account’s password is changed, both the old and the new password are accepted for that many days, so clients can move to the new password one at a time without login failures. The account status shows OPEN &amp; IN ROLLOVER during the window. The window can be from one hour (1/24) to 60 days; 0 turns it off.</p><p><strong>How do I find which clients still use the old password during a rollover?</strong></p><p>Audit logins for the account (CREATE AUDIT POLICY … ACTIONS LOGON and AUDIT POLICY … BY the user). Each successful login’s AUTHENTICATION_TYPE in UNIFIED_AUDIT_TRAIL includes LOGON_INFO with VERIFIER=12C-OLD or VERIFIER=12C-NEW. Group the logins since the password change by verifier, USERHOST and CLIENT_PROGRAM_NAME; every OLD row is a client still to update. This was verified on Oracle 26ai; on other releases, check that LOGON_INFO is recorded.</p><p><strong>How do I end a password rollover period early?</strong></p><p>Run ALTER USER username EXPIRE PASSWORD ROLLOVER PERIOD. The account status returns to OPEN and only the new password works for new logins. Sessions that are already connected, including ones that logged in with the old password, are not disconnected.</p><p><strong>Does password rollover prevent ORA-28000 account locked?</strong></p><p>Only for the old password. During the rollover window the old and new passwords both log in, so clients that haven’t moved don’t fail. A wrong password still fails with ORA-01017 and still counts toward FAILED_LOGIN_ATTEMPTS, and in the companion lab five wrong logins during the window locked the account for both passwords.</p><p><strong>What happens if I change the password twice during a rollover?</strong></p><p>The rollover window continues, but only the original password (from before the first change) and the latest password are valid. The password in between stops working immediately, even though the account status still shows OPEN &amp; IN ROLLOVER. Clients that had already moved to the middle password get ORA-01017.</p><p>ORA-01017 after a password change isn’t a mystery about the password. It’s a rollout problem: some client is always last, and without a window, the last client locks out the first. Give the account a rollover window, change the password once, watch the audit trail until nobody is on the old one, and close the window yourself. It belongs with the other account controls in the <a href="https://uptimearchitect.com/blog/oracle-database-hardening-checklist/">hardening checklist</a> and with the connection errors that look like outages, like <a href="https://uptimearchitect.com/blog/oracle-ora-12514-listener-service/">ORA-12514</a>. Then prove it the way that ends the argument — with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/password-rollover">password-rollover lab</a>, where the same password change locks the account without rollover and causes zero failed logins with it.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-ora-01017-password-rollover/"><em>https://uptimearchitect.com</em></a><em> on October 28, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c4bfac240da4" width="1" height="1" alt=""><hr><p><a href="https://systemweakness.com/ora-01017-after-a-password-change-rotate-it-with-zero-downtime-c4bfac240da4">ORA-01017 After a Password Change: Rotate It With Zero Downtime</a> was originally published in <a href="https://systemweakness.com/">System Weakness</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ORA-12514: The Database Is Up. The Listener Just Doesn’t Know It.]]></title>
            <link>https://systemweakness.com/ora-12514-the-database-is-up-the-listener-just-doesnt-know-it-f3718dd3fe7a?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/f3718dd3fe7a</guid>
            <category><![CDATA[cybersecurity]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Tue, 20 Oct 2026 05:00:13 GMT</pubDate>
            <atom:updated>2026-11-01T13:48:08.975Z</atom:updated>
            <content:encoded><![CDATA[<p>The application can’t connect. The error is one every Oracle DBA has seen:</p><pre>ORA-12514: TNS:listener does not currently know of service requested in connect descriptor</pre><p>Newer clients word it more plainly. The 26ai client in this post’s lab says:</p><pre>ORA-12514: Cannot connect to database. Service FREEPDB is not registered with the listener<br>at host 127.0.0.1 port 1521.</pre><p>The database is up. You can log in on the server. So someone restarts the listener, and now <em>nothing</em> can connect for a minute.</p><p>ORA-12514 is a precise message, and it’s good news of a kind: <strong>the listener answered.</strong> It’s running, the host and port are right, and the network path works. What it doesn’t have is a service by the name the client asked for. The database behind it may be perfectly healthy. This post covers how services get to the listener, the four common reasons one is missing, and how to tell ORA-12514 from its neighbours. It ends with a lab where restarting the listener <strong>causes</strong> a minute of ORA-12514 for every service, and one ALTER SYSTEM REGISTER closes the gap at once.</p><h3>How a service gets to the listener</h3><p>The listener doesn’t know what databases exist. The database tells it. A background process (LREG) in each instance <strong>registers</strong> the instance’s services with the listener: FREEPDB1, each application service, the CDB&#39;s own service. It does that at startup, whenever a service starts or stops, and periodically after that. The listener keeps that list in memory only.</p><p>A client connects by asking for a service: host:1521/FREEPDB1 in EZConnect, or SERVICE_NAME=FREEPDB1 in a tnsnames.ora descriptor. If the listener has a registered service by that name, it hands the connection to the instance. If not, it refuses with ORA-12514.</p><p>So ORA-12514 has exactly two kinds of cause: <strong>the client asked for the wrong name</strong>, or <strong>the right name isn’t registered</strong> with <em>this</em> listener right now. lsnrctl services (or lsnrctl status) shows what the listener has:</p><pre>Services Summary...<br>Service &quot;FREE&quot; has 1 instance(s).<br>Service &quot;FREEXDB&quot; has 1 instance(s).<br>Service &quot;freepdb1&quot; has 1 instance(s).<br>Service &quot;orders_app&quot; has 1 instance(s).</pre><p>If the service is in that list, look at the client. If it isn’t, look at the database.</p><h3>Which error do you actually have?</h3><p>Five errors get mixed up in tickets. They fail at different points in the connection, so they point to different fixes:</p><pre>ORA-12154   the alias wasn&#39;t found (tnsnames.ora, LDAP)    the client never reached a listener<br>ORA-12541   no listener at that host and port              wrong host/port, listener down, firewall<br>ORA-12505   the listener doesn&#39;t know that SID             often a PDB name given as a SID<br>ORA-12514   the listener doesn&#39;t know that SERVICE         this post<br>ORA-12504   no service name given, and no default service  incomplete connect string</pre><p>ORA-12505 is the usual developer version. jdbc:oracle:thin:@db01:1521:FREEPDB1 (a colon before the name) asks for a <strong>SID</strong>, which is an instance, not a PDB. jdbc:oracle:thin:@//db01:1521/FREEPDB1 (a slash) asks for the service, which is what a PDB is reached by.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/0*uwS5MBTW23tK6ULR" /><figcaption>ORA-12514 means the listener answered and has no service by that name. A wrong name is a client fix; a missing registration is a database fix.</figcaption></figure><h3>Prove it</h3><p>The lab runs Oracle Database Free in a container. It creates an application service, orders_app, in the pluggable database FREEPDB1 the way many shops do: with DBMS_SERVICE, started by hand. Then it breaks the connection four ways, with the database up and healthy every time:</p><pre>case                                     result                                   fix<br>FREEPDB instead of FREEPDB1              ORA-12514; FREEPDB1 connects fine        fix the connect string<br>listener restarted                       ORA-12514 for every service, ~60 s       ALTER SYSTEM REGISTER<br>PDB closed and opened                    PDB READ WRITE, orders_app ORA-12514     start it + SAVE STATE<br>second listener on port 1522             &quot;supports no services&quot;, ORA-12514        LOCAL_LISTENER</pre><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/listener-service"><em>listener-service lab</em></a><em> </em><strong><em>asserts</em></strong><em> every row: ORA-12514 for the wrong name and the right name still connecting; ORA-12514 right after a listener restart and recovery once the database re-registers; the app service missing after a PDB restart and surviving the next one after </em><em>SAVE STATE; and the second listener refusing until </em><em>LOCAL_LISTENER names it. It also asserts ORA-12154, ORA-12541 and ORA-12505 for their cases. If any of that doesn&#39;t reproduce, the run fails. Proven on every CI push.</em></blockquote><h3>Cause 1: the wrong name</h3><p>The most common cause, and the easiest to miss because the name <em>looks</em> right. FREEPDB instead of FREEPDB1, orders instead of orders_app, a domain mismatch (the database has a DB_DOMAIN, so its service is freepdb1.example.com, and the client asks for plain freepdb1, or the other way round), or a connect string still pointing at the old database&#39;s service name after a <a href="https://uptimearchitect.com/blog/oracle-cloud-migration-methods/">migration</a>.</p><p>You don’t have to guess which app is asking for what. <strong>The listener log records every refused connect</strong> with the service the client requested, the client program, and the client host. From the lab:</p><pre>(CONNECT_DATA=(SERVICE_NAME=FREEPDB)(CID=(PROGRAM=sqlplus)(HOST=...)(USER=oracle)))<br>  * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59690)) * establish * FREEPDB * 12514<br>TNS-12514: listener does not currently know of service requested in connect descriptor</pre><p>Even on 26ai the log uses the classic text. lsnrctl status prints the log&#39;s location. On a busy listener, grep it for 12514 and count by SERVICE_NAME and HOST: the typo and the server it lives on fall out in one command.</p><h3>Cause 2: the listener was restarted</h3><p>This is the one that turns a small problem into an outage. A listener knows only what’s been registered with it since it started. Restart it, and for a while it knows nothing:</p><pre>$ lsnrctl status<br>...<br>The listener supports no services</pre><p>Every service gets ORA-12514 until the database registers again on its own. In the lab that took <strong>about a minute</strong>, every time: 61 seconds of refused connections for every service, caused entirely by the restart. Restarting the listener to “fix” ORA-12514 makes it worse.</p><p>The database can be told to register right now:</p><pre>ALTER SYSTEM REGISTER;</pre><p>In the lab, after the same restart, the connection works immediately after this command. Run it in every instance (and every RAC node) whose services that listener serves. lsnrctl reload re-reads listener.ora without a stop/start, but don&#39;t count on it keeping the registrations: in the lab it sometimes kept every one and sometimes dropped them all. Follow a reload with ALTER SYSTEM REGISTER too.</p><h3>Cause 3: the service didn’t come back with the database</h3><p>The PDB is open. FREEPDB1 connects. The application&#39;s service doesn&#39;t:</p><pre>FREEPDB1 is READ WRITE; active services: freepdb1<br>localhost:1521/FREEPDB1    -&gt; connected<br>localhost:1521/orders_app  -&gt; ORA-12514: ... Service orders_app is not registered with the listener ...</pre><p>A service created with DBMS_SERVICE and started by hand is running only until the next time the PDB closes. After a patch, a server reboot or a <a href="https://uptimearchitect.com/blog/oracle-data-guard-switchover-vs-failover/">switchover</a>, the PDB comes back and its default service with it, but nothing starts orders_app. The database is fine. The application is down.</p><p>On a single instance, start the service and save the PDB’s state, so it reopens with the same services running:</p><pre>ALTER SESSION SET CONTAINER = FREEPDB1;<br>EXEC DBMS_SERVICE.START_SERVICE(&#39;orders_app&#39;)<br>ALTER PLUGGABLE DATABASE FREEPDB1 SAVE STATE;</pre><p>The lab restarts the PDB again after this, and orders_app connects. Under Grid Infrastructure or RAC, define the service in Clusterware instead (srvctl add service -db ... -pdb FREEPDB1 -service orders_app) and it starts with the database. With Data Guard, the same command takes -role PRIMARY, so the application&#39;s service runs only where the primary is, and follows it on switchover. That last case is outside what the lab builds.</p><h3>Cause 4: a listener the database doesn’t register with</h3><p>A new listener on port 1522, for an application tier or a firewall rule, or a listener moved off 1521. Clients pointed at it get ORA-12514, and its status says why:</p><pre>$ lsnrctl status LISTENER2<br>...<br>The listener supports no services</pre><p>The database registers only with the listeners it knows about. By default that’s the local listener on port 1521. Any other address has to be in LOCAL_LISTENER (or REMOTE_LISTENER, for RAC and SCAN):</p><pre>ALTER SYSTEM SET local_listener =<br>  &#39;(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1522)))&#39;<br>  SCOPE = BOTH;</pre><p>In the lab, both services connect through port 1522 right after this; changing the parameter triggers a registration. (The lab uses SCOPE = MEMORY so a rerun starts clean.) On a Grid Infrastructure node, the agent manages LOCAL_LISTENER; register a new listener with srvctl instead of setting it by hand.</p><h3>The checklist</h3><p>When ORA-12514 comes in, in this order:</p><ol><li><strong>Read the full message.</strong> The service name and the host and port the client used are in it (newer clients) or in the listener log (all releases).</li><li><strong>lsnrctl services</strong> on that host. Is the service listed? If yes, the client has the wrong name or the wrong listener.</li><li><strong>SELECT name FROM v$active_services</strong> in the database (in the PDB). Is the service running? If not, start it, and make sure it starts next time (SAVE STATE or srvctl).</li><li><strong>SHOW PARAMETER local_listener</strong>. Does it include the address the client is using?</li><li><strong>ALTER SYSTEM REGISTER</strong>. Re-registers everything now. Harmless, instant, and much better than a listener restart.</li></ol><h3>What teams get wrong</h3><ul><li><strong>Restarting the listener first.</strong> It refuses every service until the database re-registers, about a minute in the lab. If a restart or a reload really is needed, follow it with ALTER SYSTEM REGISTER.</li><li><strong>Treating ORA-12514 as “the database is down.”</strong> It’s a naming or registration problem. The lab’s database was up and open through every case.</li><li><strong>Starting app services by hand.</strong> They don’t survive a PDB restart. Use SAVE STATE or Clusterware, and test it by restarting the PDB before the patch weekend does it for you.</li><li><strong>Giving a PDB name as a SID.</strong> That’s ORA-12505, not ORA-12514. PDBs are reached by service name: /FREEPDB1, not :FREEPDB1.</li><li><strong>Relying on a default service.</strong> The lab’s container has DEFAULT_SERVICE_LISTENER set, so a connect string with no service name quietly lands in the CDB root. Without that parameter it fails with ORA-12504. Neither is where the application should be; always name the service.</li></ul><h3>Frequently asked questions</h3><p><strong>What does ORA-12514 TNS:listener does not currently know of service requested in connect descriptor mean?</strong></p><p>It means the client reached a listener, and the listener has no registered service with the name the client asked for. The listener is up and reachable; the database may be healthy too. Either the client asked for the wrong service name (a typo, a missing or extra domain, an old name after a migration), or the right service isn’t registered with that listener: because the listener was just restarted, the service isn’t running, or the database doesn’t register with that listener. Newer clients word the same error as ‘Cannot connect to database. Service X is not registered with the listener at host … port …’.</p><p><strong>How do I fix ORA-12514?</strong></p><p>Run lsnrctl services on the database host and check whether the service is listed. If it is, fix the client’s connect string; the listener log records the exact service name each refused client asked for. If it isn’t, check v$active_services in the database: start the service if it’s stopped and make it start automatically (SAVE STATE on a single instance, srvctl under Grid Infrastructure). Check that LOCAL_LISTENER includes the listener’s address. Then run ALTER SYSTEM REGISTER to register everything with the listener immediately.</p><p><strong>Why do I get ORA-12514 after restarting the listener?</strong></p><p>Because a newly started listener knows no services. The database registers its services again on its own, which took about a minute in the companion lab, and until then every service is refused with ORA-12514. ALTER SYSTEM REGISTER in each instance makes the database register immediately. lsnrctl reload, which re-reads listener.ora without a stop/start, sometimes dropped the registrations in the lab too, so follow it with ALTER SYSTEM REGISTER as well.</p><p><strong>What is the difference between ORA-12514, ORA-12505, ORA-12541 and ORA-12154?</strong></p><p>They fail at different steps. ORA-12154 means the client couldn’t resolve the alias (in tnsnames.ora or LDAP) and never reached a listener. ORA-12541 means nothing is listening at that host and port. ORA-12505 means the listener doesn’t know the SID requested, often because a PDB name was given as a SID (host:1521:PDB instead of //host:1521/PDB). ORA-12514 means the listener answered but has no service by the requested name.</p><p><strong>Why does my PDB’s application service disappear after a restart?</strong></p><p>A service created with DBMS_SERVICE and started manually is not started automatically when the PDB opens again. The PDB and its default service come back, so the database looks healthy, but connections to the application service fail with ORA-12514. On a single instance, start the service and run ALTER PLUGGABLE DATABASE … SAVE STATE so the PDB reopens with the same services. Under Grid Infrastructure or RAC, create the service with srvctl add service so Clusterware starts it, with -role PRIMARY for Data Guard.</p><p><strong>Does ORA-12514 mean the database is down?</strong></p><p>No. ORA-12514 is about registration and naming, not database health. In the companion lab the database was up and the PDB open READ WRITE in every case that produced ORA-12514. If the instance were down, a listener with only dynamic registration would also report ORA-12514, so check both: lsnrctl services for what the listener has, and the instance itself for whether it’s running.</p><p>ORA-12514 is a listener telling you exactly what’s wrong: it’s here, and it doesn’t have that name. Before you restart anything, compare what the client asked for (the listener log) with what the listener has (lsnrctl services), and let that point you at the client or the database. Then register, don&#39;t restart. It sits with the other Oracle Net settings worth getting right, like <a href="https://uptimearchitect.com/blog/oracle-sqlnet-network-encryption/">network encryption</a>, and with the cutover-weekend surprises like <a href="https://uptimearchitect.com/blog/oracle-sequence-cutover-ora-00001/">the sequence that wasn&#39;t moved</a>. Then prove it the way that ends the argument — with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/listener-service">listener-service lab</a>, where a listener restart refuses every service for a minute and one ALTER SYSTEM REGISTER brings them all back.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-ora-12514-listener-service/"><em>https://uptimearchitect.com</em></a><em> on October 20, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f3718dd3fe7a" width="1" height="1" alt=""><hr><p><a href="https://systemweakness.com/ora-12514-the-database-is-up-the-listener-just-doesnt-know-it-f3718dd3fe7a">ORA-12514: The Database Is Up. The Listener Just Doesn’t Know It.</a> was originally published in <a href="https://systemweakness.com/">System Weakness</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ORA-01652: Unable to Extend Temp Segment. Don’t Just Add Space.]]></title>
            <link>https://levelup.gitconnected.com/ora-01652-unable-to-extend-temp-segment-dont-just-add-space-60cb7e1319c6?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/60cb7e1319c6</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[performance]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Sat, 17 Oct 2026 05:00:23 GMT</pubDate>
            <atom:updated>2026-10-29T15:33:42.735Z</atom:updated>
            <content:encoded><![CDATA[<p>The open-orders report has run every morning for a year. Today it fails:</p><pre>ORA-01652: unable to extend temp segment by 128 in tablespace TEMP</pre><p>The fix everyone knows is one line: add a tempfile. It takes a minute, it’s online, and the next run fails again, a little later. So someone adds a bigger tempfile, turns on autoextend, and the report fails a third time, after filling the disk.</p><p>ORA-01652 says TEMP ran out. It doesn’t say <em>why</em>. Sometimes TEMP really is too small for legitimate work. Often, one query is asking for far more TEMP than any database could give it, and the error is the only thing standing between you and a wrong answer. This post covers what TEMP is for, how to tell the two cases apart, how to find the query, and why TEMP looks full even when it isn’t. It ends with a lab where giving TEMP <strong>ten times the space buys ten times the runtime and the same error</strong>, while restoring one join predicate makes the same report need <strong>no TEMP at all</strong>.</p><h3>What TEMP is for</h3><p>Sorts, hash joins, DISTINCT, GROUP BY, analytic functions and index builds all need a work area. Oracle gives each one memory from the PGA first. When the work area outgrows its share of memory, the operation <strong>spills</strong> to the temporary tablespace: it writes partial results to TEMP, then reads them back to finish.</p><p>Spilling is normal; a big nightly batch or an index rebuild is supposed to use TEMP. ORA-01652 is raised when a spilling operation asks for another extent and the temporary tablespace has none left to give, and can’t grow. The statement fails and its TEMP is released.</p><p>So the question is never just “is TEMP big enough?” It’s “is this operation’s demand reasonable?” A sort of 200,000 rows that needs 40 MB of TEMP is reasonable. A sort that needs 8 GB to produce 50,000 rows is a bug.</p><h3>The classic runaway</h3><p>Here is the report as it shipped:</p><pre>SELECT DISTINCT c.name, c.region, o.order_id, o.amount<br>FROM   orders o, customers c<br>WHERE  o.status = &#39;OPEN&#39;;</pre><p>A line went missing in an edit: AND o.customer_id = c.customer_id. Without it, there is no join condition, so Oracle pairs <strong>every</strong> open order with <strong>every</strong> customer, a Cartesian product. The report suddenly showed each order thousands of times, so someone added DISTINCT to &quot;remove the duplicates.&quot;</p><p>Now the database has to deduplicate the whole product. With 50,000 open orders and 2,000 customers, that’s 100 million rows into a HASH UNIQUE, far beyond any PGA work area. It spills, and keeps spilling until TEMP is gone.</p><p>And here’s the part that makes adding space the wrong fix: the rows aren’t actually duplicates. Each customer name is different, so every one of the 100 million combinations is distinct. <strong>Given enough TEMP, the report would finish, and return 100 million rows instead of 50,000.</strong> ORA-01652 is the only thing stopping a wrong report from shipping.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/554/0*AK9FKJtshLQ68qsx" /></figure><p>ORA-01652 means a spilling sort or hash asked TEMP for more space than it had. Before adding space, check whether the demand is reasonable. A legitimate big operation needs more TEMP; a runaway query, like a Cartesian join behind a DISTINCT, will fill any amount of TEMP and needs its SQL fixed.</p><h3>Prove it</h3><p>The lab runs Oracle Database Free in a container. It creates a <strong>32 MB</strong> temporary tablespace that cannot autoextend and assigns it to an application user. That user owns ORDERS (200,000 rows, 50,000 of them OPEN) and CUSTOMERS (2,000 rows). Then it runs the broken report three ways:</p><pre>TEMP            report                     result            elapsed   TEMP used<br>32 MB           join predicate missing     ORA-01652          1.2 s       31 MB<br>320 MB (10x)    join predicate missing     ORA-01652 again   10.7 s      318 MB<br>32 MB           join predicate restored    50,000 rows        0.1 s        0 MB</pre><p>Ten times the space bought ten times the runtime, then the same error. (Elapsed times depend on the hardware; the lab checks the outcomes, not the clock.) A hundred times the space would buy a hundred times the runtime. With the join predicate back, and DISTINCT left in place to show that the join alone is the fix, the report returns its 50,000 rows in a tenth of a second without touching TEMP.</p><p>The plan says so before anything runs. EXPLAIN PLAN on the broken report:</p><pre>| Id  | Operation               | Name      | Rows  | Bytes |TempSpc|<br>---------------------------------------------------------------------<br>|   3 |    HASH UNIQUE          |           |  200M |  6484M|  8445M|<br>|   4 |     MERGE JOIN CARTESIAN|           |  200M |  6484M|       |<br>|   5 |      TABLE ACCESS FULL  | ORDERS    |  100K |  1660K|       |<br>|   6 |      BUFFER SORT        |           |  2000 | 34000 |       |<br>|   7 |       TABLE ACCESS FULL | CUSTOMERS |  2000 | 34000 |       |</pre><p>MERGE JOIN CARTESIAN and a TempSpc estimate of <strong>8,445M</strong>, about 8 GB of TEMP, for a report that should return 50,000 rows. (The optimizer estimates twice the real row count because, without a histogram, it assumes half the orders are OPEN. That doesn&#39;t change the conclusion.) <a href="https://uptimearchitect.com/blog/oracle-execution-plans-decoded/">Reading an execution plan</a> for TempSpc and Cartesian joins takes seconds, and it would have caught this before the report ever ran.</p><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/temp-space"><em>temp-space lab</em></a><em> runs the broken report on 32 MB of TEMP and </em><strong><em>asserts</em></strong><em> ORA-01652. It asserts the Cartesian join in the plan, then adds a 288 MB tempfile online and asserts the </em><strong><em>same</em></strong><em> error again, after catching the report live in </em><em>V$TEMPSEG_USAGE. Finally it drops the extra tempfile and asserts that the corrected report returns 50,000 rows using no TEMP. If any of that doesn&#39;t reproduce, the run fails. Proven on every CI push.</em></blockquote><h3>How to find the query</h3><p><strong>Read the whole message.</strong> Recent releases put the culprit in the error itself. The lab’s Oracle Database Free (26ai) says <em>grow</em> rather than <em>extend</em>, and names the SQL ID and how much TEMP the session held:</p><pre>ORA-01652: unable to grow temp segment in tablespace TEMP_SMALL by 1MB during operation<br>with SQL ID : 249gkzhhu3cdk, temp space used by session : 31 (MB)</pre><p>The same line goes to the alert log. On 19c the message names only the tablespace, so you have to catch the statement another way.</p><p><strong>Live: </strong><strong>V$TEMPSEG_USAGE.</strong> While the statement is running, this view shows who holds TEMP and for which SQL:</p><pre>SELECT s.sid, s.username, u.sql_id_tempseg, u.segtype,<br>       ROUND(u.blocks * t.block_size / 1024 / 1024) AS temp_mb<br>FROM   v$tempseg_usage u<br>JOIN   v$session s ON s.saddr = u.session_addr<br>JOIN   dba_tablespaces t ON t.tablespace_name = u.tablespace<br>ORDER  BY u.blocks DESC;</pre><p>Use SQL_ID_TEMPSEG, not SQL_ID. In the lab, watching the report from a second session, SQL_ID pointed at SQL*Plus&#39;s own login query ( SELECT DECODE(USER, &#39;XS$NULL&#39;, ...), while SQL_ID_TEMPSEG was the report that was actually holding the TEMP. The other catch: once the statement fails, its TEMP is released and the row is gone. In the lab, V$TEMPSEG_USAGE is empty a second after the error.</p><p><strong>After the fact: ASH.</strong> Active Session History samples every active session once a second, and each sample records TEMP_SPACE_ALLOCATED. The statement that filled TEMP stands out:</p><pre>SELECT sql_id, COUNT(*) AS samples,<br>       ROUND(MAX(temp_space_allocated) / 1024 / 1024) AS peak_temp_mb<br>FROM   v$active_session_history<br>WHERE  sample_time &gt; SYSTIMESTAMP - INTERVAL &#39;30&#39; MINUTE<br>AND    temp_space_allocated &gt; 0<br>GROUP  BY sql_id<br>ORDER  BY peak_temp_mb DESC;</pre><p>In the lab, ASH caught 249gkzhhu3cdk peaking at over 300 MB of TEMP. For older incidents, DBA_HIST_ACTIVE_SESS_HISTORY has the same column, and an <a href="https://uptimearchitect.com/blog/how-to-read-an-awr-report/">AWR report</a> shows the top SQL for the window. (ASH and AWR are part of the Diagnostics Pack on Enterprise Edition.)</p><p><strong>Then look at its plan.</strong> With the SQL ID, DBMS_XPLAN.DISPLAY_CURSOR (if the cursor is still in the shared pool) or EXPLAIN PLAN on the statement text tells you whether the demand makes sense. Compare the TempSpc and row estimates with what the statement should return.</p><h3>Why TEMP looks full when it isn’t</h3><p>Right after the failure, the lab looks at the 32 MB tablespace through four views:</p><pre>V$TEMPSEG_USAGE        0 rows        the session that caused it is gone<br>V$TEMP_SPACE_HEADER    32 MB used, 0 MB free<br>V$SORT_SEGMENT         0 MB in use<br>DBA_TEMP_FREE_SPACE    31 MB free of 32 MB</pre><p>Oracle doesn’t hand TEMP extents back to the files when a sort finishes. It keeps them allocated in the sort segment and gives them to the next operation that needs them, which is faster than allocating fresh ones. V$TEMP_SPACE_HEADER keeps counting those extents as used after the sort ends, so a monitoring check built on it reports TEMP at 100% all day after the first big sort. That isn&#39;t a leak, and it isn&#39;t a reason to add space. V$SORT_SEGMENT ( USED_BLOCKS) and DBA_TEMP_FREE_SPACE show what&#39;s actually free. Alert on those.</p><h3>The fix</h3><p><strong>If the SQL is wrong, fix the SQL.</strong> A missing join predicate, a DISTINCT hiding duplicates, a join on the wrong column, a plan that hash-joins two huge row sources because statistics are stale: these need a code or statistics fix. TEMP is just where the damage shows up.</p><p><strong>If the work is legitimate, add space online.</strong> Big index builds, a nightly aggregation, and more concurrent users than TEMP was sized for are real reasons TEMP is too small:</p><pre>-- Smallfile temporary tablespace: add a tempfile<br>ALTER TABLESPACE temp ADD TEMPFILE &#39;/u01/oradata/PROD/temp02.dbf&#39; SIZE 8G;</pre><pre>-- Bigfile temporary tablespace (one file only): resize it<br>ALTER DATABASE TEMPFILE &#39;/u01/oradata/PROD/temp01.dbf&#39; RESIZE 16G;</pre><p>Both are online, and so is ALTER TABLESPACE ... DROP TEMPFILE once nothing is using the file; the lab adds a 288 MB tempfile and drops it again without a restart. A bigfile tablespace holds exactly one file, so ADD TEMPFILE fails with ORA-32771 there. The lab&#39;s 26ai database creates new tablespaces as bigfile unless told otherwise; check DEFAULT_TBS_TYPE in DATABASE_PROPERTIES on yours.</p><p><strong>Contain the damage.</strong></p><ul><li><strong>Cap autoextend.</strong> An autoextending tempfile without MAXSIZE doesn&#39;t fix a runaway query. It lets the query fill the filesystem instead of the tablespace, and then everything on that filesystem fails too.</li><li><strong>Separate temporary tablespaces.</strong> Give a reporting or batch user its own temporary tablespace (the lab’s user hasTEMP_SMALL). Then one application&#39;s runaway query can&#39;t starve every other session&#39;s sorts.</li><li><strong>Size from evidence.</strong> The peak of DBA_HIST_ACTIVE_SESS_HISTORY.TEMP_SPACE_ALLOCATED summed across sessions, over a few busy weeks, tells you what your legitimate workload actually needs.</li></ul><h3>What teams get wrong</h3><ul><li><strong>Adding space first, asking later.</strong> It’s the right fix maybe half the time. Check the statement’s plan before you add the second tempfile, not after the third.</li><li><strong>Turning on unlimited autoextend.</strong> It converts a failed report into a full filesystem. Use MAXSIZE.</li><li><strong>Trusting </strong><strong>V$TEMP_SPACE_HEADER.</strong> It shows extents the sort segment has cached, not space in use. A TEMP that &quot;is always 100% full&quot; is usually fine.</li><li><strong>Looking for the culprit too late, or in the wrong column.</strong> V$TEMPSEG_USAGE empties the moment the statement fails; read the full error text, or go to ASH. While it&#39;s running, trustSQL_ID_TEMPSEG, not SQL_ID.</li><li><strong>Hiding duplicates with </strong><strong>DISTINCT.</strong> Unexpected duplicates are a symptom of a wrong join. DISTINCT makes the report look right on small data and turns into a TEMP bomb on real data.</li><li><strong>Confusing it with a permanent tablespace.</strong> ORA-01652 ... in tablespace USERS (or any non-temporary tablespace) is a different problem: aCREATE TABLE AS SELECT, index build or MOVE ran out of room while building its new segment. The tablespace named in the message tells you which case you have.</li></ul><h3>Frequently asked questions</h3><p><strong>What does ORA-01652 unable to extend temp segment mean?</strong></p><p>It means an operation that spills to disk, such as a sort, hash join, DISTINCT, GROUP BY or index build, asked the temporary tablespace for another extent and none was available, and the tablespace could not grow. The statement fails and its temporary space is released. The error says that TEMP ran out, not why: either the temporary tablespace is genuinely too small for legitimate work, or one statement is demanding far more TEMP than it should, often because of a missing join predicate or a bad plan.</p><p><strong>Should I just add a tempfile to fix ORA-01652?</strong></p><p>Only after checking the statement that caused it. If the work is legitimate, such as a large index build, a nightly batch or more concurrent sessions, adding a tempfile (or resizing the tempfile of a bigfile tablespace) is the right fix and it is online. If the statement is a runaway, such as a Cartesian join behind a DISTINCT, more space only moves the failure. In the companion lab, giving TEMP ten times the space made the same report fail again after ten times the runtime, while restoring one join predicate made it return 50,000 rows using no TEMP.</p><p><strong>How do I find which query caused ORA-01652?</strong></p><p>Recent Oracle releases include the SQL ID and the session’s temp usage in the error message and the alert log. On 19c, which names only the tablespace, query V$TEMPSEG_USAGE joined to V$SESSION while the statement is running (use its SQL_ID_TEMPSEG column, which identifies the statement that owns the temp segment; SQL_ID can show a different statement the session parsed), or look in Active Session History afterwards: V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY record TEMP_SPACE_ALLOCATED for every sample, so grouping by SQL_ID and taking the maximum shows the statement that filled TEMP. Then check its execution plan for a Cartesian join or a TempSpc estimate far larger than the result should need.</p><p><strong>Why does my temporary tablespace always show 100% used?</strong></p><p>Because Oracle keeps temporary extents allocated in the sort segment after a sort finishes and reuses them for the next operation, rather than freeing them back to the tempfiles. V$TEMP_SPACE_HEADER counts those cached extents as used, so it can show TEMP as full long after the sorts that used it have finished. V$SORT_SEGMENT (USED_BLOCKS versus FREE_BLOCKS) and DBA_TEMP_FREE_SPACE show the space that is actually free, and are the right views to monitor.</p><p><strong>Can I add or drop a tempfile without downtime?</strong></p><p>Yes. ALTER TABLESPACE temp ADD TEMPFILE … SIZE … is online, and so is ALTER TABLESPACE temp DROP TEMPFILE … once no session is using the file. A bigfile temporary tablespace can hold only one tempfile, so adding a file fails with ORA-32771; resize the existing one with ALTER DATABASE TEMPFILE … RESIZE instead. DATABASE_PROPERTIES.DEFAULT_TBS_TYPE shows whether new tablespaces default to bigfile or smallfile on your database.</p><p><strong>Is ORA-01652 on a permanent tablespace the same problem?</strong></p><p>No. When the message names a permanent tablespace such as USERS rather than a temporary one, the space was needed to build a new permanent segment: a CREATE TABLE AS SELECT, an index build or rebuild, or an ALTER TABLE MOVE, which create their new segment as temporary extents in the target tablespace until they finish. The fix is space in that tablespace, or a smaller operation, not a bigger TEMP.</p><p>ORA-01652 is easy to make go away for a day and hard to make go away for good, because the obvious fix treats the symptom. Before you add space, read the whole error, find the statement, and look at its plan. If the demand is legitimate, add a tempfile online and cap its growth. If it’s a Cartesian join behind a DISTINCT, no amount of TEMP will help, and enough of it would let a wrong report finish. It belongs with the other errors that freeze or fail work for a reason the message doesn&#39;t spell out, like <a href="https://uptimearchitect.com/blog/oracle-ora-01555-snapshot-too-old/">ORA-01555</a> and <a href="https://uptimearchitect.com/blog/oracle-ora-00257-archiver-stuck/">ORA-00257</a>. Then prove it the way that ends the argument - with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/temp-space">temp-space lab</a>, where ten times the TEMP buys ten times the runtime and the same error, and one restored join needs none.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-ora-01652-temp-space/"><em>https://uptimearchitect.com</em></a><em> on October 17, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=60cb7e1319c6" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/ora-01652-unable-to-extend-temp-segment-dont-just-add-space-60cb7e1319c6">ORA-01652: Unable to Extend Temp Segment. Don’t Just Add Space.</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Checkpoint Not Complete: Every Commit Waits, and the SQL Is Fine]]></title>
            <link>https://medium.com/@yaroperventsev/checkpoint-not-complete-every-commit-waits-and-the-sql-is-fine-53b58b6bddb1?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/53b58b6bddb1</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sre]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[devops]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Tue, 13 Oct 2026 05:00:24 GMT</pubDate>
            <atom:updated>2026-10-25T20:49:40.609Z</atom:updated>
            <content:encoded><![CDATA[<p>The nightly batch used to finish in twenty minutes. Now it takes an hour, and during that hour the application stutters: every few seconds, every session that writes anything (an order, a login timestamp, a session audit row) freezes for a second or two, then carries on. The SQL hasn’t changed. The plans haven’t changed. CPU is fine, the storage team says the disks are fine, and the alert log has the same two lines over and over:</p><pre>Thread 1 cannot allocate new log, sequence 23<br>Checkpoint not complete</pre><p>Nothing is broken, and no query is slow. The database is waiting for itself: the <strong>online redo logs are too small for the rate at which the workload changes data</strong>, so the log writer keeps running out of logs it is allowed to reuse. This post covers what that message means, why it freezes every writer at once, how to spot it before users do, and how to fix it online. It ends with a lab where the same update takes <strong>14.1 seconds</strong> on two 20 MB redo logs, 11.9 of them waiting, and <strong>1.9 seconds</strong> after the fix.</p><h3>How the redo log circle works</h3><p>Every change in an Oracle database is first written as <strong>redo</strong> to the online redo logs, a small, fixed set of files ( <em>groups</em>) that the log writer (LGWR) fills one after another in a circle. When the current group is full, LGWR <strong>switches</strong> to the next one, and when it reaches the last group it wraps around and reuses the first.</p><p>Reusing a group is only safe once nothing still needs the redo in it for crash recovery. The redo protects changes to data blocks that may still exist only in memory. So before a group can be overwritten, the <strong>checkpoint</strong> that covers it must finish: the database writer (DBWR) has to write every dirty block whose changes are recorded in that group to the datafiles. Until that happens, V$LOG shows the group as ACTIVE, meaning still needed for recovery, and LGWR cannot touch it.</p><p>Normally the circle is big enough that DBWR finishes long before LGWR comes back around, and the next group is always INACTIVE and ready.</p><h3>Why it freezes everyone</h3><p>When the logs are small relative to the write rate, LGWR fills the whole circle faster than DBWR can complete the checkpoints. It comes back to a group that is still ACTIVE, and it has nowhere to write. The alert log records it as <em>&quot;cannot allocate new log ... Checkpoint not complete&quot;</em>.</p><p>LGWR is a single point of serialisation. Until DBWR frees that group, <strong>no session in the database can generate redo</strong>: no insert, no update, no delete, no commit. They all wait on the same event, log file switch (checkpoint incomplete), and then all resume at once. That&#39;s the stutter. It&#39;s not one slow query; it&#39;s the whole instance pausing every time the log writer laps the checkpoint.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/450/0*O_wx6FC5PrbQ3_Lo" /></figure><p>LGWR writes redo into a circle of online log groups. Before it can reuse a group, DBWR must finish the checkpoint covering it. With two 20 MB groups and a heavy write burst, LGWR comes back around to a group that is still ACTIVE, and every session that needs to write redo waits until DBWR frees it. With groups large enough to absorb the burst, the next group is always INACTIVE and nobody waits.</p><p>This is a close cousin of <a href="https://uptimearchitect.com/blog/oracle-ora-00257-archiver-stuck/">ORA-00257, archiver stuck</a>. There, a log group can’t be reused because the <strong>archiver</strong> hasn’t copied it yet, and the wait is log file switch (archiving needed). Here it&#39;s because the <strong>checkpoint</strong> hasn&#39;t finished. Same circle, same freeze, different fix.</p><h3>Prove it</h3><p>The lab uses the community Oracle Database Free container image, which trims the redo logs to <strong>two groups of 20 MB each</strong> to keep the image small: exactly the layout that causes this. It loads a table of 100,000 rows of about 1 KB, then runs one statement that rewrites every row and commits, a little over 200 MB of redo. It measures the elapsed time, the waits from V$SYSTEM_EVENT, the switches from V$LOG_HISTORY, and the Checkpoint not complete lines in the alert log:</p><pre>redo logs              elapsed   switches   waits (time waited)   alert lines<br>2 x 20 MB (image)       14.1 s         12        12 (11.9 s)              12<br>3 x 512 MB               1.9 s          0         0                        0</pre><p>On the default logs, <strong>84% of the update’s elapsed time was waiting</strong> for a redo log to become reusable: 11.9 of 14.1 seconds. The statement itself was never the problem. After the logs were resized, the identical update ran in 1.9 seconds with no switches and no waits.</p><p>Elapsed times depend on the hardware and vary from run to run; on my machine the small-log update took between 8 and 14 seconds. The waits and the alert-log lines appear every time, and disappear every time after the resize.</p><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/checkpoint-incomplete"><em>checkpoint-incomplete lab</em></a><em> runs the burst on the default 2 x 20 MB logs and </em><strong><em>asserts</em></strong><em> that </em><em>log file switch (checkpoint incomplete) waits and </em><em>Checkpoint not complete alert-log lines appear. It then resizes the logs online and asserts the same burst produces </em><strong><em>zero</em></strong><em> of both. If the stall doesn&#39;t reproduce, or the fix doesn&#39;t remove it, the run fails. Proven on every CI push.</em></blockquote><h3>How to spot it</h3><p><strong>The alert log</strong> is the giveaway. Search it for Checkpoint not complete; one occurrence during a once-a-year data load is noise, but dozens a day mean the logs are undersized for your real workload.</p><p><strong>The wait event.</strong> log file switch (checkpoint incomplete) shows up in V$SYSTEM_EVENT, in AWR&#39;s Top Events and in ASH. In an <a href="https://uptimearchitect.com/blog/how-to-read-an-awr-report/">AWR report</a> it&#39;s often near the top during batch windows, and it&#39;s easy to misread as an I/O problem, because the time is spent waiting on DBWR&#39;s writes.</p><p><strong>How often the logs switch.</strong> V$LOG_HISTORY has one row per switch:</p><pre>SELECT TO_CHAR(TRUNC(first_time, &#39;HH24&#39;), &#39;YYYY-MM-DD HH24&#39;) AS hour,<br>       COUNT(*)                                             AS switches<br>FROM   v$log_history<br>WHERE  first_time &gt; SYSDATE - 7<br>GROUP  BY TRUNC(first_time, &#39;HH24&#39;)<br>ORDER  BY 1;</pre><p>A common rule of thumb is a few switches per hour at peak, roughly one every 15 to 20 minutes. The lab’s update switched 12 times in 14 seconds. If your busiest hours show switches <em>per minute</em>, the logs are far too small for that workload, whether or not you’ve seen the alert yet.</p><p><strong>Oracle’s own sizing advice.</strong> V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE suggests a redo log size in megabytes, but only when FAST_START_MTTR_TARGET is set. With it at 0, as on the lab database, the column is empty. In the lab, with a 60-second target, it suggested 827 MB. Treat it as a starting point, not a law: it&#39;s derived from the recovery target, not from your peak write rate.</p><h3>The fix</h3><p>You can’t resize a redo log file in place. Instead, you <strong>add new groups at the size you want, move onto them, and drop the old ones</strong>, all online:</p><pre>-- 1. Add bigger groups (use your own paths, and two members each on separate disks in production)<br>ALTER DATABASE ADD LOGFILE GROUP 3 (&#39;/u01/oradata/PROD/redo03.log&#39;) SIZE 2G;<br>ALTER DATABASE ADD LOGFILE GROUP 4 (&#39;/u01/oradata/PROD/redo04.log&#39;) SIZE 2G;<br>ALTER DATABASE ADD LOGFILE GROUP 5 (&#39;/u01/oradata/PROD/redo05.log&#39;) SIZE 2G;<br>-- 2. Switch until one of the new groups is CURRENT, then checkpoint<br>ALTER SYSTEM SWITCH LOGFILE;<br>ALTER SYSTEM CHECKPOINT;<br>-- 3. Wait until the old groups show INACTIVE in V$LOG, then drop them<br>SELECT group#, bytes / 1024 / 1024 AS mb, status FROM v$log;<br>ALTER DATABASE DROP LOGFILE GROUP 1;<br>ALTER DATABASE DROP LOGFILE GROUP 2;</pre><ul><li><strong>Order matters.</strong> A CURRENT group can&#39;t be dropped, and neither can an ACTIVE one, because it&#39;s still needed for recovery. Switch off it, checkpoint, and wait forINACTIVE.</li><li><strong>Dropping a group doesn’t delete its file.</strong> Remove the old files at the operating-system level afterwards (with Oracle Managed Files, the database does it for you).</li><li><strong>Size for the peak, not the average.</strong> The goal is that, at your busiest, a log lasts long enough for DBWR to finish the checkpoint long before LGWR needs it again. In the lab, 512 MB groups hold the entire burst, so there is no switch at all. In production, aim for that rule of thumb of a few switches an hour at peak.</li><li><strong>More groups help too.</strong> A third or fourth group gives DBWR more time before LGWR comes back around. Two groups is the bare minimum Oracle allows, and it leaves no slack.</li><li><strong>All groups the same size.</strong> Mixed sizes make switch timing unpredictable and complicate standby redo logs.</li></ul><p>The other lever is DBWR itself: if it can’t keep up because the storage is slow or asynchronous I/O is off, checkpoints take longer at any log size. (The lab container runs with FILESYSTEMIO_OPTIONS=none, which doesn&#39;t help DBWR either.) But undersized logs are far more common, and resizing them is a five-minute, fully online change.</p><h3>What teams get wrong</h3><ul><li><strong>Treating it as a storage problem.</strong> The waits look like I/O, so the first instinct is faster disks. Faster writes help DBWR a little; logs sized for the workload remove the wait entirely.</li><li><strong>Never changing the default.</strong> Many databases still run on the redo logs they were created with, sized for the workload of the day they were built, or trimmed for a small footprint the way the lab’s container image trims them to two 20 MB groups. Fine for a laptop, not for a batch window. Check yours.</li><li><strong>Forgetting the standby.</strong> With Data Guard, standby redo logs should be the same size as the online redo logs, with one more group per thread. Resize the online logs and leave the standby redo logs small, and the standby can’t use them for real-time apply; it falls back to applying from archived logs. Resize both.</li><li><strong>Confusing it with the archiver.</strong> log file switch (archiving needed) is <a href="https://uptimearchitect.com/blog/oracle-ora-00257-archiver-stuck/">ORA-00257</a>&#39;s wait: the archive destination is full or slow. Bigger logs don&#39;t fix that one; freeing the destination does.</li><li><strong>Fearing longer recovery, or ignoring it.</strong> With FAST_START_MTTR_TARGET at 0, how far the checkpoint lags is tied to the size of the smallest log, so bigger logs can mean more redo to replay after a crash. When you enlarge the logs, setFAST_START_MTTR_TARGET (say, 60 to 300 seconds) and the database paces its checkpoint writes to keep recovery time bounded. Undersized logs cost you every busy day; properly sized ones cost a little disk.</li><li><strong>Dropping the wrong group.</strong> Run SELECT group#, status FROM v$log before every DROP LOGFILE. A CURRENT orACTIVE group can&#39;t be dropped, and in a hurry it&#39;s easy to type the wrong number.</li></ul><h3>Frequently asked questions</h3><p><strong>What does ‘Checkpoint not complete’ mean in the Oracle alert log?</strong></p><p>It means the log writer (LGWR) filled the current online redo log and wanted to switch to the next group in the circle, but that group was still ACTIVE: the checkpoint covering it had not finished, so the database writer (DBWR) had not yet written all the dirty blocks whose changes are recorded in that log. The log cannot be overwritten until that happens, so LGWR waits, and while it waits no session can generate redo. The usual cause is online redo logs that are too small for the database’s write rate, so LGWR laps the checkpoint during busy periods.</p><p><strong>What is the ‘log file switch (checkpoint incomplete)’ wait event?</strong></p><p>It is the wait that sessions experience when they need to write redo but the log writer cannot switch to the next online redo log, because that log is still needed for crash recovery until a checkpoint completes. Every session that inserts, updates, deletes or commits waits on it at the same time, which is why it shows up as the whole database stalling rather than one slow query. In the companion lab, a 100,000-row update spent 11.9 of its 14.1 seconds on this wait with two 20 MB redo logs, and zero seconds after resizing them.</p><p><strong>How do I resize Oracle online redo logs?</strong></p><p>Online redo log files cannot be resized in place. Add new log groups at the desired size with ALTER DATABASE ADD LOGFILE GROUP n … SIZE, run ALTER SYSTEM SWITCH LOGFILE until one of the new groups is CURRENT, run ALTER SYSTEM CHECKPOINT, wait until V$LOG shows the old groups as INACTIVE, then drop them with ALTER DATABASE DROP LOGFILE GROUP n and delete their files at the operating-system level (Oracle Managed Files are removed automatically). The whole procedure is online. Keep all groups the same size, and in a Data Guard configuration resize the standby redo logs to match.</p><p><strong>How big should Oracle redo logs be?</strong></p><p>Big enough that, at peak load, a log lasts long enough for the checkpoint covering the previous logs to finish well before the log writer needs them again. A common rule of thumb is a few log switches per hour at the busiest time, roughly one every 15 to 20 minutes. Check V$LOG_HISTORY for switches per hour; switches per minute mean the logs are far too small. V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE also suggests a size in megabytes, but only when FAST_START_MTTR_TARGET is set. Use at least three groups, all the same size.</p><p><strong>Is ‘Checkpoint not complete’ the same as ORA-00257 archiver error?</strong></p><p>No, although both freeze every session that writes. With ‘Checkpoint not complete’, the next online redo log cannot be reused because its checkpoint has not finished, and sessions wait on log file switch (checkpoint incomplete); the fix is usually larger or more redo log groups. With ORA-00257, the database is in ARCHIVELOG mode and the next log cannot be reused because the archiver has not been able to copy it, typically because the archive destination or Fast Recovery Area is full; sessions wait on log file switch (archiving needed), and the fix is to free or enlarge the archive destination.</p><p><strong>Do larger redo logs make crash recovery slower?</strong></p><p>Not in a way that should stop you from sizing them properly. Crash recovery time depends on how much redo must be applied since the last checkpoint position, and modern Oracle uses incremental checkpointing to write dirty blocks continuously rather than only at log switches. With FAST_START_MTTR_TARGET at 0, how far that checkpoint may lag is tied to the size of the smallest redo log, so larger logs can mean more redo to replay. Setting FAST_START_MTTR_TARGET makes the database pace its checkpoint writes to keep the estimated recovery time within the target, so set it when you enlarge the logs. Undersized logs, on the other hand, stall every writer on every busy day.</p><p>Redo sizing is one of those settings that is decided once, at database creation, and then forgotten until the workload outgrows it. The symptoms point everywhere else: the <a href="https://uptimearchitect.com/blog/oracle-wait-events-decoded/">wait events</a> look like I/O, the batch looks slow, and the SQL looks guilty. Check the alert log for Checkpoint not complete, count your log switches per hour, and if the logs are lapping the checkpoint, give them room: add bigger groups, switch, checkpoint, drop the old ones, and resize the standby redo logs to match. Then prove it the way that ends the argument - with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/checkpoint-incomplete">checkpoint-incomplete lab</a>, where the same update drops from 14.1 seconds, 11.9 of them waiting, to 1.9.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-checkpoint-not-complete/"><em>https://uptimearchitect.com</em></a><em> on October 13, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=53b58b6bddb1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Same SQL, Fast for One Value, Slow for Another]]></title>
            <link>https://levelup.gitconnected.com/same-sql-fast-for-one-value-slow-for-another-226202e67a4f?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/226202e67a4f</guid>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[performance]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Sat, 10 Oct 2026 05:00:27 GMT</pubDate>
            <atom:updated>2026-10-21T16:23:24.300Z</atom:updated>
            <content:encoded><![CDATA[<p>The order-history screen is fast for every customer except one. Or it’s fast all morning and crawls all afternoon. Or it was fine for months and became slow the night after a restart, with nobody deploying anything. You pull the statement and run it yourself with the problem value, and it comes back instantly. The SQL is identical. The data hasn’t changed. The plan in the execution history is the same plan it always uses.</p><p>That last sentence is the clue. The statement uses a <strong>bind variable</strong>, which is the right thing to do, and the cursor for it holds <strong>one plan</strong>. That plan was chosen for whichever value happened to arrive first. On a column where values are wildly unevenly distributed, that’s a plan built for one kind of caller and handed to every other. This post is how that happens, why Oracle’s safety net for it, adaptive cursor sharing, doesn’t always catch it, how to see it in the cursor cache, and how to fix it, with a lab that reproduces it on demand.</p><h3>Bind peeking: the first caller picks the plan</h3><p>When a statement with a bind variable is hard-parsed, the optimizer <strong>peeks</strong> at the actual bind value and costs the plan as if that value were a literal. That’s usually good: a plan built for a real value beats a plan built for an average. The catch is what happens next. The finished cursor goes into the shared pool and <strong>every later execution reuses it</strong>, whatever value it binds. The plan is decided once, by the first caller.</p><p>On a column with evenly spread values, that doesn’t matter: one value costs about the same as another. It matters on a <strong>skewed</strong> column, where a few values are rare and one is everywhere: a status code where 99.9% of rows are CLOSED, a country column dominated by one country, a flag that&#39;s almost always N. If the column has a <strong>histogram</strong>, the optimizer knows about the skew and builds very different plans for the rare value and the common one. Peeking means it builds <strong>one</strong> of them, and everyone gets that one.</p><p>The classic shape is a join. For a rare value, a few hundred driving rows, the best plan is <strong>nested loops</strong>: for each order, look up its customer by primary key. For the common value, hundreds of thousands of rows, the best plan is a <strong>hash join</strong>: read both tables once and match them in memory. Nested loops for the common value means one index probe per row, hundreds of thousands of times.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/708/0*beD_OwdTTfT1VwXF" /></figure><p>A bind-variable statement is optimized once, for the first value it sees, and the cursor is reused for every value after. On a skewed column with a histogram, a rare first value produces a nested-loops plan that is right for a few rows; the common value then inherits it and pays for one index probe per row. Adaptive cursor sharing can split the cursor into one plan per kind of value, but only once it has seen executions that differ enough; until then every caller gets the first caller’s plan.</p><h3>Adaptive cursor sharing: the safety net, with holes</h3><p>Oracle has known about this since 11g, and <strong>adaptive cursor sharing</strong> (ACS) is its answer. A cursor that peeked a bind on a column with a histogram is marked <strong>bind-sensitive</strong>. Oracle then watches its executions, sorting each one into a small number of buckets by how much work it did. If executions with different bind values land in clearly different buckets, the cursor becomes <strong>bind-aware</strong>: from then on, each new value is checked against the existing child cursors, and a value that needs a different plan gets its own child cursor with its own plan.</p><p>That works well when the difference is visible. The hole is in “clearly different”. If the rare value’s first execution and the common value’s executions land in the <strong>same bucket</strong>, ACS sees nothing to react to, and the cursor stays exactly as it was. And whether you’re protected can depend on <strong>the order in which values arrive</strong> after each restart, shared pool flush, or statistics refresh, which nobody controls.</p><h3>Prove it</h3><p>The lab builds ORDERS with 200,000 rows and a skewed STATUS, <strong>200 </strong><strong>OPEN and 199,800 </strong><strong>CLOSED</strong>, with a frequency histogram on the column, and joins it to a 1,000,000-row CUSTOMERS table. It flushes the shared pool, runs the statement once with&#39;OPEN&#39;, then five times with &#39;CLOSED&#39;, each in a fresh session, measuring session logical reads from V$MYSTAT and the child cursor each execution ran:</p><pre>&gt;&gt; Fresh shared pool. The SAME statement, bound:  ... where o.status = :s<br>   OPEN    rows=200     logical reads=2624     child cursor=0  plan=NESTED LOOPS<br>   CLOSED  rows=199800  logical reads=603391   child cursor=0  plan=NESTED LOOPS<br>   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS<br>   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS<br>   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS<br>   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS</pre><p>The optimizer peeked &#39;OPEN&#39;, estimated 199 rows from the histogram, and chose nested loops, which is right for 200 rows. Every&#39;CLOSED&#39; run then reused child cursor 0 and its nested loops: <strong>603,354 logical reads, five times in a row</strong>. Adaptive cursor sharing never marked the cursor bind-aware. The first &#39;OPEN&#39; execution, including its hard parse, and every&#39;CLOSED&#39; execution fell into the same middle bucket, so the safety net never saw a difference. (The plan was even an adaptive plan, with a statistics collector that can switch join methods, but an adaptive plan settles on its first execution and the settled plan is what gets reused.)</p><p>Give &#39;CLOSED&#39; its own plan and the cost collapses:</p><pre>&gt;&gt; The fix for a skewed, low-cardinality column: the predicate as a literal -&gt; one cursor + plan per value<br>   CLOSED  rows=199800  logical reads=34716    child cursor=0  plan=HASH JOIN<br>   OPEN    rows=200     logical reads=806      child cursor=0  plan=NESTED LOOPS</pre><p><strong>34,716 logical reads instead of 603,354</strong>: the same rows, 17 times less work, because the plan was built for the value it runs. And&#39;OPEN&#39; keeps its cheap nested loops at 806.</p><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/bind-peeking"><em>bind-peeking lab</em></a><em> builds the skewed tables on Oracle Database Free, peeks the rare value, and </em><strong><em>asserts</em></strong><em> that every common-value run inherits nested loops on the same cursor at 10 times or more the work of a hash join, without adaptive cursor sharing splitting it. Then it gives each value its own plan and asserts the common value drops to 10% or less of that work. If the trap stops reproducing or the fix stops working, the run fails. Proven on every CI push.</em></blockquote><h3>See it in your own system</h3><p>Start from the statement, not the plan. A query whose cost swings by value shows up as <strong>high variance in buffer gets per execution</strong> for one SQL_ID. Then look at its cursors:</p><pre>-- one row per child cursor: which ones are bind-sensitive / bind-aware, and what each costs<br>SELECT child_number, plan_hash_value, is_bind_sensitive, is_bind_aware,<br>       executions, ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec<br>FROM   v$sql<br>WHERE  sql_id = &#39;&amp;sql_id&#39;<br>ORDER  BY child_number;</pre><ul><li><strong>Bind-sensitive </strong><strong>Y, bind-aware </strong><strong>N, one child, huge gets per execution:</strong> the lab&#39;s situation. The cursor knows the bind matters, but it never split.</li><li><strong>Several children with different plan hash values, bind-aware </strong><strong>Y:</strong> ACS is doing its job.</li><li><strong>V$SQL_CS_HISTOGRAM</strong> shows how its executions were bucketed; all executions in one bucket means ACS has nothing to react to.</li></ul><p>To see which value the plan was built for, ask for the peeked binds:</p><pre>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(&#39;&amp;sql_id&#39;, 0, &#39;TYPICAL +PEEKED_BINDS&#39;));</pre><p>In the lab that prints :S ... &#39;OPEN&#39; above a nested-loops plan, the whole diagnosis in one screen. In AWR, the same statement appearing with <strong>more than one plan hash value</strong> over time, with very different elapsed times, is the historical fingerprint: the plan changes whenever the cursor is rebuilt with a different first value.</p><h3>The fix</h3><p>Pick by how the column behaves. In order of preference:</p><ul><li><strong>For a skewed, low-cardinality column, use a literal for that predicate.</strong> A status code with five values makes five cursors, each with the right plan. That’s no hard-parse storm: the storm comes from literal values with high cardinality, like IDs. Keep binds for everything else in the statement. This is the exception to “bind everything” that the<a href="https://uptimearchitect.com/blog/oracle-bind-variables-hard-parse/">bind variables post</a> ends on, and the lab’s fix.</li><li><strong>Split the query in the application</strong> when it already knows which kind of value it has: a separate statement for the “open orders” screen and the “history” screen. Two statements, two plans, no guessing.</li><li><strong>A SQL plan baseline with more than one accepted plan.</strong> Baselines cooperate with adaptive cursor sharing: accept the nested-loops plan and the hash-join plan, and each value can use the one that fits. See<a href="https://uptimearchitect.com/blog/oracle-sql-plan-management-baselines/">SQL plan management</a>.</li><li><strong>Remove the histogram</strong>, if one plan is genuinely acceptable for every value. Without it the optimizer stops distinguishing values and picks one middle-of-the-road plan. It’s a legitimate choice when no value is catastrophic, but a bad one when, as here, the wrong plan costs 17 times the work.</li></ul><p>What not to do: don’t “fix” it by flushing the shared pool or purging the cursor when it goes bad. That just rolls the dice again on which value arrives first, and the problem returns on its own schedule.</p><h3>What teams get wrong</h3><ul><li><strong>Blaming the data or the database for “random” slowness.</strong> It isn’t random: it’s the plan built for whichever value arrived first, and it changes when the cursor is rebuilt.</li><li><strong>Testing by hand.</strong> Paste the statement into a SQL tool with the slow value typed in and it’s different SQL text, so it gets its<em>own</em> hard parse and the right plan, and it’s fast. The problem only shows on the shared, bind-variable cursor the application uses.</li><li><strong>Trusting adaptive cursor sharing to handle it.</strong> It helps when executions differ enough to land in different buckets, and quietly does nothing when they don’t, as the lab shows five times in a row.</li><li><strong>Flushing the shared pool as the fix.</strong> It resets the lottery; it doesn’t change the odds.</li><li><strong>Disabling bind peeking database-wide.</strong> It’s a hidden parameter, and switching it off trades one wrong plan for a plan built for no real value at all, for every statement in the database.</li><li><strong>Turning a whole application to literals to escape it.</strong> That’s the <a href="https://uptimearchitect.com/blog/oracle-bind-variables-hard-parse/">hard-parse storm</a> all over again. Use literals only for the skewed, low-cardinality predicate.</li></ul><h3>Frequently asked questions</h3><p><strong>What is bind peeking in Oracle?</strong></p><p>Bind peeking is the optimizer’s behavior of looking at the actual value of a bind variable when it hard-parses a statement, and optimizing the plan as if that value were a literal. It produces a plan suited to a real value rather than to an average. The resulting cursor is then stored in the shared pool and reused by every later execution of the same statement, regardless of the value those executions bind. So the plan is effectively chosen by whichever value happened to arrive first when the cursor was built. On columns with evenly distributed values this rarely matters; on skewed columns with histograms it can make the same SQL fast for one value and very slow for another.</p><p><strong>Why is the same Oracle query fast for one value and slow for another?</strong></p><p>Usually because the statement uses a bind variable on a skewed column and the cursor’s single plan was built for a different kind of value. If the first execution peeked a rare value, the optimizer may have chosen nested loops or an index-driven plan that is ideal for a few rows; every later execution reuses that plan, and a common value matching hundreds of thousands of rows then pays for one index probe per row. In the companion lab the same join costs 2,624 logical reads for a rare value and 603,354 for the common value on the inherited plan, versus 34,716 with its own hash-join plan. Running the query by hand with the slow value often looks fast, because a manual run with different text gets its own hard parse and the right plan.</p><p><strong>What is adaptive cursor sharing?</strong></p><p>Adaptive cursor sharing, introduced in Oracle 11g, lets a statement with bind variables have more than one plan. A cursor that peeked a bind on a column with a histogram is marked bind-sensitive, and Oracle monitors how much work its executions do, sorting them into buckets. If executions with different bind values land in clearly different buckets, the cursor is marked bind-aware, and new bind values are then checked against the existing child cursors; a value that needs a different plan gets a new child cursor with its own plan. You can see it in V$SQL (IS_BIND_SENSITIVE, IS_BIND_AWARE and multiple child cursors) and in V$SQL_CS_HISTOGRAM.</p><p><strong>Why didn’t adaptive cursor sharing fix my plan?</strong></p><p>Because adaptive cursor sharing only reacts when it sees executions that differ enough to fall into different workload buckets. If the rare value’s first execution and the common value’s executions land in the same bucket, the cursor stays bind-sensitive but never becomes bind-aware, and every execution keeps using the original plan. In the companion lab the first execution with the rare value, including its hard parse, and all five executions with the common value fell into the same bucket, so the cursor never split even though the common value did 17 times the necessary work each time. Whether a given workload is protected can also depend on the order in which values arrive after a restart, a shared pool flush, or a statistics refresh.</p><p><strong>How do I find which bind value a plan was built for?</strong></p><p>Use DBMS_XPLAN.DISPLAY_CURSOR with the PEEKED_BINDS format option, for example SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’, child_number, ‘TYPICAL +PEEKED_BINDS’)). The output includes a Peeked Binds section showing the value the optimizer saw when it built that child cursor’s plan. Combine it with V$SQL for the same SQL_ID: one row per child cursor, its plan hash value, whether it is bind-sensitive and bind-aware, and buffer gets per execution. A single child with huge gets per execution and a peeked value from the rare end of a skewed column is the bind-peeking signature.</p><p><strong>Should I use literals instead of bind variables to avoid bind peeking problems?</strong></p><p>Only for the specific predicate on a skewed, low-cardinality column, such as a status code or a flag with a handful of values. Each distinct literal gets its own cursor and its own plan, and with only a few distinct values that means only a few cursors, which is no burden on the shared pool. Keep bind variables for high-cardinality values like IDs, names, and dates: using literals there causes a hard-parse storm, with thousands of nearly identical cursors, shared pool pressure, and latch contention. The rule is bind by default, literal by exception, and the exception is low-cardinality skew.</p><p><strong>Does flushing the shared pool fix a bad bind-peeked plan?</strong></p><p>Only by accident, and only temporarily. Flushing the shared pool, or purging the one cursor with DBMS_SHARED_POOL.PURGE, forces a new hard parse, and the new plan will be built for whichever bind value arrives first afterwards. If that is the common value, things look fixed until the next time the cursor is rebuilt with the rare value first; if it is the rare value, nothing changes. It resets the lottery without changing the odds. Fix the cause instead: give the skewed predicate a literal, split the statement in the application, or use a SQL plan baseline with more than one accepted plan.</p><p><strong>Should I disable bind peeking?</strong></p><p>No. Disabling bind peeking requires a hidden parameter, which should only be changed under Oracle Support’s guidance, and it applies to every statement in the database. Without peeking, the optimizer builds plans using average selectivity rather than any real value, so instead of a plan that is right for some values you get a plan that is right for none of the skewed ones. Address the specific statements that suffer: literals for low-cardinality skewed predicates, separate statements per case, SQL plan baselines, or removing a histogram that does more harm than good.</p><p>Bind peeking sits right at the meeting point of two other posts in this series. <a href="https://uptimearchitect.com/blog/oracle-bind-variables-hard-parse/">Bind variables</a> are why the cursor is shared in the first place, and that’s the right default; <a href="https://uptimearchitect.com/blog/oracle-indexes-help-vs-hurt/">indexes</a> are why the same predicate deserves an index for one value and a full scan for another. The plan itself is something you can read in <a href="https://uptimearchitect.com/blog/oracle-execution-plans-decoded/">the execution plan</a>, the histogram that sets the trap comes from <a href="https://uptimearchitect.com/blog/oracle-optimizer-statistics-demystified/">optimizer statistics</a>, and <a href="https://uptimearchitect.com/blog/oracle-sql-plan-management-baselines/">SQL plan management</a> is one way to pin more than one good plan. When one statement is fast for some values and slow for others, look at the peeked bind before anything else, and prove it the way that ends the argument — with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/bind-peeking">bind-peeking lab</a>, where the same query costs 2,624 reads for one value and 603,354 for another until each value gets its own plan.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-bind-peeking-adaptive-cursor-sharing/"><em>https://uptimearchitect.com</em></a><em> on October 10, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=226202e67a4f" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/same-sql-fast-for-one-value-slow-for-another-226202e67a4f">Same SQL, Fast for One Value, Slow for Another</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cutover Went Perfectly. The First New Order Failed With ORA-00001.]]></title>
            <link>https://levelup.gitconnected.com/cutover-went-perfectly-the-first-new-order-failed-with-ora-00001-f08c2c124fbd?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/f08c2c124fbd</guid>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[cloud-computing]]></category>
            <category><![CDATA[data-migration]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Sun, 04 Oct 2026 05:00:24 GMT</pubDate>
            <atom:updated>2026-11-11T16:13:03.098Z</atom:updated>
            <content:encoded><![CDATA[<p>The migration went by the book. The initial load ran days ahead. On cutover night the final data refresh finished early, row counts matched table for table, the reconciliation report was green, and the application was pointed at the new database. Ten minutes later the first customer placed an order and got an error page:</p><pre>ORA-00001: unique constraint (NEWSHOP.SYS_C008699) violated on table NEWSHOP.ORDERS columns (ID)</pre><p>Nothing was lost and nothing was corrupted. Every row was there. The problem was a number that no data check looks at: the <strong>sequence</strong> that generates new order IDs was still where the initial load left it, days earlier, and it was handing out IDs the source had already used. This post is how that happens, why rehearsals tend to miss it, the check that catches it before go-live, and the fix.</p><h3>Sequences are objects, not data</h3><p>An Oracle sequence is a schema object that hands out numbers: ORDER_SEQ.NEXTVAL returns 9,001, then 9,002, and so on. The application uses it for new keys. Crucially, the sequence&#39;s current position is part of its <strong>definition</strong>, not part of any table&#39;s data. When you export a sequence, Data Pump writes a CREATE SEQUENCE ... START WITH n statement, where n is where the sequence stood at that moment.</p><p>That one fact sets the trap. Many migrations work like this:</p><ol><li><strong>Initial load</strong>, days or weeks before cutover: export and import the whole schema, tables <em>and</em> sequences.</li><li><strong>The source keeps running.</strong> Customers keep placing orders; ORDER_SEQ keeps advancing on the source.</li><li><strong>Final refresh</strong> on cutover night: copy just the <strong>table data</strong> again, so the target catches up. With Data Pump that isCONTENT=DATA_ONLY with TABLE_EXISTS_ACTION=TRUNCATE; with other tools it&#39;s a final catch-up load or replication.</li></ol><p>Step 3 is deliberately data-only so it doesn’t touch the target’s objects, grants and code. Sequences are objects, so they are not touched either. The tables now contain every order up to 10,000; the sequence still thinks the next order is 9,001.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/526/0*v_T33o6gziqt-fJO" /></figure><p>The initial load copies the table and the sequence (next = 9,001). The source keeps taking orders up to id 10,000. The final refresh copies table data only, so the target has every row but its sequence never moved. The first insert after go-live asks the sequence for 9,001, which already exists, and fails with ORA-00001. Resetting the sequence past max(id) before go-live prevents it.</p><h3>Why rehearsals miss it</h3><p>Two things make this bug unusually good at surviving testing.</p><ul><li><strong>Every data check passes.</strong> Row counts, checksums and reconciliation reports compare data. The data is correct. Nothing in a typical validation compares a sequence’s position to the keys in its table.</li><li><strong>Cached sequences hide it.</strong> With a cached sequence, Data Pump exports the sequence’s LAST_NUMBER, which is the top of its in-memory cache, not the last value actually used. In the lab&#39;s first run, withCACHE 20 on a release that grows sequence caches dynamically, the source had used 9,000 values but the target sequence started at<strong>22,221</strong>. A rehearsal with a few hundred test orders never reaches that headroom. Production, with days of real orders between initial load and cutover, does.</li></ul><p>And when it does fail, it fails noisily but confusingly. A failed insert still consumes a NEXTVAL, so an application that retries walks the sequence forward one collision at a time. In the lab that is 1,000 failed orders before the sequence reaches a free key. Users see intermittent errors that &quot;fix themselves&quot;, which is the worst kind to diagnose under cutover pressure.</p><h3>Prove it</h3><p>The lab builds a source schema SHOP with 9,000 orders keyed by ORDER_SEQ, then runs the migration exactly as above with expdp/ impdp: a full initial load into NEWSHOP, 1,000 more orders on the source, and a final CONTENT=DATA_ONLY refresh.</p><pre>initial load    source  9,000 rows, max(id)  9,000 | target  9,000 rows, max(id)  9,000, ORDER_SEQ next 9,001<br>source drift    source 10,000 rows, max(id) 10,000 | target (unchanged)<br>final refresh   source 10,000 rows, max(id) 10,000 | target 10,000 rows, max(id) 10,000, ORDER_SEQ next 9,001<br>check           NEWSHOP.ORDER_SEQ  next=9001  max(ORDERS.ID)=10000  BEHIND by 1000<br>first insert    ORA-00001: unique constraint violated on table NEWSHOP.ORDERS columns (ID)<br>fix             ALTER SEQUENCE newshop.order_seq RESTART START WITH 10001<br>first insert    OK, new id = 10001</pre><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/sequence-cutover"><em>sequence-cutover lab</em></a><em> runs this whole migration on Oracle Database Free with real </em><em>expdp/ </em><em>impdp. It </em><strong><em>asserts</em></strong><em> that the row counts match after the final refresh, that the pre-go-live check flags the sequence as behind, that the first insert fails with ORA-00001, and that after the fix the same insert succeeds with a key above everything migrated. If any of those stops being true, the run fails. Proven on every CI push.</em></blockquote><h3>The check: run it before you open the doors</h3><p>For every sequence that feeds a key, compare the value it will hand out next with the highest key already in the table. Oracle doesn’t record which sequence feeds which column (unless it’s a column DEFAULT or an identity column), so the mapping belongs in the cutover runbook. The check itself is short:</p><pre>-- next value each sequence will hand out vs the highest key in the table it feeds<br>SELECT s.sequence_owner, s.sequence_name, s.last_number AS next_value,<br>       (SELECT MAX(id) FROM newshop.orders) AS max_key,<br>       CASE WHEN s.last_number &lt;= (SELECT MAX(id) FROM newshop.orders)<br>            THEN &#39;BEHIND&#39; ELSE &#39;ok&#39; END AS status<br>FROM   dba_sequences s<br>WHERE  s.sequence_owner = &#39;NEWSHOP&#39; AND s.sequence_name = &#39;ORDER_SEQ&#39;;</pre><p>The lab’s version loops over a list of sequence =&gt; table.column pairs, so one run covers every sequence in the application. Any BEHIND row is a guaranteed ORA-00001 waiting for its first insert.</p><h3>The fix</h3><p>Move each lagging sequence past the highest existing key:</p><pre>ALTER SEQUENCE newshop.order_seq RESTART START WITH 10001;   -- max(id) + 1</pre><p>RESTART START WITH (18c and later) resets the sequence in place, so grants, synonyms and dependent code stay intact. On older releases, either temporarily change INCREMENT BY to the gap, call NEXTVAL once and set it back, or drop and recreate the sequence and re-grant it. For <strong>identity columns</strong>, Oracle can do the arithmetic for you: ALTER TABLE newshop.orders MODIFY id GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE) moves the identity&#39;s next value past the current maximum.</p><p>The durable fix is in the runbook, not the database:</p><ul><li><strong>Make “resync sequences” an explicit cutover step</strong>, after the final data load and before go-live, usingmax(key) + 1 (plus a margin if anything else might still write).</li><li><strong>Or re-export sequences with the final data.</strong> Include the sequence objects in the final refresh and replace the target’s copies, so they carry the source’s position at cutover time.</li><li><strong>Run the check as a go/no-go gate.</strong> A cutover isn’t done when the rows match. It’s done when the sequence check reports noBEHIND.</li></ul><h3>What teams get wrong</h3><ul><li><strong>Treating “row counts match” as “migration verified”.</strong> Data checks prove the data. They say nothing about the objects that generate the next row.</li><li><strong>Rehearsing with too little traffic.</strong> A cached sequence’s export position includes headroom. If the rehearsal doesn’t generate more keys between initial load and refresh than that headroom, the bug never shows.</li><li><strong>Fixing it by dropping the primary key “temporarily”.</strong> That turns a loud error into silent duplicate IDs. Fix the sequence, never the constraint.</li><li><strong>Resetting to exactly </strong><strong>max(id).</strong> START WITH is the next value handed out, so it must be max(id) + 1.RESTART START WITH max(id) produces one more collision.</li><li><strong>Forgetting sequences that don’t feed a primary key.</strong> Invoice numbers, external reference IDs and other unique business keys come from sequences too, and collide the same way.</li></ul><h3>Frequently asked questions</h3><p><strong>Why do I get ORA-00001 right after a database migration?</strong></p><p>Usually because a sequence on the new database is behind the data. Migrations often load the schema once, including sequences, while the source keeps running, then refresh only the table data at cutover. The tables end up with every row, but each sequence is still at the position it had at the initial load, so its next values are keys that already exist in the table. The first inserts that use those sequences fail with ORA-00001 unique constraint violated. Nothing is lost or corrupted; the sequences just need to be moved past the highest existing key.</p><p><strong>Does Data Pump migrate sequence values?</strong></p><p>Data Pump exports a sequence as a CREATE SEQUENCE statement whose START WITH value is the sequence’s position at export time, specifically its LAST_NUMBER, which for a cached sequence is the top of the cache rather than the last value actually used. So a full export and import does carry the sequence position as of the moment of the export. What it cannot do is follow the source after that: if the source keeps generating values and you later refresh only table data (for example with CONTENT=DATA_ONLY), the target sequences stay at the old position.</p><p><strong>How do I check whether sequences are behind after a migration?</strong></p><p>For each sequence that feeds a key, compare the value it will hand out next with the maximum key in the table it feeds. DBA_SEQUENCES.LAST_NUMBER gives the next value that has not been cached yet, and SELECT MAX(column) gives the highest existing key; if LAST_NUMBER is less than or equal to the maximum, the sequence is behind and will produce a duplicate key. Oracle does not record which sequence feeds which column unless the column uses it as a DEFAULT or is an identity column, so keep the sequence-to-column mapping in the cutover runbook and run the check as a go/no-go step before go-live.</p><p><strong>How do I reset an Oracle sequence to a specific value?</strong></p><p>On Oracle 18c and later, use ALTER SEQUENCE owner.name RESTART START WITH n, where n is the next value you want handed out, typically the maximum existing key plus one. It resets the sequence in place and keeps its grants and dependents. On older releases, either temporarily set INCREMENT BY to the size of the gap, select NEXTVAL once, and set INCREMENT BY back to its original value, or drop and recreate the sequence with the right START WITH and re-grant it.</p><p><strong>What about identity columns?</strong></p><p>Identity columns are backed by an internal sequence and have the same problem if the table data is reloaded without moving that sequence. Oracle provides a direct fix: ALTER TABLE table_name MODIFY column_name GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE) sets the identity’s next value past the current maximum value in the column. Use the GENERATED clause that matches how the column is defined (ALWAYS or BY DEFAULT). You can see identity definitions in USER_TAB_IDENTITY_COLS.</p><p><strong>Why didn’t the problem show up in the migration rehearsal?</strong></p><p>Two reasons are common. First, rehearsals validate data (row counts, checksums, reconciliation), and the data really is correct; nothing compares sequence positions to keys. Second, cached sequences are exported at the top of their cache, which can be well ahead of the last value actually used, especially on releases that grow sequence caches dynamically. A rehearsal that generates only a little test activity between the initial load and the refresh never uses up that headroom, while days of production traffic do.</p><p><strong>Can I fix it by disabling the primary key constraint?</strong></p><p>No. Disabling or dropping the constraint makes the error disappear by allowing duplicate keys, which silently corrupts the data model and breaks anything that relies on the key being unique. The constraint is doing its job by refusing duplicates. The correct fix is to move the sequence past the highest existing key with ALTER SEQUENCE … RESTART START WITH max plus one, and to make sequence resynchronization an explicit step in the cutover runbook.</p><p>Sequence drift is the third of the migration failures that sail through a “successful” cutover, alongside <a href="https://uptimearchitect.com/blog/oracle-data-pump-done-right/">Data Pump jobs nobody counted</a> and <a href="https://uptimearchitect.com/blog/oracle-character-set-migration-data-loss/">character-set conversions that quietly damage data</a>. The pattern is the same each time: the tooling reports success, so verify the thing the tooling doesn’t check. Choose the method with the <a href="https://uptimearchitect.com/blog/oracle-cloud-migration-methods/">migration decision guide</a>, then make sequence resync and its check explicit steps in the cutover runbook. And prove it the way that ends the argument: with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/sequence-cutover">sequence-cutover lab</a>, where every row arrives intact and the first new order still fails until the sequence is moved.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-sequence-cutover-ora-00001/"><em>https://uptimearchitect.com</em></a><em> on October 4, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f08c2c124fbd" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/cutover-went-perfectly-the-first-new-order-failed-with-ora-00001-f08c2c124fbd">Cutover Went Perfectly. The First New Order Failed With ORA-00001.</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[You Deleted 99% of the Rows. The Full Scan Didn’t Get Any Faster.]]></title>
            <link>https://levelup.gitconnected.com/you-deleted-99-of-the-rows-the-full-scan-didnt-get-any-faster-e69b64f07da3?source=rss-5feaf78a15c7------2</link>
            <guid isPermaLink="false">https://medium.com/p/e69b64f07da3</guid>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[oracle]]></category>
            <dc:creator><![CDATA[Yaro Perventsev]]></dc:creator>
            <pubDate>Sat, 03 Oct 2026 05:00:09 GMT</pubDate>
            <atom:updated>2026-10-16T14:18:40.377Z</atom:updated>
            <content:encoded><![CDATA[<p>The audit table had grown to 200 million rows, so the team finally ran the purge: everything older than 90 days, gone. Ninety-nine percent of the rows deleted, committed, celebrated. The next morning the nightly report that full-scans that table ran exactly as long as it always had. Same I/O, same elapsed time, same line in the AWR report. The data was gone. The work wasn’t.</p><p>Nothing is broken here, and the delete did what it was told. The full scan simply doesn’t read <em>rows</em> — it reads <em>blocks</em>, every block up to a boundary called the <strong>high-water mark</strong>, and DELETE never moves that boundary. This post is what the high-water mark is, why a purge leaves it where it was, how to find tables carrying that dead weight, the fix, and a lab that measures the same scan before the delete, after it, and after the fix.</p><h3>What the high-water mark is</h3><p>A table is stored in a <strong>segment</strong>: a set of blocks the database allocates as the table grows. As rows are inserted, blocks get formatted and filled from the start of the segment outward. The <strong>high-water mark (HWM)</strong> is the boundary between blocks that have ever been used and blocks that never have.</p><p>A <strong>full table scan</strong> has no index telling it where the rows are, so it reads every block below the high-water mark and looks inside each one. Blocks above the HWM are skipped — they have never held data. Blocks below it are read whether they contain a thousand rows or none.</p><p>The optimizer works the same way: it costs a full scan from the table’s block count in its statistics. A table with a high HWM doesn’t just <em>run</em> like a big table, it <em>looks</em> like one to the optimizer too.</p><h3>Why DELETE doesn’t lower it</h3><p>DELETE removes rows from blocks. It does not hand the blocks back, and it does not move the high-water mark. The emptied blocks stay part of the segment, below the HWM, marked as having free space so that future inserts can reuse them. That is a sensible design for a table that will grow back - but for a table that was purged and won&#39;t, every full scan now reads thousands of blocks that are empty, or nearly so.</p><p>So the table’s cost to scan is set by its <strong>largest-ever size</strong>, not its current size. A purge that removes 99% of the rows removes 0% of the full-scan work.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/276/0*cOQTllxbdSeMrQwu" /></figure><p>A full table scan reads every block below the high-water mark, empty or not. Loading 200,000 rows pushes the HWM out to about 6,175 blocks. Deleting 99% of the rows empties those blocks but leaves the HWM where it was, so the same scan still reads about 6,000 blocks to find 2,000 rows. SHRINK SPACE packs the survivors into the start of the segment and lowers the HWM, and the scan drops to about 60 blocks.</p><h3>Prove it</h3><p>The lab loads a 200,000-row table (about 200 bytes a row) and measures one full scan — SELECT /*+ FULL(t) */ COUNT(pad) FROM hwm.t - in consistent gets, the logical block reads the scan performs, taken from V$MYSTAT. Then it deletes 99% of the rows, commits, and runs the <em>same</em> scan:</p><pre>                       rows   full-scan gets   blocks below HWM<br>loaded              200,000            6,074              6,175<br>after DELETE 99%      2,000            6,074              6,175<br>after SHRINK SPACE    2,000               64                 62</pre><p>One percent of the rows, one hundred percent of the work — until the high-water mark comes down. After the shrink, the scan’s cost follows the data: 64 gets for 2,000 rows.</p><blockquote><strong><em>Don’t take my word for it — run it.</em></strong><em> The </em><a href="https://github.com/pyaroslav/oracle-labs/tree/main/hwm-bloat"><em>high-water mark lab</em></a><em> loads the table on Oracle Database Free, measures the full scan, deletes 99% of the rows and </em><strong><em>asserts</em></strong><em> the scan still costs at least 90% of the baseline, then runs </em><em>SHRINK SPACE and asserts it drops to 5% or less with every remaining row intact. If the bloat doesn&#39;t reproduce, or the shrink doesn&#39;t fix it, the run fails. Proven on every CI push.</em></blockquote><h3>How to find bloated tables</h3><p>You don’t need to guess. Compare what a table occupies with what its rows actually need, using fresh optimizer statistics in DBA_TABLES:</p><pre>SELECT owner, table_name, num_rows, blocks,<br>       CEIL(num_rows * avg_row_len / (8192 * 0.9))                         AS blocks_needed,<br>       ROUND(100 * (1 - (num_rows * avg_row_len / (8192 * 0.9))<br>                        / NULLIF(blocks, 0)))                              AS pct_wasted<br>FROM   dba_tables<br>WHERE  owner = &#39;APP&#39;<br>AND    blocks &gt; 1000<br>ORDER  BY blocks - CEIL(num_rows * avg_row_len / (8192 * 0.9)) DESC;</pre><p>blocks is the space below the high-water mark; blocks_needed estimates the space the current rows would fill at about 90% density (adjust 8192 if your block size differs). In the lab, after the delete, this reports <strong>6,175 blocks</strong> for rows that need about <strong>58</strong> - 99% wasted. Two caveats: it is only as current as the statistics, so gather first; and it is an estimate, so treat it as a ranking of where to look, not a precise measurement. For precise per-block fullness, DBMS_SPACE.SPACE_USAGE reports how many blocks are empty, 0-25% full, and so on; the database&#39;s automatic <strong>Segment Advisor</strong> also flags segments with reclaimable space.</p><p>Prioritise tables that are both bloated and <strong>full-scanned</strong>: check AWR or V$SQL_PLAN for TABLE ACCESS FULL on them. A bloated table that is only ever read by primary key costs you disk space, not query time.</p><h3>The fix</h3><p><strong>SHRINK SPACE</strong> compacts the rows into the start of the segment, lowers the high-water mark and releases the freed space:</p><pre>ALTER TABLE app.audit_log ENABLE ROW MOVEMENT;<br>ALTER TABLE app.audit_log SHRINK SPACE;</pre><ul><li>It needs <strong>row movement</strong>, because rows are physically relocated and get new ROWIDs.</li><li>It works on tables in <strong>ASSM</strong> (automatic segment space management) tablespaces, which is the default.</li><li>It is <strong>online</strong>: DML continues during the compaction, with only a brief lock at the end to move the HWM.</li><li><strong>Indexes stay usable</strong> and are maintained as rows move. Add CASCADE to shrink dependent indexes too.</li><li><strong>SHRINK SPACE COMPACT</strong> does the row movement but stops short of moving the HWM, so you can split the work: compact during the day, then run a quickSHRINK SPACE in a quiet window.</li></ul><p><strong>ALTER TABLE ... MOVE ONLINE</strong> (12.2 and later) is the alternative: it rebuilds the table into a new segment, keeps indexes usable, and allows DML throughout, at the cost of needing space for a second copy while it runs. A plain MOVE without ONLINE blocks DML and leaves every index UNUSABLE until rebuilt - a common way to turn a cleanup into an outage. A handful of segment types can&#39;t be shrunk; MOVE ONLINE is the usual fallback.</p><p>After either one, <strong>gather statistics</strong>, so the optimizer sees the new, smaller block count.</p><h3>What teams get wrong</h3><ul><li><strong>Expecting </strong><strong>DELETE to make things faster.</strong> It reduces rows, not blocks. Full scans, and the optimizer&#39;s costing of them, keep paying for the table&#39;s largest-ever size until the HWM is lowered.</li><li><strong>Running the purge as one giant </strong><strong>DELETE every time.</strong> For data that ages out by date, partition by date and drop or truncate old partitions instead: it releases the space immediately, generates almost no undo, and lets queries skip old data entirely with<a href="https://uptimearchitect.com/blog/oracle-partition-pruning/">partition pruning</a>.</li><li><strong>Using </strong><strong>MOVE without </strong><strong>ONLINE.</strong> Every index on the table goes UNUSABLE, and queries relying on them fail or fall back to full scans until someone rebuilds them.</li><li><strong>Shrinking tables that will grow straight back.</strong> Conventional inserts reuse the free space below the HWM. If a table is purged and then refilled on a cycle, shrinking it just makes the next cycle re-allocate the same space. Shrink tables that were purged and will stay small.</li><li><strong>Forgetting direct-path loads.</strong> INSERT /*+ APPEND */ always writes above the high-water mark and never reuses free space below it. A table loaded by direct path and purged byDELETE grows forever.</li><li><strong>Skipping the statistics afterwards.</strong> Until stats are refreshed, the optimizer still believes the table is thousands of blocks and costs plans accordingly.</li></ul><h3>Frequently asked questions</h3><p><strong>What is the high-water mark in Oracle?</strong></p><p>The high-water mark (HWM) is the boundary in a table’s segment between blocks that have been used to store data at some point and blocks that never have. As rows are inserted, the database formats and fills blocks from the start of the segment outward, pushing the high-water mark up. A full table scan reads every block below the high-water mark, whether those blocks currently contain rows or are empty, and skips the blocks above it. The optimizer also uses the table’s block count from statistics, which reflects the space below the high-water mark, to cost full scans.</p><p><strong>Why doesn’t DELETE make a full table scan faster?</strong></p><p>Because DELETE removes rows from blocks but does not release the blocks or lower the high-water mark. The emptied blocks remain part of the table’s segment, below the high-water mark, marked as having free space for future inserts. A full table scan reads every block below the high-water mark, so after deleting 99% of the rows it still reads almost exactly the same number of blocks. In the companion lab the same full scan costs 6,074 consistent gets with 200,000 rows and still 6,074 after deleting all but 2,000 of them. Only lowering the high-water mark, with SHRINK SPACE, MOVE, or TRUNCATE, reduces the scan’s work.</p><p><strong>How do I lower the high-water mark of an Oracle table?</strong></p><p>The usual online method is ALTER TABLE … ENABLE ROW MOVEMENT followed by ALTER TABLE … SHRINK SPACE, which compacts the rows toward the start of the segment, lowers the high-water mark, and releases the freed space while allowing concurrent DML. It requires an ASSM tablespace, which is the default. Alternatively, ALTER TABLE … MOVE ONLINE (12.2 and later) rebuilds the table into a new segment while keeping indexes usable. TRUNCATE also resets the high-water mark instantly but removes every row. After any of these, gather optimizer statistics so plans reflect the smaller table.</p><p><strong>What is the difference between SHRINK SPACE and SHRINK SPACE COMPACT?</strong></p><p>SHRINK SPACE does two phases in one command: it moves rows toward the beginning of the segment, then lowers the high-water mark and releases the space, taking a brief lock at the end to do so. SHRINK SPACE COMPACT performs only the first phase, the row movement, and leaves the high-water mark where it is. That lets you split the work: run the compaction, which can take a while on a large table, during normal hours, and later run a plain SHRINK SPACE in a quiet window, which then completes quickly because the rows are already packed. Adding CASCADE also shrinks the table’s dependent indexes.</p><p><strong>Does SHRINK SPACE lock the table or invalidate indexes?</strong></p><p>SHRINK SPACE is an online operation: queries and DML can continue while rows are being compacted, and only a short exclusive lock is taken at the end to adjust the high-water mark. Indexes remain usable because they are maintained as rows move to new locations. Row movement must be enabled first because rows receive new ROWIDs, so anything that stores ROWIDs long-term would need to tolerate that. This differs from a plain ALTER TABLE … MOVE without the ONLINE keyword, which blocks DML and marks every index on the table UNUSABLE until it is rebuilt.</p><p><strong>How can I find tables with a lot of wasted space below the high-water mark?</strong></p><p>After gathering fresh statistics, compare each table’s BLOCKS in DBA_TABLES, which is the space below the high-water mark, with the space its rows need, estimated as NUM_ROWS times AVG_ROW_LEN divided by the usable bytes per block (for example 8192 times 0.9). Tables where BLOCKS is far larger than that estimate are carrying dead space; in the companion lab a purged table shows 6,175 blocks against about 58 needed. DBMS_SPACE.SPACE_USAGE gives an exact breakdown of how full the blocks are, and the automatic Segment Advisor reports segments with reclaimable space. Focus on bloated tables that are also read by full scans, since those are where the space turns into query time.</p><p><strong>Should I shrink every table after deleting rows?</strong></p><p>No. Conventional inserts reuse the free space below the high-water mark, so a table that is regularly purged and then refilled will simply grow back into the same space, and shrinking it only adds work. Shrinking pays off for tables that were purged and will stay much smaller, and that are read by full scans. For data that ages out by date on a schedule, partitioning by date and dropping or truncating old partitions is usually better than repeated DELETE plus SHRINK: it releases space immediately, generates little undo and redo, and lets queries skip old partitions entirely.</p><p>High-water mark bloat is the same lesson the rest of the performance series keeps arriving at from different directions: the cost of a query is the work the engine actually does, not the size of the answer. <a href="https://uptimearchitect.com/blog/oracle-execution-plans-decoded/">Reading the execution plan</a> shows you a TABLE ACCESS FULL; <a href="https://uptimearchitect.com/blog/oracle-indexes-help-vs-hurt/">the right index</a> avoids reading the whole table at all; <a href="https://uptimearchitect.com/blog/oracle-partition-pruning/">partition pruning</a> skips the parts you don&#39;t need; and <a href="https://uptimearchitect.com/blog/oracle-optimizer-statistics-demystified/">fresh statistics</a> make sure the optimizer is costing the table it actually has. When a purge didn&#39;t make anything faster, check the high-water mark: find the tables whose blocks dwarf their rows, shrink the ones that are full-scanned and won&#39;t grow back, and prove it the way that ends the argument - with the <a href="https://github.com/pyaroslav/oracle-labs/tree/main/hwm-bloat">high-water mark lab</a>, where deleting 99% of a table changes nothing until the shrink drops the same scan from 6,074 gets to 64.</p><p><em>Originally published at </em><a href="https://uptimearchitect.com/blog/oracle-high-water-mark-bloat/"><em>https://uptimearchitect.com</em></a><em> on October 3, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e69b64f07da3" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/you-deleted-99-of-the-rows-the-full-scan-didnt-get-any-faster-e69b64f07da3">You Deleted 99% of the Rows. The Full Scan Didn’t Get Any Faster.</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>