Server Administration :: Change Space Allocation For Character Columns In Database
Oct 24, 2012
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
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:
Currently my oracle database character set is we8mswin1252 and it is only containing English data as well as spatial data (which is in English of course). I would like to change the Database character set so it could accept Arabic characters.
I have checked the below command on a test DB and it worked fine, but I want to know if it's recommended as a best practice when changing the character set to accept arabic and this won't corrupt my old entered Data ?
SHUTDOWN IMMEDIATE STARTUP RESTRICT ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256 SHUTDOWN IMMEDIATE STARTUP
how do we know database character set is either single character set or multi character set?
While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".
Below are steps taken to resolve the issue -
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
i got this error: ORA-12712: new character set must be a superset of old character set
below are the commands executed by me:
SQL> SHUTDOWN IMMEDIATE; SQL> CONNECT SYS/password AS SYSDBA; SQL> STARTUP MOUNT; SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252; SQL> SHUTDOWN; SQL> STARTUP; SQL> QUIT;
And its working...
I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?
I'm try to import a table of data (character set: CL8ISO8859P5) to another database (character set: AL32UTF8) using exp/imp utility.After the import, all Cyrillic text was corrupted!
We are converting WE8ISO8859P1 oracle db character set to AL32UTF8. Before conversion, i want to check implication on PL/SQL code for byte based SQL functions.
What all points to consider while checking implications on PL / SQL code? I could find 3 methods on Google surfing, SUBSTRB, LENGTHB, INSTRB. What do I check if these methods are used in PL/SQL code?
What all other methods should I check? What do I check in PL/SQL if varchar and char type declarations exist in code? How do i check implication of database character set change to AL32UTF8 for byte bases SQL function.
In My database rollback segment space is not releasing space even though, there is no transaction is using RBS. RBS tablespace size is around 70GB. Unfortunately still our environment is running in 9i due to application code
SQL> show parameter undo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 1800 undo_suppress_errors boolean FALSE undo_tablespace
Is there any way I can calculate percentage of space used in a block.Eg if a table size is 100 blocks,How Can I check the percentage of used space in block.
am using Oracle 10.2.0.4 on win 2008 server SP2. I would like to know if we can set the Segment Space Management feature to AUTO for RBS and Temporary tablespaces. As the data is not permanent in these tablespaces, will it manage automatically?
Presently its Segment Space Mgmt is manual for System, RBS, Temporary tablespaces.
In my drive E: I'll have a space issue soon because one file is taking all space. SYSTEM01. DBF size is around 25GB. I want to know if I delete some data in my database I'll gain space.
In one of our Data warehousing DB, even though, all the tablespaces' space should keep on at least 1 month, but our leader want us to estimate how much space can free up with db method.
I have referenced
<Administrator Guide> - Reclaiming Wasted Space
[URL].....
I have several questions on reclaim space:
1. It seems that segment adviser give a really cool view to know which segment should be shrink and how much size will free up after shrink. But actually, this need a very a job or manually do this. I have once heard about some query from can estimate this :
A script from MOS, but actually I found this it's not very accurate with segment adviser. This script should report the real space the table occupy, but after shrink space, the space doesn't free up.
SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;
Also there is a script evaluate the tablespace fragments from some people:
SELECT tablespace_name, SQRT (MAX (blocks) / SUM (blocks)) * (100 / SQRT (SQRT (COUNT (blocks)))) FSFI FROM dba_free_space GROUP BY tablespace_name ORDER BY 1;
if the value is very low, we can coalesce the tablespace. But after I coalesce the tablespace, I can't see any space free up.
From <Concepts>: Quote:Coalescing extents is not necessary in locally managed tablespaces, because all contiguous free space is available for allocation to a new extent regardless of whether it was reclaimed from one or more extents.
2. is there any good way to estimate how much space we should free up? ( After free up the space , dba_free_space should see this result)
Application team requested hosting team to add some space to tablespace as it was exceeding 80% used.Now the hosting team have added the space as per recommendation and the application team wants us to verify if the space was added. How to check the space was added in GB to list of tablesapces ?
We have separate tablespaces for Tables and Indexes. Also Temp is in different temporary tablespace and UNDO also in UNDO tablespace and Index tablespace contains only Indexes. The tablespace usage for tables is 80% and Index is 91%.There is not enough disk space to allocate for the datafile on our system. I ran a rebuild on one of the Index but now notice the Index tablespace is 98% used soon after the rebuild finished.
How can i free up space for Index tablespace and why does the size of Index tablespace increased after the rebuild.
Mistakenly I added lot of datafiles with autextend on option.. I realized later and then tried to resize the datafiles to a minumum space but got the below error
ORA-03214: File Size specified is smaller than minimum required.
How to resolve this problem to reclain the space back?
I have one generic question about space management. I have one table with size of 1TB. This table stored in ORC1 tablespace. This tablespace contains 70 datafiles.
Since it's 10.2.0.4 database. I have dropped this table by using purge
drop table <<table_name>> purge;
Once table drop was completed. When I check the tablespace space it was 100% free but due to HWM was unable to resize the datafile from current size to small size. What was the reason behind this. Is there any process needs to follow when dropping big tables ? like instead of dropping the tables do I need to truncate first & then drop .