How To Set Database To Noarchivelog

Login to sqlplus

# sqlplus /nolog
SQL> connect / as sysdba

The db must be mounted EXCLUSIVE and not open for operation

SQL> shutdown immediate;
SQL> startup mount;

Check the log mode of the database

SQL> select log_mode from v$database;

Set it to noarchivelog mode

SQL> alter database noarchivelog;

Set db open for user operation

SQL> alter database open;

Activate and De-Activate Granted Roles

Check the roles that are enabled in your session:

select * from session_roles;

Using the command SET ROLE you can set only specific roles to active. Set all active:

set role all;

Disable specific roles:

set role all except role1, role2;

Datapump: Re-Importing TYPEs

When duplicating a schema which contains TYPE objects using expdp/impdp, you will encounter the following error:

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal

This is because Oracle tries to create the type object in the target schema with the same object identifier (OID) as it has in the source schema. But an OID must be unique within a database.

Add the following parameter to your impdp command to make sure new OIDs are generated upon import:

transform=oid:n

Since: Oracle 10gR2
Source: Oracle Support Doc ID 351519.1

Stop Datapump Job Using PL/SQL API

Check the status of all current datapump jobs:

select *
from   dba_datapump_jobs
;

Using the PL/SQL API, attach to the job and stop it:

set serveroutput on
set lines 100
--/
declare
   handle number;
begin
  -- format: dbms_datapump.attach('','');
   handle := dbms_datapump.attach('SYS_IMPORT_SCHEMA_01','SYS');
   dbms_datapump.stop_job (handle,1,0);
end;
/

In a previous post, I showed how to achieve this with the command line utility: killing a datapump job.

Column Dependencies

When looking for information about column dependencies, I stumbled over this article: DBA_DEPENDENCY_COLUMNS. The script below is copied from this source and made available here for easier copying :-) If you run it as sysdba, you'll have a new view DBA_DEPENDENCY_COLUMS. Requires Oracle 11g.

create view dba_dependency_columns as
select d.u_name                                        owner
   , d.o_name                                          name
   , decode  
     ( d.o_type#  
     , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'  
     , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'  
     , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT' 
      , 11, 'PACKAGE BODY', 12, 'TRIGGER' 
      , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY' 
      , 28, 'JAVA SOURCE', 29, 'JAVA CLASS' 
      , 32, 'INDEXTYPE', 33, 'OPERATOR' 
      , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION' 
      , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA' 
      , 59, 'RULE', 62, 'EVALUATION CONTXT' 
      , 92, 'CUBE DIMENSION', 93, 'CUBE' 
      , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS' 
      , 'UNDEFINED' 
      )                                                 type 
    , nvl2( d.po_linkname, d.po_remoteowner, d.pu_name) referenced_owner 
    , d.po_name                                         referenced_name 
    , decode 
      ( d.po_type# 
      , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER' 
      , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE' 
      , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT' 
      , 11, 'PACKAGE BODY', 12, 'TRIGGER' 
      , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY' 
      , 28, 'JAVA SOURCE', 29, 'JAVA CLASS' 
      , 32, 'INDEXTYPE', 33, 'OPERATOR' 
      , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION' 
      , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA' 
      , 59, 'RULE', 62, 'EVALUATION CONTXT' 
      , 92, 'CUBE DIMENSION', 93, 'CUBE' 
      , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS' 
      , 'UNDEFINED' 
      )                                                 referenced_type 
    , d.po_linkname                                     referenced_link_name 
    , c.name                                            referenced_column 
    , decode(bitand(d.d_property, 3), 2, 'REF', 'HARD') dependency_type 
 from ( select obj# 
             , u_name 
             , o_name 
             , o_type# 
             , pu_name 
             , po_name 
             , po_type# 
             , po_remoteowner 
             , po_linkname 
             , d_property 
             , colpos 
          from sys."_CURRENT_EDITION_OBJ" o 
             , sys.disk_and_fixed_objects po 
             , sys.dependency$ d 
             , sys.user$ u 
             , sys.user$ pu 
         where o.obj# = d.d_obj# 
           and o.owner# = u.user# 
           and po.obj# = d.p_obj# 
           and po.owner# = pu.user# 
           and d.d_attrs is not null 
         model 
               return updated rows 
               partition by 
               ( po.obj#        obj# 
               , u.name         u_name 
               , o.name         o_name 
               , o.type#        o_type# 
               , po.linkname    po_linkname 
               , pu.name        pu_name 
               , po.remoteowner po_remoteowner 
               , po.name        po_name 
               , po.type#       po_type# 
               , d.property     d_property 
               ) 
               dimension by (0 i) 
               measures (0 colpos, substr(d.d_attrs,9) attrs) 
               rules iterate (1000) 
                     until (iteration_number = 4 * length(attrs[0]) - 2) 
               ( colpos[iteration_number+1] 
                 = case bitand 
                        ( to_number 
                          ( substr 
                            ( attrs[0] 
                            , 1 + 2*trunc((iteration_number+1)/8) 
                            , 2 
                            ) 
                          ,'XX' 
                          ) 
                        , power(2,mod(iteration_number+1,8)) 
                        ) 
                   when 0 then null 
                   else iteration_number+1 
                   end 
               ) 
      ) d 
    , sys.col$ c 
where d.obj# = c.obj#
   and d.colpos = c.col#
;

grant select on dba_dependency_columns to select_catalog_role
;

create public synonym dba_dependency_columns for dba_dependency_columns
;

Check Installed Features And Options

To see a list of installed features and their status:

select *
from   dba_registry
order by comp_name
;

To see which options are available:

select * 
from   v$option
order by parameter
;

Check Default Tablespaces

Check default tablespaces of the database:

select * 
from   database_properties
where  property_name like '%TABLESPACE'
order by property_name
;

And the users:

select username
,      default_tablespace
,      temporary_tablespace
from   dba_users
order by username
;