Wanted to compare a sequence value with a max value in a corresponding table. The declare -n option not available in the bash shell on the server (surprised as new OCI build and thought bash would be greater than 4.2) but this worked in ksh.  #!/bin/ksh monitor=( [sys1]=(seqname="SEQ_SAMPLE_KEY0" tablename="SAMPLE" rowname="ID_NUMERIC" ) [sys2]=(seqname="SEQ_TEST_TEST_NUMBER" tablename="TEST" rowname="TEST_NUMBER") [sys3]=(seqname="SEQ_ACCESS_LOG_KEY0" tablename="ACCESS_LOG" rowname="SESSION_ID") [sys4]=(seqname="SEQ_AUDITOR_DATA" tablename="AUDIT_DATA" rowname="DATA") [sys5]=(seqnam ..read more

Multi-byte characters and problem resolution when going from on-prem to OCI When doing a data pump from on-prem to the cloud you will often hit errors with conversion errors. Plenty of background information but little on resolving this, so putting this together as a reminder when going forward. Typical errors when doing datapump - ORA-02374: conversion error loading table "ABC"."STANDARD_LETTERS" ORA-12899: value too large for column NAME (actual: 51, maximum: 50) ORA-02372: data for row: NAME : 0X'4D65746572205374726174656779202D204D6574657220696E' …….. . . imported "ABC"."STANDARD_LETTE ..read more

 Good link for patching databases in Oracle OCI - will update this as we go along. https://www.pythian.com/blog/oci-database-cloud-service-patching-of-database-and-grid-infrastructure-using-oci-cli ..read more

Changing time zone Default Autonomous Database Time Zone The default Autonomous Database time zone is Coordinated Universal Time ‎(UTC)‎ and by default calls to SYSDATE and SYSTIMESTAMP return the date and time in UTC. In order to change database time zone, you can run the following statement.  This example sets the database time zone to UTC-5. ALTER DATABASE SET TIME_ZONE='-05:00'; Note: You must restart the Autonomous Database instance for the change to take effect. After you set the database time zone, by default SYSDATE and SYSTIMESTAMP continue to return date and time in UTC (S ..read more

Quick notes re dbcli commands -  [root@ ~]# dbcli describe-system DbSystem Information ---------------------------------------------------------------- ID: c13eb0d0-........... Platform: Vmdb Data Disk Count: 8 CPU Core Count: 2 Created: August 3, 2022 4:49:59 PM BST System Information ---------------------------------------------------------------- Name: gi2iuoaa Domain Name: s003.ldntier4prd.oraclevcn.com Time Zone: Europe/London DB Edition: EeHp DNS Servers: NTP Servers: 169........ Disk Group Information ---------------------------------------------------------------- DG Name Redu ..read more

Needed to quickly check the Weblogic version that I was using - quick notes. From $WL_HOME/server/lib run this : [oracle@dev01-wls-1 lib]$ pwd /u01/app/oracle/products/fmw_12214/wlserver/server/lib [oracle@dev01-wls-1 lib]$ java -cp weblogic.jar weblogic.version -version -verbose WebLogic Server 12.2.1.4.0 Thu Sep 12 04:04:29 GMT 2019 1974621 ImplVersion: 12.2.1.4.0 Oracle Security Developer Tools Security Engine ImplVersion: 3.1.0 Oracle Security Developer Tools Crypto ImplVersion: 3.1.0 Oracle Universal Connection Pool ImplVersion: 19.3.0.0.0 etc... There are other ways to che ..read more

Using the note from here - https://oracle-base.com/articles/12c/multitenant-startup-and-shutdown-cdb-and-pdb-12cr1 "Oracle-Base" always excellent but please check this note on Metalink and the note below if using TDE. Cloning PDB From PDB Fails With ORA-28357: Password Required To Open The Wallet (Doc ID 2415131.1) Example database ORAPDB and example password used.  ++++++++++++++++++++++++++++++ [oracle@dev01tier2-dbs admin]$ sqlplus / as sysdba  SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 30 12:25:22 2021 Version 19.9.0.0.0 Copyright (c) 1982, 2020, Oracle.  All right ..read more

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 ..read more

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 ..read more

  https://stackoverflow.com/questions/57991488/how-to-export-users-in-oracle-with-its-roles-and-system-privileges-using-expdp SQL> set long 20000 SQL> set lines 200 SQL> set pages 666 SQL> col DBMS_METADATA.GET_DDL('USER',:NAME) format a90 SQL> variable name varchar2(50) SQL> exec :name:='CON29'; tabltable l 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   ..read more