Oracle : Script to list missing and INVALID Objects in the database

select A.Owner Oown, 
       A.Object_Name Oname, 
       A.Object_Type Otype, 
       'Miss Pkg Body' Prob 
  from DBA_OBJECTS A 
 where A.Object_Type = 'PACKAGE' 
   and A.Owner not in ('SYS','SYSTEM')
   and not exists 
        (select 'x' 
           from DBA_OBJECTS B 
          where B.Object_Name = A.Object_Name 
            and B.Owner = A.Owner 
            and B.Object_Type = 'PACKAGE BODY') 
union 
select Owner Oown, 
       Object_Name Oname, 
       Object_Type Otype, 
       'Invalid Obj' Prob 
  from DBA_OBJECTS 
 where Owner not in ('SYS','SYSTEM') 
   and Status != 'VALID' 
 order by 1,4,3,2
/

Oracle GoldenGate Configuring DDL Synchronization

In addition to providing replication support for all DML statements, we can also configure the GoldenGate environment to provide DDL support as well.


A number of prerequisite setup tasks need to be performed which we willl highlight here.

Run the following scripts from the directory where the GoldenGate software was installed.

The assumption here is that the database user GGS_OWNER has already been created and granted the required roles and privileges as discussed in our earlier tutorial.


Note - run the scripts as SYSDBA


SQL> @marker_setup


Marker setup script


You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name:GGS_OWNER



Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:


Setting schema name to GGS_OWNER


MARKER TABLE

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

OK


MARKER SEQUENCE

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

OK


Script complete.




SQL> alter session set recyclebin=OFF;

Session altered.



SQL> @ddl_setup

GoldenGate DDL Replication setup script


Verifying that current user has privileges to install DDL Replication...


You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name: GGS_OWNER


You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP


Working, please wait ...

Spooling to file ddl_setup_spool.txt



Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.


Working, please wait ...


RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.


Enter yes or no:yes



DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER


DDLORA_GETTABLESPACESIZE STATUS:


Line/pos Error

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

No errors No errors


CLEAR_TRACE STATUS:


Line/pos Error

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

No errors No errors


CREATE_TRACE STATUS:


Line/pos Error

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

No errors No errors


TRACE_PUT_LINE STATUS:


Line/pos Error

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

No errors No errors


INITIAL_SETUP STATUS:


Line/pos Error

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

No errors No errors


DDLVERSIONSPECIFIC PACKAGE STATUS:


Line/pos Error

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

No errors No errors


DDLREPLICATION PACKAGE STATUS:


Line/pos Error

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

No errors No errors


DDLREPLICATION PACKAGE BODY STATUS:


Line/pos Error

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

No errors No errors


DDL HISTORY TABLE

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

OK


DDL HISTORY TABLE(1)

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

OK


DDL DUMP TABLES

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

OK


DDL DUMP COLUMNS

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

OK


DDL DUMP LOG GROUPS

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

OK


DDL DUMP PARTITIONS

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

OK


DDL DUMP PRIMARY KEYS

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

OK


DDL SEQUENCE

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

OK


GGS_TEMP_COLS

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

OK


GGS_TEMP_UK

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

OK


DDL TRIGGER CODE STATUS:


Line/pos Error

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

No errors No errors


DDL TRIGGER INSTALL STATUS

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

OK


DDL TRIGGER RUNNING STATUS

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

ENABLED


STAYMETADATA IN TRIGGER

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

OFF


DDL TRIGGER SQL TRACING

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

0


DDL TRIGGER TRACE LEVEL

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

0


LOCATION OF DDL TRACE FILE

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

/opt/app/oracle/diag/rdbms/db1/db1/trace/ggs_ddl_trace.log


Analyzing installation status...



STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components


Script complete.

SQL>





SQL> @role_setup


GGS Role setup script


This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change

the gg_role parameter to the preferred name. (Do not run the script.)


You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name:GGS_OWNER

Wrote file role_setup_set.txt


PL/SQL procedure successfully completed.



Role setup script complete


Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:


GRANT GGS_GGSUSER_ROLE TO


where is the user assigned to the GoldenGate processes.



SQL> grant ggs_ggsuser_role to ggs_owner;


Grant succeeded.



SQL> @ddl_enable


Trigger altered.




SQL> @ddl_pin GGS_OWNER


PL/SQL procedure successfully completed.



PL/SQL procedure successfully completed.



PL/SQL procedure successfully completed.


Turn Recyclebin OFF


We need to set the parameter recyclebin to OFF via the

ALTER SYSTEM SET RECYCLEBIN=OFF command in order to prevent this error which we will see if we try and configure DDL support and then start the Extract process.


2010-02-19 11:13:30 GGS ERROR 2003 RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBIN in parameter file to FALSE. Then restart database and extract.


2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.

Enable additional logging at the table level


Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by GoldenGate for DDL support.


GGSCI (redhat346.localdomain) 5> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.


GGSCI (redhat346.localdomain) 6> ADD TRANDATA scott.emp

Logging of supplemental redo data enabled for table SCOTT.EMP.
Edit the parameter file for the Extract process to enable DDL synchronization

We had earlier created a parameter file for an Extract process ext1. We now edit that parameter file and add the entry


DDL INCLUDE MAPPED


This means that DDL support is now enabled for all tables which have been mapped and in this case it will only apply to the SCOTT.EMP table as that is the only table which is being processed here. We can also use the INCLUDE ALL or EXCLUDE ALL or wildcard characters to specify which tables to enable the DDL support for.


GGSCI (redhat.localdomain) 1> EDIT PARAM EXT1

EXTRACT ext1

USERID ggs_owner, PASSWORD ggs_owner

RMTHOST 10.53.100.100, MGRPORT 7809

RMTTRAIL /opt/oracle/software/goldengate/dirdat/rt

DDL INCLUDE MAPPED

TABLE scott.emp;


Test the same

We will now alter the structure of the EMP table by adding a column and we can see that this new table structure is also reflected on the target system.


On Source

SQL> ALTER TABLE EMP ADD NEW_COL VARCHAR2(10);

Table altered.

On Target

SQL> desc emp

Name Null? Type

—————————————– ——– —————————-

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(20)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

MYCOL VARCHAR2(10)

NEW_COL VARCHAR2(10)

Oracle : Script which tells free space left in tablespaces and datafiles in terms of percentage

SET PAGESIZE 100 LINES 132 ECHO OFF VERIFY OFF FEEDB OFF SPACE 1 TRIMSP ON
COMPUTE SUM OF a_byt t_byt f_byt ON REPORT
BREAK ON REPORT ON tablespace_name ON pf
COL tablespace_name FOR A17   TRU HEAD 'Tablespace|Name'
COL file_name       FOR A40   TRU HEAD 'Filename'
COL a_byt           FOR 9,990.999 HEAD 'Allocated|GB'
COL t_byt           FOR 9,990.999 HEAD 'Current|Used GB'
COL f_byt           FOR 9,990.999 HEAD 'Current|Free GB'
COL pct_free        FOR 990.0     HEAD 'File %|Free'
COL pf              FOR 990.0     HEAD 'Tbsp %|Free'
COL seq NOPRINT
DEFINE b_div=1073741824
--
SELECT 1 seq, b.tablespace_name, nvl(x.fs,0)/y.ap*100 pf, b.file_name file_name,
  b.bytes/&&b_div a_byt, NVL((b.bytes-SUM(f.bytes))/&&b_div,b.bytes/&&b_div) t_byt,
  NVL(SUM(f.bytes)/&&b_div,0) f_byt, NVL(SUM(f.bytes)/b.bytes*100,0) pct_free
FROM dba_free_space f, dba_data_files b
 ,(SELECT y.tablespace_name, SUM(y.bytes) fs
   FROM dba_free_space y GROUP BY y.tablespace_name) x
    ,(SELECT x.tablespace_name, SUM(x.bytes) ap
      FROM dba_data_files x GROUP BY x.tablespace_name) y
   WHERE f.file_id(+) = b.file_id
   AND   x.tablespace_name(+) = y.tablespace_name
   and   y.tablespace_name =  b.tablespace_name
   AND   f.tablespace_name(+) = b.tablespace_name
   GROUP BY b.tablespace_name, nvl(x.fs,0)/y.ap*100, b.file_name, b.bytes
   UNION
   SELECT 2 seq, tablespace_name,
     j.bf/k.bb*100 pf, b.name file_name, b.bytes/&&b_div a_byt,
     a.bytes_used/&&b_div t_byt, a.bytes_free/&&b_div f_byt,
     a.bytes_free/b.bytes*100 pct_free
   FROM v$temp_space_header a, v$tempfile b
     ,(SELECT SUM(bytes_free) bf FROM v$temp_space_header) j
     ,(SELECT SUM(bytes) bb FROM v$tempfile) k
   WHERE a.file_id = b.file#
   ORDER BY 1,2,4,3;

Oracle : Script that shows tablespace free space

select /*+ CURSOR_SHARING_EXACT */
  substrb(dtb.tablespace_name,1,16) TS_NAME,
  substrb(dtb.contents,1,3) TYP,
  substrb(dtb.status,1,2) ST,
  substrb(decode(dtb.logging,'LOGGING','Y','N'),1,1) "L",
  substrb(dtb.extent_management,1,1) "D",
  substrb(decode(floor(dtb.initial_extent/1073741824),0,
    decode(floor(dtb.initial_extent/1048576),0,
      to_char(dtb.initial_extent/1024,'9999')||'K',
      to_char(dtb.initial_extent/1048576,'9999')||'M'),
    to_char(dtb.initial_extent/1073741824,'99.9')||'G'),2,6) "INIT",
  substrb(decode(dtb.allocation_type,'UNIFORM',' UEM','SYSTEM',' SYS',
    decode(floor(dtb.next_extent/1073741824),0,
      decode(floor(dtb.next_extent/1048576),0,
        to_char(dtb.next_extent/1024,'9999')||'K',
        to_char(dtb.next_extent/1048576,'9999')||'M'),
      to_char(dtb.next_extent/1073741824,'99.9')||'G')),2,6) "NEXT",
  substrb(to_char(my_dba_data_files.file_count,'999'),2,4) FC,
  substrb(to_char(my_dba_data_files.auto_extend,'999'),2,4) AX,
  substrb(decode(floor(my_dba_data_files.total_size/1073741824),0,
    decode(floor(my_dba_data_files.total_size/1048576),0,
      to_char(my_dba_data_files.total_size/1024,'9999')||'K',
      to_char(my_dba_data_files.total_size/1048576,'9999')||'M'),
    to_char(my_dba_data_files.total_size/1073741824,'99.9')||'G'),2,6) "SIZE",
  substrb(decode(floor(my_free_space.total_free_space/1073741824),0,
    decode(floor(my_free_space.total_free_space/1048576),0,
      to_char(my_free_space.total_free_space/1024,'9999')||'K',
      to_char(my_free_space.total_free_space/1048576,'9999')||'M'),
    to_char(my_free_space.total_free_space/1073741824,'99.9')||'G'),2,6) "FREE",
  substrb(decode(floor(my_dba_data_files.max_size/1073741824),0,
    decode(floor(my_dba_data_files.max_size/1048576),0,
      to_char(my_dba_data_files.max_size/1024,'99999')||'K',
      to_char(my_dba_data_files.max_size/1048576,'99999')||'M'),
    to_char(my_dba_data_files.max_size/1073741824,'999.9')||'G'),2,7) "MAX",
  substrb(decode(floor(my_free_space.largest_free_segment/1073741824),0,
    decode(floor(my_free_space.largest_free_segment/1048576),0,
      to_char(my_free_space.largest_free_segment/1024,'9999')||'K',
      to_char(my_free_space.largest_free_segment/1048576,'9999')||'M'),
    to_char(my_free_space.largest_free_segment/1073741824,'99.9')||'G'),2,6) LFS,
  lpad(decode(my_dba_data_files.auto_extend,0,' ','(')||
    ltrim(to_char(least(nvl(((my_free_space.total_free_space+(my_dba_data_files.max_size-my_dba_data_files.total_size))/my_dba_data_files.max_size)*100,0),99.9),'99.9'))||
'%'||decode(my_dba_data_files.auto_extend,0,' ',')'),7) PC_FREE
from dba_tablespaces dtb, (
  select
    ddf.tablespace_name,
    count(ddf.file_id) file_count,
    sum(ddf.bytes) total_size,
    sum(greatest(ddf.bytes,ddf.maxbytes)) old_max_size,
    sum(decode(ddf.autoextensible,'YES',greatest(ddf.bytes,ddf.maxbytes),ddf.bytes)) max_size,
    sum(decode(ddf.autoextensible,'YES',1,0)) auto_extend
  from dba_data_files ddf
  group by ddf.tablespace_name ) my_dba_data_files, (
    select
    dfs.tablespace_name tablespace_name,
    max(dfs.bytes) largest_free_segment,
    sum(dfs.bytes) total_free_space
  from dba_free_space dfs
  group by tablespace_name
  union all
  select
    dtb.tablespace_name,0,0
  from dba_tablespaces dtb
  where not exists ( select 1 from dba_free_space dfs where dfs.tablespace_name = dtb.tablespace_name )) my_free_space
where dtb.tablespace_name = my_dba_data_files.tablespace_name(+)
and dtb.tablespace_name = my_free_space.tablespace_name(+)
order by
  ((my_free_space.total_free_space+(my_dba_data_files.max_size-my_dba_data_files.total_size))/my_dba_data_files.max_size) desc,
  typ,
  ts_name
/

Oracle : Script to Monitor User Activity in database

set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
  from v$sqltext a, v$session b, v$process c
   where a.address    = b.sql_address
--   and b.status     = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
--                                  ACTVE TRANSACTION ON THAT MOMENT */
   and b.paddr      = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid,a.hash_value,a.piece
/   

Oracle : ORA-01045: user username lacks CREATE SESSION privilege; logon denied : When grants are given through role

If the user has "grant create session" grant through a role and the schema is not able to create session then it must be the problem with the user not picking up the role.

Solution :

alter user <username> default role all;

More About Roles

__Alter user scott default role all;__


1. DEFAULT ROLE is the clause that is used to enable a particular role as a default role when user log on to the database.

2. To use the DEFAULT ROLE clause OR before using DEFAULT ROLE command, you must grant a ROLE to USER.


For ex> Assume a user has beed granted three roles...,

Role1, Role2,Role3... Connect privileage granted to Role1, I need only role1 to be a default role.

So, I issued a command to enable a role1 as a default role.

SQL>ALTER USER scott DEFAULT ROLE ROLE1;

What oracle does do, it enable a Role1 as a default role WHEN USER LOGS ON . Now you have connect priviliage only. All other roles are disabled.

If you specify ALTER USER scott DEFAULT ROLE ALL.

\\ All roleS granted to user are active\\

Oracle : ORA-01045: user username lacks CREATE SESSION privilege; logon denied

ERROR:
ORA-01045: user LG2186 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

Resolution:

SQL> grant create session to <username>;