mercoledì 11 settembre 2024

RDBMS ORACLE - Statistiche di uno schema DB

 Di seguito alcuni comandi per raccogliere le statistiche di uno schema oracle:

  • DBMS_UTILITY.ANALYZE_SCHEMA --> viene utilizzato per raccogliere statistiche per tutte le tabelle, i cluster e gli indici di uno schema.
Examples:

  • EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');

  • EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);

  • EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);

  • EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');

Note: È anche possibile analizzare l'intero database con il comando  DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); 

oppure si può utilizzare DBMS_STATS.GATHER_TABLE_STATS

  • EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'CONTRACT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);

  • EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'CONTRACT', tabname => 'CONT_NAME', method_opt => 'FOR ALL COLUMNS', granularity => 'ALL', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE);

il seguente script genererà l'elenco dei comandi per l'analisi di tutte le tabelle nello schema.

SELECT 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS
 (ownname =>'||CHR(39)||'CONTRACT'||CHR(39)||', 
  tabname =>'||CHR(39)||TNAME||CHR(39)||', 
  method_opt => ''FOR ALL COLUMNS'', 
  granularity => ''ALL'', 
  cascade => TRUE, 
  degree => DBMS_STATS.DEFAULT_DEGREE);'
FROM TAB;

Terminando abbiamo la possibilità di utilizzare due metodi differenti per la raccolta delle statistiche. Per capire quale utilizzare si rimanda alla documentazione ORACLE:

  • DBMS_UTILITY.ANALYZE_SCHEMA
  • DBMS_STATS.GATHER_TABLE_STATS



lunedì 12 agosto 2024

SQL developer error with "disable modules and continue"

Nel caso in cui state utilizzando SQLDEVELOPER e ricevete un errore del tipo:

"Warning - could not install some modules: oracle.java_annotations - org.netbeans.InvalidException: Netigso: D:\sqldeveloper-4.0.2.15.21-no-jre\sqldeveloper\ide\lib\annotations.jar: Not found bundle:oracle.java_annotations oracle.ide_boot  ecc."

Allora occorre fare un pò di pulizia nella system_cache di sqldeveloper.

L'esempio di seguito è per UBUNTO.


Una volta cancellata la directory si può aprire nuovamente ODI.






venerdì 23 febbraio 2024

UBUNTU - How do I mount shared folders in Ubuntu using VMware

Per condividere una directory tra host e VM occorre prima accedere a vmware "Virtual Machine Settings" e spostarsi su "Options". 

In questo tab accedere a "Shared Folders" e configurare una directory di cui fare lo share. 

Lato Ubuntu una volta configurato vmware effettuare la configurazione del file fstab. Ma prima occorre configurare i tools di vmware su Ubuntu.

Eseguire il seguente comando per abilitare i tools:

  • sudo apt-get install open-vm-tools open-vm-tools-desktop
A questo punto accedere al file fstab ed aggiungere la stringa che permette la mount della directory presente sull'host.

  • sudo vi /etc/fstab
# /etc/fstab: static file system information.
#
# Use 'blkid' to print the universally unique identifier for a
# device; this may be used with UUID= as a more robust way to name devices
# that works even if disks are added and removed. See fstab(5).
#
# <file system> <mount point>   <type>  <options>       <dump>  <pass>
# / was on /dev/sda3 during installation
UUID=dad2f2d1-2c0a-4e88-831c-67a122f0572c /               ext4    errors=remount-ro 0       1
# /boot/efi was on /dev/sda2 during installation
UUID=DA2B-A740  /boot/efi       vfat    umask=0077      0       1
/swapfile                                 none            swap    sw              0       0
/dev/fd0        /media/floppy0  auto    rw,user,noauto,exec,utf8 0       0

A questo punto aggiungere al termine del file la stringa che permette di effettuare la mount della directory host:

.host:/ORACLE_PROJECT    /mnt/hgfs/ORACLE_PROJECT/    fuse.vmhgfs-fuse    defaults,allow_other,uid=1000     0    0

Salvare il file e creare la directory indicata sopra:
  • cd /mnt/hgfs
  • mkdir ORACLE_PROJECT
A questo punto fare un test eseguendo il comando :
  • mount -a
Se tutto ok allora avremo effettuato correttamente la mount.

orcl1521@orcl1521-virtual-machine:~$ ll /mnt/hgfs
total 28
drwxr-xr-x 3 root     root  4096 feb 23 17:44 ./
drwxr-xr-x 3 root     root  4096 feb 19 17:12 ../
drwxrwxrwx 1 orcl1521 root 20480 feb 23 17:42 ORACLE_PROJECT/
orcl1521@orcl1521-virtual-machine:~$ ll /mnt/hgfs/ORACLE_PROJECT/
total 9308
drwxrwxrwx 1 orcl1521 root   20480 feb 23 17:42  ./
drwxr-xr-x 3 root     root    4096 feb 23 17:44  ../
drwxrwxrwx 1 orcl1521 root    4096 mag 11  2022  001_ORACLE_BASE_DBA_Scripts/
drwxrwxrwx 1 orcl1521 root    4096 gen 11  2023  02_WORKDAY/
-rwxrwxrwx 1 orcl1521 root  974608 feb 19 18:32  report_export1.xml*
-rwxrwxrwx 1 orcl1521 root  211773 feb 19 18:33  report_export2.xml*
-rwxrwxrwx 1 orcl1521 root   91339 feb 19 18:33  report_export3.xml*
-rwxrwxrwx 1 orcl1521 root  601587 feb 19 18:33  report_export4.xml*
-rwxrwxrwx 1 orcl1521 root  148404 feb 19 18:33  report_export5.xml*
-rwxrwxrwx 1 orcl1521 root  160672 feb 19 18:34  report_export6.xml*
-rwxrwxrwx 1 orcl1521 root  137419 gen 15  2019  report_export.xml*
drwxrwxrwx 1 orcl1521 root       0 mag 11  2022  SAP/
-rwxrwxrwx 1 orcl1521 root    3205 feb  1  2019  SEQ_INS_UPD_ANA_EDW_INV2*
-rwxrwxrwx 1 orcl1521 root  109350 apr 30  2019  seq.xml*
-rwxrwxrwx 1 orcl1521 root 3167755 feb 28  2017  SmartExport_PRJ_ODI_AEI_SVIL.xml*
drwxrwxrwx 1 orcl1521 root   12288 mag 11  2022  SOGEI/
drwxrwxrwx 1 orcl1521 root   12288 feb 16 14:05  sw/
drwxrwxrwx 1 orcl1521 root    4096 mag 11  2022  TEST_AEI/
drwxrwxrwx 1 orcl1521 root    4096 mag 11  2022  TEST_EOC/
-rwxrwxrwx 1 orcl1521 root  152302 mar 25  2019  Text1.txt*
-rwxrwxrwx 1 orcl1521 root    7143 mag 10  2019  Text2*
-rwxrwxrwx 1 orcl1521 root     228 apr 30  2019  Text3*

giovedì 11 gennaio 2024

ODI 12c - Shell Unix for Test if Agent ODI is Up o Down

 Di seguito una semplice shell per verificare se un agent ODI sia o meno attivo.


#!/bin/bash

# Set the Oracle ODI Agent URL

AGENT_URL="http://localhost:25910/oraclediagent"


# Check the status of the ODI Agent

#echo "curl -so /dev/null -w "%{http_code}" $AGENT_URL"

STATUS_CODE=$(wget --server-response $AGENT_URL  2>&1 | awk '/^  HTTP/{print $2}' | grep 200 | awk '{print NR}')

echo $STATUS_CODE

# Check the HTTP status code of the ODI Agent

#if [ $STATUS_CODE == "200" ]; then

if [ $STATUS_CODE == "1" ]; then

    echo "Oracle ODI Agent is running."

     exit 0  # Exit with success status

else

    echo "Oracle ODI Agent is not running."

     exit 1  # Exit with failure status

fi


To use this script, follow these steps:

1. Open a text editor and paste the script.

2. Save the file with a `.sh` extension, for example `check_odi_agent.sh`.

3. Open a terminal and navigate to the directory where you saved the script.

4. Make the script executable with the command `chmod +x check_odi_agent.sh`.

5. Run the script with the command `./check_odi_agent.sh`.

6. The script will display whether the Oracle ODI Agent is running or not.

venerdì 22 dicembre 2023

RDBMS ORACLE - Come leggere un campo LONG

 Di seguito un semplice esempio di come leggere un campo LONG.

desc dba_views;

Nome             Nullo?   Tipo                

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

OWNER            NOT NULL VARCHAR2(128)       

VIEW_NAME        NOT NULL VARCHAR2(128)       

TEXT_LENGTH               NUMBER              

TEXT                      LONG                

TEXT_VC                   VARCHAR2(4000 CHAR) 

TYPE_TEXT_LENGTH          NUMBER              

TYPE_TEXT                 VARCHAR2(4000)      

OID_TEXT_LENGTH           NUMBER              

OID_TEXT                  VARCHAR2(4000)      

VIEW_TYPE_OWNER           VARCHAR2(128)       

VIEW_TYPE                 VARCHAR2(128)       

SUPERVIEW_NAME            VARCHAR2(128)       

EDITIONING_VIEW           VARCHAR2(1 CHAR)    

READ_ONLY                 VARCHAR2(1 CHAR)    

CONTAINER_DATA            VARCHAR2(1 CHAR)    

BEQUEATH                  VARCHAR2(12 CHAR)   

ORIGIN_CON_ID             NUMBER 


create table IA_INTEGRATION.gr_views

(owner VARCHAR2(128),

 view_name VARCHAR2(128),

 text clob );


DESC IA_INTEGRATION.gr_views;

Nome      Nullo? Tipo          

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

OWNER            VARCHAR2(128) 

VIEW_NAME        VARCHAR2(128) 

TEXT             CLOB  


insert into IA_INTEGRATION.gr_views 

     select owner,view_name,to_lob(text) 

       from dba_views

       where owner='IA_INTEGRATION';

COMMIT;


SELECT COUNT(1) FROM IA_INTEGRATION.gr_views;

  COUNT(1)

----------

        12


select COUNT(1) 

  from IA_INTEGRATION.gr_views 

    where text like ('%SELECT%');

  COUNT(1)

----------

        12





lunedì 24 luglio 2023

ODI 12c - How to Program Conditional Clauses Using ODI Substitution Methods

Come rendere una procedura ODI dinamica durante la chiamata da parte dell'Agent.Supponiamo di dover effettuare la insert in una tabella leggendo una select che occorre comporre in maniera dinamica ad ogni chiamata a seconda del valore di input. Il che vuol dire che se io ho in input ad esempio un valore leggo dalla tabella A altrimenti leggo dalla tabella B e cosi' via.

How to make an ODI procedure dynamic during the call by the Agent. Suppose we have to insert into a table by reading a select that must be composed dynamically at each call according to the input value. Which means that if I have a value as input, for example, I read from table A, otherwise I read from table B and so on.

INSERT INTO <....>

if valore di input A1 then

SELECT A1 FROM A1

if valore di input A2 then

SELECT A1 FROM A2 

...

if valore di input An-1 then

SELECT A1 FROM An-1

if valore di input An then

SELECT A1 FROM An;

O magari vogliamo rendere un pezzo di una procedura dinamico in base a quanto viene fornito in input. Per ottenere quanto indicato sopra possiamo ad esempio procedere nei seguenti modi:

Or maybe we want to make a piece of a procedure dynamic based on what is provided as input. To obtain the above we can for example proceed in the following ways:

INSERT INTO TEST1  (    JOB_ID,    CRMT_CAPDATE,    CRMT_INSDATE  )

<$

 if  (#PROJECT.JOB_ID == 1) {$> SELECT '1' JOB_ID, SYSTIMESTAMP CRMT_CAPDATE, SYSTIMESTAMP CRMT_INSDATE FROM A1 <$}

 else if  (#PROJECT.JOB_ID == 2) {$> SELECT '2' JOB_ID, SYSTIMESTAMP CRMT_CAPDATE, SYSTIMESTAMP CRMT_INSDATE FROM A2 <$}

 else if  (#PROJECT.JOB_ID == 3) {$> SELECT '3' JOB_ID, SYSTIMESTAMP CRMT_CAPDATE, SYSTIMESTAMP CRMT_INSDATE FROM A3 <$}

 else if  (#PROJECT.JOB_ID == 4) {$> SELECT '4' JOB_ID, SYSTIMESTAMP CRMT_CAPDATE, SYSTIMESTAMP CRMT_INSDATE FROM A4 <$}

 $>     

In questo caso a seconda del valore assunto dalla variabile JOB_ID viene eseguita una ed una sola delle select presenti. Avremo quindi in fase di execution la seguente sostituzione nel caso in cui in input ricevessimo JOB_ID=1: 

In this case, depending on the value assumed by the JOB_ID variable, one and only one of the selects present is performed. We will therefore have the following substitution in the execution phase if we receive JOB_ID=1 as input:

INSERT INTO TEST1  (JOB_ID,ODI_STEP,ODI_NAME) SELECT '1' JOB_ID, SYSTIMESTAMP CRMT_CAPDATE, SYSTIMESTAMP CRMT_INSDATE FROM A1      

Altro modo di scrivere quanto indicato sopra, nel caso in cui input abbiamo un varchar e non un number è il seguente, invece di utilizzare la doppia uguaglianza "==" si può utilizzare il richiamo di una funzione ODI:

INSERT INTO TEST1  (    JOB_ID,    CRMT_CAPDATE,    CRMT_INSDATE  )

<@ 

if  ("#JOB_ID".equals("A1") {@> 

SELECT '1' JOB_ID, CRMT_CAPDATE, CRMT_INSDATE  FROM A1 <@}

else if  ("#JOB_ID".equals("A2") {@> 

SELECT '1' JOB_ID, CRMT_CAPDATE, CRMT_INSDATE  FROM A2 <@}

else if  ("#JOB_ID".equals("A3") {@> 

SELECT '1' JOB_ID, CRMT_CAPDATE, CRMT_INSDATE  FROM A3 <@}

else if  ("#JOB_ID".equals("A4") {@> 

SELECT '1' JOB_ID, CRMT_CAPDATE, CRMT_INSDATE  FROM A4 <@}

 @>     


Di seguito la nota del supporto che spiega il meccanismo di scambio:

Below is the support note explaining the swap mechanism:

· How to Program Conditional Clauses Using ODI Substitution Methods ? (Doc ID 424038.1)











venerdì 16 giugno 2023

RDBMS ORACLE - Query di analisi dello spazio occupato

 Di seguito alcune query per l'analisi dello spazio occupato da un DB oracle, basate sull'analisi dei datafile/tablespace e tabelle AWM.

  • CRESCITA DATABASE BASATO SU DATAFILE

SELECT
(select min(creation_time) from v$datafile) ""Create Time"",
(select name from v$database) ""Database Name"",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) ""Database Size MB"",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) ""Used Space MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) ""Used in % MB"",
ROUND((FREE.P / 1024 / 1024 ),2) ""Free Space MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) ""Free in % MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2)  ""Growth DAY MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3)  ""Growth DAY in % MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) ""Growth WEEK MB"",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) ""Growth WEEK in MB""
FROM    (SELECT BYTES FROM gV$DATAFILE
UNION ALL
SELECT BYTES FROM gV$TEMPFILE
UNION ALL
SELECT BYTES FROM gV$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;

  • DATABASE CRESCITA DAY-WEEK
SELECT MIN(creation_time) Create_Time,  ts.name,
  ROUND(SUM(df.bytes)  /1024/1024) curr_size_MB,
  ROUND( (SUM(df.bytes)/1024/1024)/ROUND(sysdate-MIN(creation_time)),1) growth_per_day_MB,
  ROUND( (SUM(df.bytes)/1024/1024)/ROUND(sysdate-MIN(creation_time)) * 7,1) growth_7_days_MB
FROM v$datafile df ,  v$tablespace ts
WHERE df.ts#=ts.ts#
GROUP BY df.ts#,  ts.name
ORDER BY df.ts#

  • DATABASE CRESCITA - TABLESPACE PER MESE
select v.TS#,d.tablespace_name, 
       v.status ""Status"" ,to_char(creation_time, 'MM-YYYY') ""Month"", 
       ROUND(sum(v.bytes)/1024/1024/1024,3) ""Growth in GB""
FROM v$datafile v, dba_data_files d
where creation_time >to_date('2000','YYYY')
     and d.file_name = v.name
group by TS#,d.tablespace_name, 
         v.status,to_char(creation_time, 'MM-YYYY')
order by  TS#,d.tablespace_name, 
          v.status,to_char(creation_time, 'MM-YYYY')

  • DATABASE CRESCITA - AWR - SNAP_ID - SNAP_TIME
SELECT   A.sNAP_ID,A.SNAP_TIME,
     A.ProgSum/1024/1024 ProgSum,( ((c.bb-b.sad) + a.ProgSum)/(1024*1024)) MB
              from
              ( select a.snap_id,
                  SNAP_TIME,
                  sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
              from
                  (select SNAP_ID,
                      sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
                  from DBA_HIST_SEG_STAT
                  group by SNAP_ID
                  having sum(SPACE_ALLOCATED_TOTAL) <> 0
                  order by 1 ) a,
                  (select distinct SNAP_ID,
                      to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
                  from DBA_HIST_SNAPSHOT) b
              where a.snap_id=b.snap_id ) A,
              (select sum(SPACE_ALLOCATED_DELTA) sad from DBA_HIST_SEG_STAT) B,
              (select sum(bytes) bb from dba_segments) C
order by 1,2


  • DATABASE - MIN/MAX SPAZIO UTILIZZATO TRA 2 SNAP_ID
with snaps as (select min(snap_id) min_snap, max(snap_id) max_snap 
 from dba_hist_snapshot 
 where begin_interval_time > ADD_MONTHS(sysdate,-2))
select
  O.TABLESPACE_NAME,
  O.OWNER||'.'||O.OBJECT_NAME SCHEMA_OBJECT_NAME,
  o.object_type,
  min(SNAP_ID) MIN_OBJ_SNAP_id,
  max(SNAP_ID) max_obj_snap_id,
  ROUND(MIN(H.SPACE_USED_TOTAL)/1024/1024/1024,3) min_space_used_GB, 
  ROUND(SUM(H.SPACE_USED_DELTA)/1024/1024/1024,3) SPACE_USED_GB, 
  ROUND(SUM(H.SPACE_ALLOCATED_DELTA)/1024/1024/1024,3) space_alloc_GB
from
  DBA_HIST_SEG_STAT H join dba_hist_seg_stat_obj o 
  on h.dbid=o.dbid and h.ts#=o.ts# and h.obj#=o.obj# and h.dataobj#=o.dataobj#
WHERE 1=1
  AND H.SNAP_ID BETWEEN (SELECT MIN_SNAP FROM SNAPS) AND (SELECT MAX_SNAP FROM SNAPS)
  and H.DBID = (select DBID from V$DATABASE)
  and H.INSTANCE_NUMBER = (select INSTANCE_NUMBER from V$INSTANCE)
  and O.OWNER != '** MISSING **' -- segments already gone
  and O.OBJECT_NAME not like 'BIN$%' -- recycle-bin
  and O.OBJECT_NAME not like 'SYS_%' -- LOBs, etc - not too representative
  AND o.OWNER NOT IN ('APEX_030200','SCOTT','OWBSYS','PERFSTAT',
  'FLOWS_FILES','PUBLIC','SYS','SYSTEM','OUTLN','DIP','DBSNMP','WMSYS', 
  'EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA',
  'MDSYS','OLAPSYS','MDDATA','SYSMAN','MGMT_VIEW','VIP')
group by
  O.TABLESPACE_NAME,O.OWNER||'.'||O.OBJECT_NAME,o.OBJECT_TYPE
having SUM(H.SPACE_ALLOCATED_DELTA)>0