Return
-
Title
ALTER VIEW ORA 43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set. -
Description
Sometimes ALTER VIEW > View Info tab improperly attempts to set "Default Collation" to USING_NLS_COMP. For my purposes, this is incorrect as default collation is not required for the view. Following the suggested ORA-43929 corrective action goes down a very dangerous rabbit hole where the MAX_STRING_SIZE is irreversibly switched to EXTENDED and utl32k is run - which can result in the database being un-openable and must be restored from backup. Is there a way to default to "Default Collation"=Default? -
Resolution
If max_string_size = STANDARD, Toad will not include the DEFAULT COLLATION clause. However, if user does not have the SELECT privilege on the SYS.V_$PARAMETER view, then Toad doesn't know what the max_string_size is and includes the Default Collation clause. So the problem will go away If user can be granted select on SYS.V_$PARAMETER. Toad is not adding the Default Collation clause just because the user is putting a DB Link in the query. Toad is adding it because it sees the collation specified in USER/ALL/DBA_VIEWS (and doesn't know the max string size).Toad should not specify collation when it is the default, and when we cannot determine the database's max string size due to lack of privileges. This will be fixed in Toad.As a workaround, if user blanks out the default collation in the "view info" tab of the alter view window, then Toad will not specify Default Collation in the ALTER VIEW statement.Also, if user is seeing the DEFAULT COLLATION clause in Schema Browser | Views | Scripts, user can get rid of that by:1) Click first toolbar button on Script Tab.2) Go to "Storage Objects" tab.3) Change the "Collation" option to "Only when non-default".STATUS:Waiting for fix in a future release of Toad for Oracle. -
Defect ID
TOR-3360