If you went through the above checklist and the problem is still occurring, this means that we have something going wrong in the JDBC configuration. It could be one of the following:. To resolve this issue, you will need to change the JDBC database configuration. Configuring an Oracle Datasource in Apache Tomcat.
Atlassian Support Confluence 7. Related content No related content found. Still need help? The Atlassian Community is here for you. Was this helpful?
Yes No It wasn't accurate. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?
Collectives on Stack Overflow. Learn more. Asked 13 years, 1 month ago. Active 3 years, 11 months ago. Viewed 84k times. Oracle FAQ defines temp table space as follows: Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. Simon Gibbs Simon Gibbs 4, 6 6 gold badges 49 49 silver badges 77 77 bronze badges.
Add a comment. Active Oldest Votes. SID , a. SID ',' a. You can set a diagnostic event for the ORA error in your individual database session with the following statement:. The above statement will affect the current instance only and will not edit the server parameter file. That is to say, if you stop and restart the instance, the diagnostic event setting will no longer be active. You can deactivate the ORA diagnostic event or remove all diagnostic event settings from the server parameter file with statements such as the following:.
The entry in the instance alert log that indicates an ORA error occurred will also indicate that a trace file was written. An entry in the instance alert log will look like this:. From the trace file you can clearly see the full text of the SQL statement that failed. You can also see when it failed along with attributes of the database session such as module, action, and service name.
It is important to note that the statements captured in trace files with this method may not themselves be the cause of space issues in the temporary tablespace. For example, one query could run successfully and consume The second query is the one that will get captured in a trace file, while the first query is more likely to be the root cause of the problem. The trace file will contain additional information, including a call stack trace and a binary stack dump.
This information is not likely to be useful, unless perhaps you want to learn more about Oracle internals. The diagnostic event facility has been built into the Oracle database product for a very long time, but it is not widely documented. In general, you absolutely should not try setting diagnostic events in a production database unless you have a very good idea of what they do. Although I am not aware of an Oracle Support document that officially blesses setting diagnostic event for identifying SQL statements that fail due to lack of sort space, there are bulletins on Metalink that do show how to set events to dump an error stack for basic Oracle errors.
Metalink document We are doing the exact same thing here for the ORA error, and therefore it seems like a relatively safe thing to do. Like most debugging or diagnostic facilities, you should only use the ORA diagnostic event to the extent you really need to. Instead of waiting for a temporary tablespace to fill and for statements to fail, you can monitor temporary space usage in the database in real time. The following query displays information about all sort segments in the database.
Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.
The query displays for each sort segment in the database the tablespace the segment resides in, the size of the tablespace, the amount of space within the sort segment that is currently in use, and the amount of space available. Sample output from this query is as follows:. Right now, 9 MB of the sort segment is in use, leaving a total of 9, MB available for additional sort operations.
Note that the available space may consist of unused blocks within the sort segment, unallocated extents in the TEMP tablespace, or a combination of the two. The following query displays information about each database session that is using space in a sort segment. Although one session may have many sort operations active at once, this query summarizes the information by session. The query displays information about each database session that is using space in a sort segment, along with the amount of sort space and the temporary tablespace being used, and the number of sort operations in that session that are using sort space.
This example shows that there is one database session using sort segment space. The connection was initiated by the httpd db1 process running under the rpk operating system user, and the Oracle server process has operating system process ID The following query displays information about each statement that is using space in a sort segment.
The query displays information about each statement using space in a sort segment, including information about the database session that issued the statement and the temporary tablespace and amount of sort space being used.
0コメント