iscsiadm -m discovery -t st -p 10.240.252.1:3260
iscsiadm -m node -T iqn.2002-03.com.compellent:5000d3100d08722e -p 10.240.252.1 -l
 
 
iscsiadm -m node --login   (ricollega tutti i multipath)
iscsiadm -m node --logout  (fa il logout da tutti i multipath)


###Visualizzare tutti i multipath####
multipath -ll

###Informazioni dettagliate sui dischi e multipath###
iscsiadm -m session -o show -P 3


####Per marcare i volumi per ASM####
/usr/sbin/oracleasm createdisk VOL1 /dev/mapper/mpathb
Cancellare un disco marcato:
dd if=/dev/zero of=/dev/mapper/mpathXX bs=1024 count=4


####Riscansionare dischi da ASM####
oracleasm scandisks

###Montare una share NFS per voting####
192.168.148.175:/votedisk /voting_disk nfs rw,bg,hard,intr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600 0 0


####Controllare se i diskgroup sono montati####
sqlplus / as sysasm
SQL> set pages 444
SQL> set lines 444
select * from v$asm_diskgroup;




####impostare variabile ambiente#####
. oraenv
+ASM(numero istanza)

####installare nuovo database con GUI#####
export DISPLAY=192.168.100.2:0.0
/u01/app/oracle/product/11.2.0/dbhome_1/bin/dbca


####fare il login come sysdba####
sqlplus / as sysdba
cambiare utente collegato "connect NomeUtente@database

####Vedere l'istanza a cui si è attualmente connessi e il DB###
SELECT sys_context('USERENV','INSTANCE_NAME') AS Instance
FROM dual;

SELECT sys_context('USERENV','DB_NAME') AS Database_Name
FROM dual;

select * from v$instance;


#####vedere tutte le tabelle###
SELECT *
FROM all_tables
WHERE OWNER = 'WTO_ESEMPIO'
ORDER BY table_name;

####Vedere i tablespace####
SELECT DISTINCT sgm.TABLESPACE_NAME , dtf.FILE_NAME
FROM DBA_SEGMENTS sgm
JOIN DBA_DATA_FILES dtf ON (sgm.TABLESPACE_NAME = dtf.TABLESPACE_NAME)
WHERE sgm.OWNER = 'WTO_ESEMPIO';

####Vedere i datafile per un determinato tablespace####
col file_name for a80
col tablespace_name for a22 
set lines 200 
set pages 999 
col status for a7
select a.tablespace_name, 
--a.file_id, 
a.file_name, 
a.bytes/(1024*1024) MB_ATT, 
a.MAXBYTES/(1024*1024) MB_MAX,substr(a.AUTOEXTENSIBLE,1,1) AUTOEXT, b.status
from dba_data_files a, dba_tablespaces b
where a.TABLESPACE_NAME LIKE '%&tbsp%'
and a.tablespace_name = b.tablespace_name
--and a.AUTOEXTENSIBLE='YES' 
order by a.file_name;

###check dischi di voting####
crsctl query css votedisk

#####estendere e aggiungere un datafile####
ALTER TABLESPACE SIGP_LAVORO ADD DATAFILE '/oradata/SGP/sigp_lavoro041' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
alter database datafile '/u02/app/oracle/oradata/BDA/bda/datafile/bac_adapter.471.852036957' RESIZE 3072M;						--AMM

####ASM Controllare se i diskgroup sono montati####
sqlplus / as sysasm
SQL> set pages 444
SQL> set lines 444
select * from v$asm_diskgroup;


####Creare una vista####
create view clienti_roma as
 select nome, cognome
 from clienti
 where comune='H501'; 


####Cestino###
Vedere il cestino:
 select OBJECT_NAME, original_name, type
from user_recyclebin;

PURGE RECYCLEBIN  //svuota il cestino
Purge table|index nometabellaOindice  //svuota solo la tabella o indice
FLASHBACK TABLE <nome schema>.<nome tbella> TO BEFORE DROP  //recupera una tabella


------------------Alert-log------------------------------
## For 11g and later releases (12c, 18c, 19c)
$ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/

## For 9i and 10g
$ORACLE_BASE/admin/$ORACLE_SID/bdump/

## For rest of cases without any clues
$ORACLE_HOME/rdbms/log/

## Find it
SQL> show parameter background_dump_dest;
SQL> select value from v$parameter where name = 'background_dump_dest';

oracle$ find $ORACLE_BASE -type f -name alert_$ORACLE_SID.log
oracle$ locate alert_$ORACLE_SID.log
--------------------------------------------------------------------


##########CERCARE un oggetto restituendo il proprietario e il tipo######
select u.name as owner, o.name as object_name, 
 decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
 11, 'PACKAGE BODY', 12, 'TRIGGER',
 13, 'TYPE', 14, 'TYPE BODY',
 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
 32, 'INDEXTYPE', 33, 'OPERATOR',
 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
 43, 'DIMENSION',
 44, 'CONTEXT', 47, 'RESOURCE PLAN',
 48, 'CONSUMER GROUP',
 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNKNOWN') as type,
o.ctime, o.mtime,
 to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
 decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID') as status
 from sys.obj$ o, sys.user$ u
 where o.owner# = u.user#
 and o.linkname is null
 and (o.type# not in (1 , 10) or
 (o.type# = 1 and 1 = (select 1
 from sys.ind$ i
 where i.obj# = o.obj#
 and i.type# in (1, 2, 3, 4, 6, 7, 9))))
 and o.name like 'TABELLA_DA_CERCARE_CASE_SENSITIVE';
#############################################################################

######TOP N###############################################################
### To get top N sql ordered by Elapsed time (3), or by CPU Time (4), Buffer Gets (7) etc.
### Provide the BEGIN & END SNAP_ID and the num. N of SQLs to be displayed.
set pages 1000 lines 1000
col begin_interval_time for a30
col end_interval_time for a30
---to get list of snap_id
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id asc;


set head on
column module format a20
set lines 200
prompt SQL Ordered by Elapsed time

select * from (
select sql_id, module,
sum(ELAPSED_TIME_DELTA)/1000000 "Elapsed Time(s)",
sum(CPU_TIME_DELTA)/1000000 "CPU Time(s)",
sum(executions_delta) "Executions",
sum(ROWS_PROCESSED_DELTA) rows1,
sum(BUFFER_GETS_DELTA) "Buffer Gets",
sum(DISK_READS_DELTA) "Physical Reads",
sum(iowait_delta)/1000000 "IO Wait",
sum(ccwait_delta)/1000000 cc_wait,
sum(apwait_delta)/1000000 ap_wait,
sum(clwait_delta)/1000000 cl_wait,
sum(BUFFER_GETS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) gets_per_row,
sum(DISK_READS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) prds_per_row,
sum(BUFFER_GETS_DELTA)/decode(sum(executions_delta), 0, 1, sum(executions_delta)) gets_per_exec
from dba_hist_sqlstat
where snap_id between &min_snap_id and &max_snap_id
group by sql_id, module
order by 3 desc
) where rownum <= &rnum;
##########################################################################################