Durham Oracle

Impdp issues...

If you ever end up with lots of invalid synonyms after impdp - quick sql to sort it out.

Just a note as no doubt this will happen again and I won't remember :(

SQL> select owner, object_type, count(object_type) from dba_objects where status != 'VALID' group by owner, object_type order by 1,2

2 /

OWNER OBJECT_TYPE COUNT(OBJECT_TYPE)

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

CON29GIS2 SYNONYM 129

SQL> select 'alter synonym '||owner||'.'||object_name||' compile; ' from dba_objects where status='INVALID' and object_type='SYNONYM' and owner='CON29GIS2' order by 1

2 /

'ALTERSYNONYM'||OWNER||'.'||OBJECT_NAME||'COMPILE;'

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

alter synonym CON29GIS2.ANSWERS compile;

alter synonym CON29GIS2.ANSWER_COMBINATIONS compile;

alter synonym CON29GIS2.API_CALLED compile;

....etc

JAVA CLASS and JAVA SOURCE Objects Become Invalid After Impdp. (Doc ID 1462622.1)

SQL> select owner, object_type, count(object_type) from dba_objects where status != 'VALID' group by owner, object_type order by 1,2
2 /

OWNER OBJECT_TYPE COUNT(OBJECT_TYPE)
----- ----------- ----------
CON29R2 JAVA CLASS 76

SQL> select 'ALTER JAVA CLASS CON29R2."'||OBJECT_NAME||'" resolve;' from dba_objects where status !='VALID' and object_type = 'JAVA CLASS'
2 /

'ALTERJAVACLASSCON29R2."'||OBJECT_NAME||'"RESOLVE;'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER JAVA CLASS CON29R2."SFSexportBlob" resolve;
ALTER JAVA CLASS CON29R2."SFSLiveLinkAPI" resolve;
ALTER JAVA CLASS CON29R2."com/opentext/api/LAPI_ATTRIBUTES" resolve;
ALTER JAVA CLASS CON29R2."com/opentext/api/LAPI_ATTRSERVER" resolve;
ALTER JAVA CLASS CON29R2."com/opentext/api/LAPI_DOCUMENTS" resolve;

Alter user as approp.

Script to resize Oracle database

I have created a link and credit to this in the useful links section but as web pages can often disappear without notice - making a note here as this has proved to very useful :

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Export users - roles & priv (draft)

https://stackoverflow.com/questions/57991488/how-to-export-users-in-oracle-with-its-roles-and-system-privileges-using-expdp

set long 20000

set lines 200

set pages 666

col DBMS_METADATA.GET_DDL('USER',:NAME) format a90

variable name varchar2(50)

exec :name:='CON29';

SELECT dbms_metadata.get_ddl('USER', :name)
  FROM dual
UNION ALL
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', grantee)
  FROM dba_role_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('DEFAULT_ROLE', grantee)
  FROM dba_role_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', grantee)
  FROM dba_sys_privs          sp,
       system_privilege_map   spm
 WHERE sp.grantee = :name
   AND sp.privilege = spm.name
   AND spm.property <> 1
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', grantee)
  FROM dba_tab_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', username)
  FROM dba_ts_quotas
 WHERE username = :name
   AND ROWNUM = 1

SQL> /

DBMS_METADATA.GET_DDL('USER',:NAME)

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

CREATE USER "CON29" IDENTIFIED BY VALUES 'AbcDefGhi'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMPORARY"

.....etc

Run against the new database to create the user.

In AIX to run against multiple DBs e.g.

#!/usr/bin/env bash

set -A array XYZDBA

## Set the env b4 running this

for i in "${array[@]}"

exit | sqlplus -silent / as sysdba << EOF

set head on

set feedback off

set long 30000

set lines 220

set pages 666

## col DBMS_METADATA.GET_DDL('USER',:NAME) format a220

col DBMS_METADATA.GET_DDL('USER','XYZDBA') format a220

SELECT dbms_metadata.get_ddl('USER', '$i')

FROM dual......

( same script as above )

EOF

done

This works too...

declare
data CLOB;
BEGIN
SELECT dbms_metadata.get_ddl('USER','SFS') into data from dual;
DBMS_XSLPROCESSOR.CLOB2FILE(data,'DIRECTORY**','create_user.sql');

END;

**DIRECTORY - change as approp. e.g. DATA_PUMP_DIR find from dba_directories

For the tablespaces for the above..

SQL> SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN

DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);

SQL> SQL> 2 3 DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);

END;

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)

FROM dba_tablespaces

WHERE tablespace_name = DECODE(UPPER('&1'), 'ALL', tablespace_name, UPPER('&1'));

4 5 SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

SQL> SQL> 2 3 Enter value for 1: Enter value for 1: SQL>

SQL> /

Enter value for 1: NWLMEAVX

CREATE TABLESPACE "NWLMEAVX" DATAFILE etc......

You will need to alter to suit when going from ASM to Unix datafiles or vice-versa.

see https://oracle-base.com/dba/script?category=script_creation&file=tablespace_ddl.sq