SQL Server TEMPDB: /f mode to change TEMPDB

To remove or re-size any files belonging to TEMPDB:

net stop MSSQLSERVER

NET START MSSQLSERVER /f /mSQLCMD

PS C:\tmp> sqlcmd -S SERVER -E

use TEMPDB

go

DBCC SHRINKFILE (temp5, EMPTYFILE);

USE master

GO

ALTER DATABASE tempdb REMOVE FILE temp5;

go

DBCC REPAIR_REBUILD

USE Prod_Clone

GO

ALTER DATABASE Prod_Clone SET SINGLE_USER

GO

DBCC CHECKDB('Prod_Clone', REPAIR_REBUILD)

GO

ALTER DATABASE Prod_Clone SET MULTI_USER

GO

ORA-01652: Unable to Extend Temp Segment in RAC (Doc ID 1534590.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

Temporary tablespace space allocation fails in RAC even when there is still free temp space.

ORA-12801: error signaled in parallel query server P017

ORA-01652: unable to extend temp segment by 640 in tablespace XY_TEMP

 

CAUSE

Unbalanced temp space distribution in RAC.  One instance seems to consume and cache most of the temp space, causing another instance to hit the ora-1652.

SQL> select inst_id, tablespace_name, round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)"

2 from gv$sort_segment

3 where tablespace_name='XY_TEMP'

4 order by 1;

INST_ID  TABLESPACE_NAME        Space(GB)

---------- ------------------------------ ----------

1           XY_TEMP                           33.39

2           XY_TEMP                           33.77

3           XY_TEMP                           34.16

4           XY_TEMP                           33.39

5           XY_TEMP                           33.46

6           XY_TEMP                       1118.79   <<<<very unbalanced

7           XY_TEMP                           33.28

8           XY_TEMP                           34.26

This is reported in Bug 14383007 - sort runs out of temp space on 2 nodes even when temp space is available

This bug will be fixed in 11.2.0.4 (future release). Refer < Document 14383007.8> for more details.

Useful queries for debugging:

Collect the information every few seconds:

1. select  * from gv$sort_segment

2. select sum(bytes), owner from gv$temp_extent_map group by owner;

3. select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;

SOLUTION

Workaround is:

Retry the operation.

One-off patch 14383007 has been provided for certain platform, please check My Oracle Support for patch detail.

REFERENCES

NOTE:14383007.8 - Bug 14383007 - Sort runs out of temp space in RAC even when temp space is available

"Windows cannot access the specified device..." when installing from a CD/DVD

 In Group Policy:

Computer Configuration -->Administrative Templates --> System --> Removable Storage Access:

CD and DVD: Deny execute access

Find a string by searching all tables in SQL Server Management Studio

 USE DATABASE_NAME

DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'

DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET  @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

    SET @ColumnName = ''

    SET @TableName = 

    (

        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

        FROM     INFORMATION_SCHEMA.TABLES

        WHERE         TABLE_TYPE = 'BASE TABLE'

            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

            AND    OBJECTPROPERTY(

                    OBJECT_ID(

                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                         ), 'IsMSShipped'

                           ) = 0

    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN

        SET @ColumnName =

        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))

            FROM     INFORMATION_SCHEMA.COLUMNS

            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)

                AND    TABLE_NAME    = PARSENAME(@TableName, 1)

                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')

                AND    QUOTENAME(COLUMN_NAME) > @ColumnName

        )

        IF @ColumnName IS NOT NULL

        BEGIN

            INSERT INTO @Results

            EXEC

            (

                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 

                FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

            )

        END

    END    

END

SELECT ColumnName, ColumnValue FROM @Results

SQL Server: Determine the transaction log usage

 DBCC SQLPERF (logspace)

Returns the current size of the transaction log and the percentage of log space used for each database. Use this information to monitor the amount of space used in a transaction log.

Oracle hint: ignore_row_on_dupkey_index

 The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.

The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.

The semantic effect of this hint results in error messages if specific rules are violated:

If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.

You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.

You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.

As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.