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

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.
[code].....

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>

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

View 4 Replies


ADVERTISEMENT

Server Administration :: Change Database Character Set?

Jan 25, 2013

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

View 11 Replies View Related

Server Administration :: Change Database Character Set From AL32UTF8 To WE8ISO8859P1?

Mar 1, 2011

I want to change database character set from AL32UTF8 to WE8ISO8859P1.

what are the steps I need to follow without creating any new database with the character set as WE8ISO8859P1.

View 1 Replies View Related

Server Administration :: How To Change The Character Set

Feb 24, 2012

what's your method,when you want to change the character set ?

View 3 Replies View Related

Server Administration :: Change Character Set For Oracle 8i Databases?

Aug 15, 2013

How to change Character set for oracle 8i database. Is there anyway to change the Character set without affecting the current database.

View 11 Replies View Related

Server Administration :: Free Space In Standby Database?

Jul 23, 2011

How do we find the free space in tablespaces in a standby database

View 4 Replies View Related

Server Administration :: How To Know Character Set As Single Character Or Multi Character

Sep 26, 2013

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?

View 11 Replies View Related

Server Administration :: IPPS Has Use Up This Amount Of Space In Database From All Objects?

Oct 3, 2011

select sum(bytes/1024/1024) from dba_segments where owner='IPPS';

Does the above finding means that IPPS has use up 'this amount' of space in the database from all his objects?

View 7 Replies View Related

Server Administration :: Disk Space When Add Data File On Primary Database?

Jun 24, 2013

I'm new into Oracle, consider the below scenario and respond it accordingly.

I want to add 500GB data file at primary database side, but doesn't have space at standby database side. My STANDBY_FILE_MANAGEMENT is set to Auto.

View 3 Replies View Related

Server Administration :: ORA-12701 Create Database Character Set Is Unknown

Jan 31, 2012

While creating my database manually i got error

sql> create database orcl
>national character set utf8
national character set utf8
*
ORA:12701 CREATE DATABASE CHARACTER SET IS UN KNOWN...

where

sql> create database orcl
> character set WE8ISO8859P1
is successful...

View 7 Replies View Related

Server Administration :: Cyrillic Character Set - Import A Table Of Data To Another Database

Aug 31, 2011

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!

View 5 Replies View Related

Server Administration :: Database Version Change?

Feb 6, 2010

i have a oracle server installed enterprise edition, for some reation i need to degrade the version from enterprise to standard

i need same tablespace,data's everything same as of now.

View 8 Replies View Related

SQL & PL/SQL :: Rows To Columns With Dynamic Allocation Column Names

Nov 16, 2012

I want the output from two tables with rows to columns and generate dynamic basing on the columns.

For example: Table A (a1) , Table B(a1,b1)

Data: A B
------ ----
a1 a1 b1
--- ---- ----
1 1 x
2 1 y
3 2 a
2 b
2 c

o/p: Columnname col_1 col_2 col_3
-----------------------------------------------
a1 b1_1 b1_2 b1_3
----- ---------------------
1 x y
2 a b c

Columns should be generated based on the second table second column.

View 18 Replies View Related

How To Review Implication Of Database Character Set Change On PL/SQL Code

Sep 14, 2013

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.

View 1 Replies View Related

Server Administration :: RBS Tablespace Is Not Releasing Space

Aug 27, 2012

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

View 15 Replies View Related

Server Administration :: Calculate Used Space In A Block

Nov 17, 2011

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.

View 6 Replies View Related

Server Administration :: How To Know Which Block Contains Free Space

Feb 26, 2013

for some reason,i want to know which data block contains free space,or which table/index contains free space.

View 6 Replies View Related

Server Administration :: Segment Space Management?

Oct 13, 2010

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.

View 4 Replies View Related

Server Administration :: Managing Space In Drive E

Oct 27, 2010

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.

View 2 Replies View Related

Server Administration :: How To Reclaim Wasted Space Thoroughly

Mar 26, 2013

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)

View 12 Replies View Related

Server Administration :: Verify Tablespace Space

Jan 9, 2012

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 ?

View 5 Replies View Related

Server Administration :: Pre-allocate Space For Temporary Tablespace?

Jan 4, 2012

I just want to know whether we have to pre-allocate space for temporary tablespaces?

View 1 Replies View Related

Server Administration :: How To Delete Flashback Log And Release Space

Feb 15, 2012

How to delete flashback log and to release the space?

I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;

View 6 Replies View Related

Server Administration :: System And Users Table Space

Oct 15, 2011

a newbie dba here..

select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME
/

result output is attached .txt file.

the SYSTEM & USERS table space shows only <10 mb free space. Is it a bad sign? What I should do ?

View 7 Replies View Related

Server Administration :: Recover Space After Index Rebuild?

Apr 26, 2011

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.

View 8 Replies View Related

Server Administration :: Segment Space Management Is Auto?

Oct 18, 2010

the syntax to make a tablespace's segment space mnagement auto which is segement management is *manual* and exent management is *local autoallocate*.

View 2 Replies View Related

Server Administration :: Added Lots Of Space To A Tablespace

Sep 6, 2011

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?

View 9 Replies View Related

Server Administration :: Find Out Used Space Of A Specific Datafile

Mar 21, 2012

post some script which will find out the used space of a specific datafile.

View 5 Replies View Related

Server Administration :: Character Set Conversion

Sep 13, 2010

we have to migrate data from 11.1.0.6.0 to 11.2.0.1.0

Version : 11.1.0.6.0 character set : WE8MSWIN1252

Version : 11.2.0.1.0 character set : AL32UTF8

Any hints with which we can go.

View 3 Replies View Related

Server Administration :: Space Management - Unable To Resize Datafile?

Feb 13, 2013

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 .

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved