Home » RDBMS Server » Server Administration » nls_length_semantics = 'CHAR', acts like 'BYTE' (Oracle Database 11g Release 11.1.0.7.0 - 64bit Production Windows)
nls_length_semantics = 'CHAR', acts like 'BYTE' [message #569320] Wed, 24 October 2012 13:52 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi All,

I want to change space allocation for character columns in my database,
So it will store them as 'CHAR' and not 'BYTE'.

my character set is

SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
AL32UTF8

SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;

System altered.




I bounced the instance just to make sure

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Connected to an idle instance.

SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size                  2215944 bytes
Variable Size            1862275064 bytes
Database Buffers          637534208 bytes
Redo Buffers                3313664 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
  2  FROM sys.v_$parameter WHERE name = 'spfile';

Init F
------
SPFILE

SQL>


SQL> show parameter semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR



And then I want to see that when I create a table with some varchar2 column,
The space for it will be allocated by chars, and not by bytes!

However, when I run a check of create table, this is what I get:

SQL> drop table check_char;

Table dropped.

SQL> create table check_char (some_name varchar2(10));

Table created.

SQL> select a.char_used
  2  from all_tab_columns a
  3  where table_name='CHECK_CHAR'
  4  and a.owner='SYS';

C
-
B

SQL>



Does anybody know the reason for space allocation to remain in BYTES and not CHAR,
or what else I can check?


Thanks in advance!

Regards,
Andrey
Re: nls_length_semantics = 'CHAR', acts like 'BYTE' [message #569321 is a reply to message #569320] Wed, 24 October 2012 14:08 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Did you create the Table as SYS?

There is no way to know how those things work in the SYS schema, since the creation of non Oracle supplied objects in the SYS schema is not supported.

From here:

NLS_LENGTH_SEMANTICS HAS NO EFFECT ON TABLES OWNED BY SYS. This is normal, for sys objects NLS_LENGTH_SEMANTICS is ignored and the these are always treated with byte semantics.

NEVER create your own objects in the SYS schema. Create your own user and schema to create your objects.
Re: nls_length_semantics = 'CHAR', acts like 'BYTE' [message #569322 is a reply to message #569321] Wed, 24 October 2012 14:13 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
WOW. Great lesson you had taught me here!!!

Happy to spend my 100th message here on ORAFAQ to Thank you very much for this:)!

As you said, it actually works like magic:

SQL> conn andrey/**********@connstring
Connected.
SQL>
SQL>
SQL>
SQL> create table check_char (some_name varchar2(10));

Table created.

SQL> select a.char_used
  2  from all_tab_columns a
  3  where table_name='CHECK_CHAR'
  4  and a.owner='ANDREY';

C
-
C


Thanks again!

Regards,
Andrey
Re: nls_length_semantics = 'CHAR', acts like 'BYTE' [message #569323 is a reply to message #569322] Wed, 24 October 2012 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
WOW. Great lesson you had taught me here!!!


as I always say (and this was my first thought when I see you post but Thomas beat me):

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Regards
Michel
Re: nls_length_semantics = 'CHAR', acts like 'BYTE' [message #569325 is a reply to message #569323] Wed, 24 October 2012 14:32 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Will note that. Thank you:)!
Previous Topic: Oracle patch information
Next Topic: Table Statistics for oracle 11g
Goto Forum:
  


Current Time: Thu Mar 28 03:33:54 CDT 2024