CHARACTER SET MIGRATION in ORACLE

ISSUE:

Database is using an invalid (non-UTF8) character set: (NLS_CHARACTERSET = WE8MSWIN1252) was given when tried to install space walk application.

CAUSE:

database was created with WE8MSWIN1252 because host os is windows. 

SOLUTION:

user csscan, csalter, datapump to migrate character set to AL32UTF8

STEPS:

1. csscan full=Y tochar=AL32UTF8

2. export user with application data need be converted. drop them from database

3. run csalter to change dictinary table character set

4. import back the dropped user.

POINTS:

it is good to user AL32UTF8 as character set at beginning. 

datapump and origical exp/imp tools can do character conversion on fly.

TABLE/VIEW:

nls_session_parameters

nls_instance_parameters

nls_database_parameters

v$nls_valid_values

v$nls_parameters

CHECK database character set:

select * from nls_database_parameters where parameter like '%CHAR%';

NLS paramters:

PARAMETER

------------------------------

NLS_LANGUAGE

NLS_TERRITORY

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_NUMERIC_CHARACTERS

NLS_CHARACTERSET

NLS_CALENDAR

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_SORT

NLS_TIME_FORMAT

NLS_TIMESTAMP_FORMAT

NLS_TIME_TZ_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

NLS_DUAL_CURRENCY

NLS_COMP

NLS_LENGTH_SEMANTICS

NLS_NCHAR_CONV_EXCP

NLS_NCHAR_CHARACTERSET

NLS_RDBMS_VERSION

reference:

http://www.morganslibrary.org/reference/character_sets.html

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch12scanner.htm#g1035448

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#g1011430

https://forums.oracle.com/forums/thread.jspa?threadID=990007

http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch1overview.htm#i1005900

https://forums.oracle.com/forums/thread.jspa?messageID=3471873

https://forums.oracle.com/forums/thread.jspa?threadID=2206301

Compress tablespace , table partition and ORA-04031

ISSUE:
ORA-04031 appears in alert log and shared memory is used up. most of sql can not  be executed due to ORA-04031.

CAUSE:
1. we use basic compress technology on partition tables.
2. new column need be added to the partition tables with basic compression. so below need be done.
   alter table p1 move;
   alter table p1 add column c1;
   alter table p1 move compress;
3. there are 200000 subpartitions on p1 because of old data.

SYMPTOM:
ORA-0431 error.
PRTMV portion in shared pool is extremely high. in may case PRTMV used 18G of 20G shared pool

SOLUTION:
reduce subpartition to reasonable number.

DEFERRED_SEGMENT_CREATION and ORA-04031

11g new feature:

segment will be created when first insert statment is issued for table. if DEFERRED_SEGMENT_CREATION is true.

ISSUE:
When partition table is used, it might cause ORA-04031 error.  The PRTMV part of shared pool will take most of shared pool memory.

Solution:
set DEFERRED_SEGMENT_CREATION  to false.


reference:
11.2 Database New Feature Deferred Segment Creation [Video] [ID 887962.1]
http://www.dba-oracle.com/t_shared_pool_scripts.htm
http://blog.tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/

Compress in Oracle 11g

basic compression (compress)-- free
advanced compression (compres for all operatons |compress for oltp) -- need license

compress can be configured on tablespace , table, partition and subpartition level

SQL COMMAND:
create tablespace YT datafile  size 10m autoextend on maxsize 31G
extent management local uniform size 128k
segment space management auto
default compress
;

alter tablespace yt default compress;

create tablespace YT datafile  size 10m autoextend on maxsize 31G
extent management local uniform size 128k
segment space management auto
default compress for all operations
;

create tablespace YT datafile  size 10m autoextend on maxsize 31G
extent management local uniform size 128k
segment space management auto
default compress for oltp
;

alter tablespace yt default compress for all operations;
alter tablespace yt default compress for oltp;


alter tablespace yt default nocompress;



create table ttt (
...
)
compress|compress for all operations | compress for oltp
;

alter table ttt nocompress;
alter table ttt compress;
alter table ttt compress for all operations;
alter table ttt compress for OLTP;
alter table ttt move compress;
alter table ttt move nocompress;
alter table ttt move compress for all operations;

COMPRESSION CHECK:

select tablespace_name, compress_for from dba_tablespaces where tablespace_name='YT';
select table_name, owner, compression, compress_for from dba_tables where table_name='TTt' and owner='TEST';

reference:

http://oracle-online-help.blogspot.com/2006/11/oracle-table-compression.html

http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#i31718

http://www.oracle-base.com/articles/11g/table-compression-enhancements-11gr1.php

Slow Drop Schema and Datapump Export Schema in Oracle 11g

ISSUE:
it is very slow to drop a schema or export  the schema in oracle 11g database.

CAUSE:
there are too many objects in the schema. In my case the schema has 1.4 million subpartitions.

SOLUTION:
to Drop the schema:  drop at table level or subpartion level first and then drop the schema.
to EXport the schema: reduce object number by combine partitions or change table definition. Otherwise metadata export will cost too much time to be completed.


reference:
http://www.oaktable.net/content/dropped-tables-hiding-extents-and-slow-dbafreespace-queries

Session Trace in ORACLE with Logon Trigger

ISSUE:
it is required to trace user session, which is initiated from application. it is hard to issue 'alter session set events ... " statement.

SOLUTION:
create a logon trigger to set up session trace.

e.x:


prompt 'this need be run as sys to create logon trigger to trace user session.'
accept UNAME prompt 'please input shcema name like UAT_TEST :'

CREATE OR REPLACE TRIGGER LOGON_TRACE
AFTER LOGON ON DATABASE
WHEN ( USER = upper('&UNAME') )
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 16''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


reference:
http://newappsdba.blogspot.com/2007/09/tip-tracing-session-via-login-trigger.html

Remove SQL Query from SQLPLUS Spool file ORACLE

ISSUE:
SQL query was spooled out by sqlplus spool command when only results need be in spool file

SOLUTION:
Condition 1: spool command was executed mannually through interactive sqlplus prompt
run below set in sqlplus

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
set trimspool on

Condition2: spool command was executed through shell script automatically.

start sqlplus with S (capital s) option. 

and include below set in the script.

set pagesize 0

set linesize 180

set feedback off

set trimspool on

Spool file should include only results. 

reference:

https://forums.oracle.com/forums/thread.jspa?messageID=3961450

http://www.the-playground.de/joomla/index.php?option=com_content&view=article&id=252:hide-sql-query-in-sqlplus-spool-file&catid=18:tipsntricks&Itemid=37