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.