| nls_length_semantics = 'CHAR', acts like 'BYTE' [message #569320] |
Wed, 24 October 2012 13:52  |
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  |
ThomasG
Messages: 3212
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
