#####SQLPLUS SET PAGES AND LINES#####
set lines 200 
set pages 999 
col status for a7


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

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

####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;
select instance_name from v$instance;
select name from V$database;

---Vedere istanza e DB (Matteo)---
SET LINES 200
SET PAGES 999
COLUMN INSTANCE_NAME FOR A20
SELECT INSTANCE_NAME, HOST_NAME, VERSION, TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME", STATUS
FROM V$INSTANCE;

## Check the startup history timing of Oracle Instance
COL INSTANCE_NAME FOR A10
SELECT INSTANCE_NAME, TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"
 FROM DBA_HIST_DATABASE_INSTANCE ORDER BY STARTUP_TIME DESC;



####Vedere tutte le sessioni attive su un DB#####
SELECT username FROM v$session 
WHERE username IS NOT NULL 
ORDER BY username ASC;


#####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';

OPPURE:
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;

OPPURE:
select tablespace_name, file_Name, bytes from dba_data_files;
per MB: select tablespace_name, file_Name, bytes/1024/1024 from dba_data_files;

oppure per avere anche mostrare anche la proprietà di autoextend:
select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files where TABLESPACE_NAME like 'tbs1';

####rinominare un datafile####
prima devo mettere offline il tablespace:  alter tablespace tbs1 offline;
modifico il nome nel filesystem: mv data0.dbf data99.dbf
lo rinomino da SQLPLUS: alter database rename file '/disk2/prod1/data/data01.dbf' to  '/disk2/prod1/data/data01.dbf';
poi lo rimetto online: alter tablespace tbs1 online;

#####vedere spazio libero tablespace#####
select tablespace_name, bytes/1024/1024 from dba_free_space where tablespace_name = 'TBS1';
byte/1024/1024 serve per visualizzare in MB



####Vedere i datafile per un determinato tablespace####

---Con Variabile Tablespace in prompt----
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;
--------------------------------



#####Gestire temporary teblespace####
select tablespace_name, file_Name, bytes/1024/1024, status from dba_temp_files;
alter database tempfile '/disk2/prod1/data/temp01.dbf' resize 70m;
create temporary tablespace temp1 tempfile '/disk2/prod1/data/temp02.dbf' size 10m;
--creare un temporary tablespace group(insieme di tablespace) e renderlo default:
Creo un nuovo temp tablespace e un nuovo gruppo: create temporary tablespace temp2 tempfile '/disk2/prod1/data/temp03.dbf' size 10m tablespace group temp_group;
Aggiungo un altro TB esistente al gruppo: alter tablespace temp1 tablespace group temp_group;
vedo l'attuale TB temporaneo di default: select * from database_properties where property_name like '%TABLESPACE%';
assegno il TB group come default: alter database default temporary tablespace temp_group;

######Gestire UNDO Rollback tablespace####
select tablespace_name, file_name, bytes/1024/1024 from dba_data_files;
show parameter undo;
alter tablespace undotbs1 add datafile '/disk2/prod1/data/undotbs1B.dbf' size 10m autoextend on maxsize unlimited next 1m;

######Gestire Redo Log file e arch file#####
vedere i logfile: select * from v$logfile;
vedere spazio dei logfile: select group#, bytes/1024/1024, status from v$log;
Aggiungere un logfile ad un loggroup: alter database add logfile member '/disk2/prod1/log/redo03b.log' to group 3; Dopo aver aggiungo un log file, devo fare "alter system switch logfile" (anche più volte) per forzare l'utilizzo
Vedere se un DB è in archive mode on: archive log list;
Per attivare l'archive mode:
	Spegnere il DB/avviare il DB in startup mount
	alter database archivelog;
	alter system set log_archive_dest_1 = 'LOCATION=/disk2/prod1/arch/' scope=both;
	alter database open;
	archive log list;
	



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

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

#####Creare un tablespace###
create tablespace tbs1
datafile '/disk1/prod/data/data01.dbf'
size 50m
autoextend on
next 512k
maxsize 250M;

#####Rimuovere tablespace###
drop tablespace tbs1 including contents and datafiles;

####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'; 

####Creare una tabella create table####
CREATE TABLE SALES (
    ORDER_NUMBER NUMBER,
    ORDER_DATE DATE,
    PRODUCT_ID NUMBER PRIMARY KEY,
	ITEM_CNT NUMBER,
	ORDER_AMOUNT NUMBER)
    PARTITION BY RANGE (order_date)
   (PARTITION sales_P1 values less than (TO_DATE('2021-06-30', 'YYYY-MM-DD')),
	PARTITION sales_P2 values less than (MAXVALUE));


####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

####Gestione Utenti users#######
create user john identified by Password123;
create user john identified by Password123 password expire;  (cambio obbligatorio al primo accesso)
GRANT CREATE SESSION to John; Per creare una sessione sqlplus
GRANT CONNECT TO John; Grant di base per collegarsi
GRANT RESOURCE TO John; Grant di base per risorse
ALTER USER john identified by PasswordNuova; per cambiare password
ALTER USER john account lock|unlock;  per bloccare sbloccare utente
select username, account_status from dba_users;  per vedere tutti gli utenti
select default_tablespace, temporary_tablespace from dba_users where username='JOHN'; Per vedere il default TB dell'utente
ALTER user john default tablespace TB_BOHHH; per modificare il TB dell'utente
ALTER user john quota 5m on TB_BOHHH; Assegnare una quota nello specifico tablespace
drop user john cascade; elimina un utente, con cascade elimina anche gli oggetti dell'utente.

ESEMPIO:
CREATE USER SALES_USER1
    IDENTIFIED BY 12345678
    DEFAULT TABLESPACE SALES_TB
	TEMPORARY TABLESPACE TEMP1;
GRANT create session,connect,CREATE TABLE to SALES_USER1;


####Gestione Privilegi Privileges#####
select * from session_privs; Visualizza i privilegi dell'utente connesso
grant create table to Pippo; concede il permesso di creare tabelle sul prioprio schema a pippo
grant create table to Pippo with admin option; concede il permesso di creare tabelle sul prioprio schema ed estendere il privilegio ad un'altro utente.
grant select on pippo.tabella to pluto;
grant select, delete on pippo.tabella to pluto;

####Gestione Ruoli, ROLES######
SELECT * FROM DBA_ROLES;




------------------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;
##########################################################################################


######Controllare stato del cluster  RAC####
crsctl check cluster -all
crsctl stat res -t


####RMAN COMANDI#####
SHOW ALL; mostra tutti i parametri
CONFIGURE RETENTION POLICY TO RECOVERY WINDOWS OF 7 DAY;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
BACKUP DATABASE;
BACKUP AS COPY DATABASE;
BACKUP CURRENT CONTROLFILE;
BACKUP AS BACKUPSET DATAFILE	
	'/DATA/USERS01.DBF'
BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-30' AND 'SYSDATE';
BACKUP TABLESPACE SYSTEM, USERS;
BACKUP SPFILE;
LIST BACKUP OF DATABASE;
BACKUP INCREMENTAL LEVEL0 DATABASE;
BACKUP INCREMENTAL LEVEL1 DATABASE;
BACKUP INCREMENTAL LEVEL1 CUMULATIVE DATABASE;


######indici index###

create index SALES_IDX on SALES(PRODUCT_ID);

VEDERE gli indici di una tabella (utilizzando una variabile in prompt)
select    table_name, index_name, column_name
from    all_ind_columns
where    table_name like upper('&TABLE_NAME')
order by table_name, index_name, column_position;