MAX_COLUMNS parameter in Oracle Database 23ai

Before Oracle 23ai, the maximum allowed columns in a table is 1000. From Oracle 23ai you can increase this value by modifying a parameter MAX_COLUMNS up to 4096 if you have any use case. This initialization parameter can be set at system level only and in case of PDB you can limit to specific PDB. 

To use this MAX_COLUMN parameter the compatibility should be set to 23.0.0.0 or higher. To increase max allowed colums you must set the MAX_COLUMNS value to “EXTENDED”. Note that you can change MAX_COLUMNS from STANDARD to EXTENDED any time but to change the value back to STANDARD only when any table or view in the database contains 1000 or fewer columns. 

By default, the MAX_COLUMNS initialization parameter is set to STANDARD

SQL>show parameters max_columns
NAME                                TYPE      VALUE
-------------------------------- ----------- -------------------------
max_columns                      string       STANDARD

When user trying to add columns more than 1000 in a table will receive below error

ORA-01792: maximum number of columns in a table or view is 1000

You can change MAX_COLUMNS value using below command that will allow up to 4096 columns

SQL> ALTER SYSTEM set MAX_COLUMNS=EXTENDED scope=spfile;
SQL> shutdown immediate;
SQL> startup

If the database has tables with more than 1000 columns and trying to update the MAX_COLUMNS parameter value back to STANDARD, then user should receive below error

SQL> ALTER SYSTEM SET set MAX_COLUMNS =STANDARD scope=spfile;

ORA-32017: failure in updating SPFILE
ORA-60471: max_columns cannot be set to STANDARD as there are one or more objects with more than 1000 columns

The only way user can change this value by dropping the objects with more than 1000 columns.

Note that older Oracle client versions (before Oracle 23ai) do not support columns more than 1000 in a table and only Oracle 23ai clients support the 4096 column limit.

Thanks & Regards,

https://oracleracexpert.com

Tableau Server Unlicensed after activating the license

User might encounter this License issue after deactivating and activating license on Tableau Server and it shows as Expired or Ended.

Error: Unlicensed Go to the licensing page for more details

When you try to deactivate and active you might receive error

The requested licensing operation failed with error code '110: TABLEAU_ERROR_LICENSING_UNLICENSED_GENERAL'.”

When you run “tsm licenses list” it will show Creator/Explorer/Viewer as “0” (Zero)

This is a known issue and happens for Tableau server 2024.2.1 and later when the installation is not using ATR.

Workaround: We don’t have fix at this time, but we have workaround and user should follow below steps

1. Stop Tableau Server using CMD (tsm stop) or TSM Web GUI
2. Restart O/S
3. Start Tableau Server using CMD (tsm start) or TSM Web UI

Once the table server is up you can run below TMS command to confirm the license expire date is set t as expected or you can check the license on TMS Web UI

C:\> tsm licenses list

Pls refer below KB’s for more Details

Error code '101' or '100' TABLEAU_ERROR_LICENSING_INTERNAL when Activating Tableau Server | Tableau Software

Immediately after activating a Tableau Server license, it shows as "Ended"

Thanks & Regards,

https://oracleracexpert.com

DBMS_SPACE package enhancements in Oracle 23ai

The DBMS_SPACE package used to analyze segment growth and space requirements in the database. From Oracle 23ai onwards you can use this package to shrink a big file tablespace to reclaim unused space. In case if there are any unsupported objects you can identify in analyze phase.

First analyze big tablespace to see how much space can be reclaimed before performing the shrink. You need to provide tablespace name and shrink and shrink mode to perform this task.

• TS_MODE_ANALYZE – This mode analyzes the tablespace
• TS_MODE_SHRINK – This mode is default, and it shrinks tablespace to reclaim space
• TS_MODE_SHRINK_FORCE - This mode moves objects online and in case of failure it will attempt to move offline.

You should set “serveroutput” g on to show the results on the terminal
SQL> set serveroutput on;

Analyze how much space can be reclaimed using TS_MODE_ANALYZE mode

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’, SHRINK_MODE => DBMS_SPACE.TS_MODE_ANALYZE);
PL/SQL procedure successfully completed.

Here is the simplified version of the output

ANALYZE RESULTS
Total Movable Objects: 2
Total Movable Size(GB): 1.32
Original Datafile Size(GB): 5
Suggested Target Size(GB): 2.59
Process Time: +00 00:00:00.701704

Now you can perform the actual shrink operation to reclaim the space, note that DBMS_SPACE.TS_MODE_SHRINK is used by default if you don’t mention explicitly. If you don’t mention any TARGET_SIZE by default it will use DBMS_SPACE.TS_TARGET_MAX_SHRINK

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’);
PL/SQL procedure successfully completed.

Or

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’, SHRINK_MODE => DBMS_SPACE.TS_MODE_SHRINK);
PL/SQL procedure successfully completed.

Here is the simplified version of the output

SHRINK RESULTS
Total Moved Objects: 2
Total Moved Size(GB): 1.32
Original Datafile Size(GB): 5
New Datafile Size(GB): 1.44
Process Time: +00 00:00:21.501692
PL/SQL procedure successfully completed.

Note that you may not be able to reduce exact size that you got from analyze.
You can run below query to get the size of the tablespace.

SQL> SELECT tablespace_name, sum(bytes)/1024/1024/1024 as ”Tablespace Size (GB)”
FROM dba_data_files
GROUP BY tablespace_name;

You can run below query to check the segment size and you can add where condition for specific segment name or type

SQL> SELECT segment_name, segment_type, bytes/1024 size_kb
FROM user_segments;

DBMS_SPACE package has below subprograms and used for various operations: -

ASA_RECOMMENDATIONS Function - This function returns recommendations
SPACE_USAGE Procedure - This procedure shows the usage of data blocks under high water mark and auto segment space management
UNUSED_SPACE Procedure - This procedure shows unused space in an object
SHRINK_TABLESPACE Procedure - This procedure used to resize or analyze big file tablespace
OBJECT_DEPENDENT_SEGMENTS Function - This function returns the list of segments associated with an object
OBJECT_GROWTH_TREND Function - This function provides space usage of object at a specific point-in-time
FREE_BLOCKS Procedure - This procedure provides information about free blocks
ISDATAFILEDROPPABLE_NAME Procedure - This procedure checks datafile is droppable
CREATE_INDEX_COST Procedure - This procedure determines cost of creating an index
CREATE_TABLE_COST Procedure - This procedure determines the size of the table

The Shrink Tablespace simplifies the procedure to reclaim the free space from datafiles by organizing objects.

Thanks & Regards,

https://oracleracexpert.com