table in read only mode OR restricitng dml on a table

HI ,

I found this somewhere and it's a nice trigger to make a table in read only mode .

16:06:06 > create table test11 (num number(20));

Table created.

16:06:13 > insert into test11 values(1);

1 row created.

16:06:26 > commit;

Commit complete.

16:06:29 > select * from test11;

NUM
----------
1

Now , create the trigger to disable the dml on the table test11

16:06:33 > create or replace trigger test11_read_only
before insert or update or delete
on test11
begin
raise_application_error (-20001, 'RAHUL has restricted DML on this table , Table TEST11 is Read Only ');
end;
/
16:06:56 2 16:06:56 3 16:06:56 4 16:06:56 5 16:06:56 6 16:06:56 7
Trigger created.

16:06:56 > insert into test11 values(1);
insert into test11 values(1)
*
ERROR at line 1:
ORA-20001: RAHUL has restricted DML on this table , Table TEST11 is Read Only
ORA-06512: at "APPS.TEST11_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'APPS.TEST11_READ_ONLY'


16:07:03 >

ORA-29342: user APPS does not exist in the database ; During transport tablesapce option

[plantopiadb:oraperf] /home/users/oraperf> imp transport_tablespace=y file=/perf/dump/perfdump.dmp log=/perf/dump/perfdump>

Import: Release 9.2.0.7.0 - Production on Fri Sep 11 15:45:06 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
"BEGIN sys.dbms_plugts.checkUser('APPS'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user APPS does not exist in the database
ORA-06512: at "SYS.DBMS_PLUGTS", line 1594
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully



Soltuion :

APPS user is having object in respective tablespace (PERFSTAT in our case) at the source database. But no APPS user at the destination database. Use the query mentioned below to fine all the users having the object in PERFSTAT tablespace at source database and need to be created at the destination database , in case if not present .

SQL> select distinct owner from dba_segments where tablespace_name='PERFSTAT' ;

OWNER
------------------------------
APPS
BIZMAX
DBSNMP
KIC
PERFSTAT
TRCANLZR

create all the users in your destiantion database . Where you're plugin the tablespace ..

ORA-29335: tablespace 'PERFSTAT' is not read only ( during transport tablespace option )

[veritas:oraprod] /home/oraprod > TAT file=/u43/oradata/stats_dump/perfdump.dmp log=/u43/oradata/stats_dump/perfdump.log <

Export: Release 9.2.0.5.0 - Production on Fri Sep 11 12:44:26 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: / as sysdba

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses UTF8 character set (possible charset conversion)

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

EXP-00008: ORACLE error 29335 encountered

ORA-29335: tablespace 'PERFSTAT' is not read only

ORA-06512: at "SYS.DBMS_PLUGTS", line 436

ORA-06512: at line 1

EXP-00000: Export terminated unsuccessfully

Soltuion :

This is because of the tablespace in READ WRITE mode . Before taking the export of metadata, tablespace

needs to be taken in READ ONLY mode

How to change the character set of a database

Step 1 : Find the present character set of your database ..

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER VALUE

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

NLS_CHARACTERSET US7ASCII

NLS_NCHAR_CHARACTERSET AL16UTF16

Step 2 : How to change

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
---- TRUNCATE TABLE SYS.METASTYLESHEET; ---to get rid of ORA-12716 per Metalink Note - 213015.1
ALTER DATABASE CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;
@$ORACLE_HOME/rdbms/admin/catmet.sql
SHUTDOWN IMMEDIATE;
STARTUP;


Step 3 : Now confirm the new character set of your database ..

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER VALUE

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

NLS_CHARACTERSET UTF8

NLS_NCHAR_CHARACTERSET AL16UTF16