ORA-39082: Object type created with compilation warnings during import

This error often occurs when running data import via impdp utility.

Cause: It occurs due to circular dependencies and reordering the sequences of importing the objects

Solution Run the utlrp.sql script located in $ORACLE_HOME/rmdms/admin directory.

>>cd $ORACLE_HOME/rmdms/admin 
>>ls -ltr utlrp*
>>@utlrp.sql

Incorrect syntax near GO and why one cannot use GO when executing dynamic sql

When dynamic sql is executed using sp_executesql,

GO

keyword cannot be used because it will produce and error "Incorrect syntax near GO". Also that SQL Server will be looking for a carriage return CHAR(13) and a line feed CHAR(10)

Example:

-- declare some variables
DECLARE @SQL VARCHAR(100)
DECLARE @dbName VARCHAR(100)-- Get the current or target database name
SET @dbName = DB_NAME()-- Build the script
SET @sql = 'USE [' + @dbName + ']' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'GO' + CHAR(13) + CHAR(10)-- Print the command
PRINT (@sql)

The code above will work for printing out the statement to the screen, but it will not execute if you add EXEC sp_executesql @sql

The solution would be to remove the

GO

keyword altogether. The code below will execute without an issue:

-- declare some variables
DECLARE @SQL VARCHAR(100)
DECLARE @dbName VARCHAR(100)-- Get the current or target database name
SET @dbName = DB_NAME()-- Build the script
SET @sql = 'USE [' + @dbName + ']' + CHAR(13) + CHAR(10)
SET @sql = @sql + CHAR(13) + CHAR(10)-- Print the command
PRINT (@sql)
EXEC sp_executesql @sql

The only way to get the dynamic sql to work with

GO

is to either copy the output to another window and run from there or write it to external file and then execute from your code using

exec master..xp_cmdshell 'osql -E -i C:\Scripts\test.sql'

Korn Shell scripting intro

I recently started writing korn shell scripts here and there and below is some useful info on how to get started. I use vi editor for creating files:

vi myscript.ksh

Korn shell scripts usually have extension ksh The first line of the script should always be:

#!/usr/bin/ksh

After the file has been created, one needs to give it execute permissions so that the file becomes runable:

chmod 755 myscript.ksh

Now the script can be executed as follows:

./myscript.ksh

if the script name or path has spaces in it, it needs to be wrapped in double quotes

"C:\My Documents\My Scripts\myscript.ksh"

The escape character for ksh scripts is a (\) backslash. For example if you want to escape a dollar sign ($) inside your script, instead of writing

select * from v$database;

use

select * from v\$database;

(#) pound sign is used for comments and print and echo commands are used to output text to a sceen

WITH GRANT OPTION vs. WITH ADMIN OPTION

Role or system privileges are granted using WITH ADMIN OPTION, and table-specific privileges are granted using WITH GRANT OPTION.

Revoking a privilege granted using WITH GRANT will cascade revoke any and all privileges assigned by the target user to other users, but revoking a privilege granted using WITH ADMIN will only revoke privileges for the target user, leaving intact all the othwer users target user might have granted privileges to.

Example:

WITH GRANT OPTION

DBA can grant object privileges using with grant option (select,update,insert) to A, and A, in turn, grants those privileges to B.

DBA--------->A--------->B

DBA can only revoke the privileges from A but not from B, because WITH GRANT OPTION implies that we can only revoke those privileges that we have granted. Only the user who granted privileges can revoke them. But if DBA revoke A's privileges, the priveleges will cascade revoke from B as well.



WITH ADMIN OPTION

We grant the system privileges to A using with admin option(CREATE TABLE,CREATE SESSION)

DBA------>A-------->B

Here DBA can revoke privileges from either A or B. But if DBA only revokes privileges from A, they won't be cascade revoked from B.

Users with ADMIN OPTION for a system privilege can revoke the privilege of any other database user, the revoker does not have to be the same user who originally granted the privilege.

Clone user privileges script

Today I finalized my sql script for cloning Oracle users:

set pages 0;
set linesize 100;
set feedback off; 
set verify off; 

spool user_privs_clone.sql
-- user itself 
select 'create user &&new_user identified by values '''||password||''' '||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&cur_user');

-- user roles
select 'grant '||granted_role||' to &&new_user'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&cur_user');

-- system privileges
select 'grant '||privilege||' to &&new_user'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&cur_user');

-- table privileges
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&new_user;'
from sys.dba_tab_privs
where grantee = upper('&&cur_user');


-- column privileges
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&new_user;'
from sys.dba_col_privs
where grantee = upper('&&cur_user');


-- tablespace quotas
select 'alter user '||username||' quota '||
decode(MAX_BYTES,-1,'UNLIMITED', to_char(MAX_BYTES/(1024*1024),'9999') || 'M')||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&cur_user');


-- default roles
set serveroutput on;
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&cur_user')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||',
'||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&new_user default role '||defroles||';');
end;
/

set serveroutput off;
spool off;

All the sql statements, including CREATE USER get spooled to a user_privs_clone.sql file. When the script runs, you will be prompted for the name of the user you want to create/or if user already exists, for whichever user you want to grant privileges. Next you will be prompted for the username of a user whose privileges you would like to clone.

Issue cloning user privileges

I was using the script below to clone the user privileges of user1 and assign them to user2:

select 'grant ' || PRIVILEGE || ' on ' || owner || '.' || TABLE_NAME || ' to ' || 'USER2' || decode(GRANTABLE,'YES',' with grant option;','NO',';') from
  dba_tab_privs
where
  GRANTEE = upper('USER1')
union
select
  'grant ' ||GRANTED_ROLE || ' to ' || 'USER2' || ';'
from
  dba_role_privs
where
  GRANTEE = upper('USER1')
union
select
  'grant ' || PRIVILEGE || ' to ' || 'USER2' || ';'
from
  dba_sys_privs
where
  GRANTEE = upper('USER1')
union
select
  'alter user ' || 'USER2' || ' quota ' || decode(MAX_BYTES,-1,'UNLIMITED', to_char(MAX_BYTES/(1024*1024),'9999') || 'M') || ' on ' || TABLESPACE_NAME || ';'
from
  dba_ts_quotas
where
  USERNAME = upper('USER1');

Everything seems to be working fine and both users had identical privileges, yet, USER2 was not able to see all the tables that USER1 could see.

The issue turned out to be that bothe users wer granted two roles ROLE1 and ROLE2, but the only difference was that ROLE2 was a default role for both users, but ROLE1 was default only for USER1.

SQL> SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM dba_role_privs WHERE grantee='USER1';

GRANTED_ROLE                   ADM DEF
------------------------------ --- ---
ROLE1                          NO  YES
ROLE2                          NO  YES


SQL> SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM dba_role_privs WHERE grantee='USER2';

GRANTED_ROLE                   ADM DEF
------------------------------ --- ---
ROLE1                          NO  NO
ROLE2                          NO  YES

Running the sql below fixed the issue

ALTER USER USER2 DEFAULT ROLE ALL;

or

ALTER USER USER2 DEFAULT ROLE ROLE1, ROLE2;

Caution: Running

ALTER USER USER2 DEFAULT ROLE ROLE1;

would set ROLE1 as a default role but remove all other roles (in this case ROLE2) from default role.

IP Addresses and Oracle RAC

There are 3 types of IP addresses used in RAC:

• Private IP - used for internal communication between the cluster nodes
• Public IP - regular IP address used to communicate with the network
• Virtual IP - used by database applications to enable failover when one node fails

Connections to the server are done through listener, and if connection has been established and listener fails, that's not an issue since after connection has been established, there is no need for a listener. Now if the net connection is trying to get a session while listener os down, the user will get an error message as connection fails. That's in a regular environment.

In RAC environment database is in a sharing mode - shared between all conencted nodes thus more than 1 listener is running in various nodes. So if a listener is down and user process is trying to get a connection, RAC will automatically transfer that request to another listener on a different node.

Before physical IP addresses were used in listener configuration which is a disadvantage over using virtual IP addresses because when using physical IP the session will wait for a timeout before failing over to another node. using Virtual IP (VIP) in listener coniguration because Oracle Notifiction Service (OBS) maintains the communication between the nodes and will notify all the nodes and listeners whenever one of the nodes is down, and VIP of the failed node will automatically divert to surviving node and session will be established in that node. There won't be any wait for TCP/IP timeout event.