Changing the Oracle Character Set after installation

Sometimes when creating an Oracle Database, we forget to choose the correct character set on the relevant dbca screen. Then, when we then go to import data or create data we discover that the character set is not correct. With Oracle 11g, you can actually change it after the database creation. Here is sql plus commands to make this happen:

conn / as sysdba
-------
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
-------
ALTER DATABASE CHARACTER SET EL8ISO8859P7;
--- EL8ISO8859P7 is greek

-- if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE EL8ISO8859P7;
SHUTDOWN IMMEDIATE;
STARTUP;
 
---if all this is not working run the following command to reload the stylesheet
dbms_metadata_util.load_stylesheets

Transferring Data Of a Big 9i Oracle Database to 11gR2

Currently I was involved in upgrading a 40 GB Oracle database installation from 9i to 11g release 2. In place upgrade was out of the question since (1) We moved from Solaris to Linux and (2) Downtime should be kept to a minimum

The 9i setup consisted of an Oracle 9i enterprise server, with multiple database instances. One instance had 40 GB of data. This is the procedure we used, which I believe is the quickest way possible to move this amount of data:

On the 9i database server:

1. Identify and Clear all tables with temporary data.  This includes temporary report tables, backup tables, log tables etc.  In our case, this saved us a gigabyte. 
2. Export all database intances using the exp utility on the 9i server. Be careful to not allow connections to the database while exporting since sequences can be out of sync.  Do not export the sys user schema.
3. Create scripts to create tablespaces, schema users and oracle directories 

Now, on the 11g database server
Note: Steps 1, 2 and 3 below can be scripted using bash and sqlplus.

1. Create all tablespaces on the target 11g database.  Tablespace reorganization complicates the migration so I would advice against it.  Plus, the imp utility expects to find the same tablespaces when importing data of tables with CLOB or BLOB fields    
2. Create all schema users on the target 11g database.
3. Create all directories on the target 11g database, both on the database and also on the operating system level and grant the appropriate rights to the appropriate users.
4. Run import with ROWS=N CONSTRAINTS=Y INDEXFILE=INDEXES.SQL, to create sql statement script for index creation.  Here's an example below.
   

   $ export ORACLE_SID=ORADB
   $ imp \'sys/oracle as sysdba\' file=/home/oracle/expdat.dmp log=createIndexes.log FROMUSER=oracleuser TOUSER=oracleuser ROWS=n INDEXES=Y CONSTRAINTS=Y INDEXFILE=INDEXES.SQL

5. Run imp with options ROWS=n INDEXES=N CONSTRAINTS=N STATISTICS=NONE This will create empty tables in the database w/o constraints and indexes, and also import procedures, triggers, and sequences.
6. Disable Constraints and Triggers in the database.  (click to get the scripts)
7. Imports table data by running the imp utility with ANALYZE=n BUFFER=100000000 recordlength=65535 FEEDBACK=10000 IGNORE=Y ROWS=Y INDEXES=N CONSTRAINTS=N STATISTICS=NONE
8. In sqlplus run INDEXES.SQL (created in step 4 above) to create database indexes
9. Analyze tables in database.  In sqlplus run:

   exec dbms_stats.gather_schema_stats( 
        ownname          => 'oracleuser', 
        estimate_percent => dbms_stats.auto_sample_size, 
        method_opt       => 'for all columns size repeat', 
        degree           => 34 );
   

SQL Plus script to disable/enable all triggers an Oracle database

Below is an SQL Plus script to disable/enable all triggers for the current user in an Oracle database.  This is useful when importing data in an oracle database and you do not want triggers firing.  Substitute "DISABLE" below with "ENABLE" to enable back triggers after data import.

begin
  for c in (select * from user_triggers a where a.trigger_name not like '%$xd' )
  loop
      execute immediate('alter trigger '||c.trigger_name||' DISABLE');
  end loop;
end;

Scripts to disable/enable all constraints in an Oracle database

Use the scripts below to enable/disable constraints for an oracle user.  You can create them as procedures in your oracle schema, or simply execute them in sqlplus without the create procedure parts.

---

create or replace procedure sp_ddl_cons_disable as
begin
for c in (select 'ALTER TABLE '|| a.table_name ||' DISABLE constraint ' ||  a.constraint_name as command
  from user_constraints a
 where a.constraint_type in ('R'))
 loop
       ---dbms_output.put_line(c.command);
       EXECUTE IMMEDIATE (c.command);
 end loop;

end;

---

create or replace procedure sp_ddl_cons_enable as
begin
for c in (select 'ALTER TABLE '|| a.table_name ||' ENABLE constraint ' ||  a.constraint_name as command
  from user_constraints a
 where a.constraint_type in ('R'))
 loop
       ---dbms_output.put_line(c.command);
       EXECUTE IMMEDIATE (c.command);
 end loop;

end;

Generating Insert statements for MS Sql Server table data

The file below contains an adjusted script for generating insert statements for sql server table data. It was originally written by Narayana Vyas Kondreddi. You can find example usage inside the script.

sp_generate_inserts

Note that this procedure's name has the devilish sp_ prefix, which means that it better be created in the master database.  This way, it will be available to all databases in an sql server instance.  Otherwise you'd have to create it on each database on your server.

Script to generate all Primary and Foreign Key Constraints In an Oracle database

STEPS TO EXECUTE:

1. SAVE THE ATTACHED FILE TO C:\
2. LOGIN TO SQLPLUS
3. TYPE: @C:\getConstraints.sql
4. WHEN THIS FINISHES, ALL DDL WILL BE IN FILE c:\constraintsDDL.sql

---------------- getConstraints.sql -------------------------------
prompt Exporting User Constraints....
set feedback off
set heading off
set termout off
set linesize 3000
set long 90000
set trimspool on
column CODE format a300
set verify off
prompt set define off
spool c:\constraintsDDL.sql
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
select trim(DBMS_METADATA.GET_DDL('CONSTRAINT',t.constraint_name))||';' as CODE
from user_constraints t where t.constraint_type='P';
select trim(DBMS_METADATA.GET_DDL('REF_CONSTRAINT',t.constraint_name))||';' as CODE
from user_constraints t where t.constraint_type='R';
spool off
prompt set define on
set feedback on
set heading on
set termout on
set linesize 100
-----------------------------------------------------

PL/SQL proc to add a column to a table if it does not exist

create or replace function ddl_column_exists(p_table     in varchar2,
                                             p_fieldname in varchar2) return number is
  
  result number;

begin
  select count(*)
    into result
    from user_tab_columns c
   where lower(c.COLUMN_NAME) = lower(p_fieldname)
     and lower(c.TABLE_NAME) = lower(p_table);

  if result >0 then
    result := 1;
  else
    result := 0;
  end if;

  return(result);
end;
/
CREATE OR REPLACE PROCEDURE "DDL_USP_ADDCOL" (p_tblName varchar2,
                                              p_fldName varchar2,
                                              p_dtype   varchar2) iS
    v_ret number(2) := 0;
    v_sql varchar2(2000);
begin

    select count(*)
      into v_ret
      from user_tab_columns c
     where lower(c.COLUMN_NAME) = lower(p_fldName)
       and lower(c.TABLE_NAME) = lower(p_tblName);

    if v_ret = 0 then

        --print 'adding field'
        v_sql := 'ALTER TABLE ' || p_tblName || ' add ' || p_fldName || ' ' || p_dtype;
        execute immediate v_sql;

    end if;
end;

Script to export all pl/code in an oracle database

---------------- getcode.sql -------------------------------
prompt Exporting User code....
set feedback off
set heading off
set termout off
set linesize 3000
set trimspool on
set verify off
prompt set define off
spool c:\policecode.sql
select text from (
select a.name, a.type, a.line, decode(line,1,'create or replace ', '' ) || text  as text
  from user_source a
union select a.name, a.type, 9000000000 as line, '/' from user_source a
order by name,type, line);
spool off
prompt set define on
set feedback on
set heading on
set termout on
set linesize 100
-----------------------------------------------------