Oracle/SQL : Différence entre versions

De Wiki-noulot
Sauter à la navigation Sauter à la recherche
Ligne 331 : Ligne 331 :
 
==Select Hardware par position et status==
 
==Select Hardware par position et status==
  
−
  select A.ASSET_TAG, A.E_NUM_BAIE, A.E_NUM_BAIE_U, S.STATUS_FR from am_asset A left outer join AM_STATUS S on A.status_ID=S.STATUS_ID where A.asset_tag like 'HW%' order by A.E_NUM_BAIE desc;
+
  select A.ASSET_TAG, A.E_NUM_BAIE, A.E_NUM_BAIE_U, S.STATUS_FR  
 +
from am_asset A left outer join AM_STATUS S on A.status_ID=S.STATUS_ID  
 +
where A.asset_tag like 'HW%' order by A.E_NUM_BAIE desc;
 +
 
 +
==Select Application Installé sur quel Host==
 +
 
 +
select A.asset_tag CI, A.network_identifier APPLICATION_INSTALLE, serveur.network_identifier SERVEUR 
 +
from am_asset A left outer join configuration_item_link toto
 +
on A.asset_id=toto.CHILD_CI_ID left outer join am_asset serveur
 +
on toto.PARENT_CI_ID=serveur.asset_id
 +
where A.network_identifier like 'JBOSS%'
 +
and A.ASSET_TAG like 'LG10%'
 +
and serveur.ASSET_TAG like 'LG50%'
 +
and serveur.STATUS_ID not like'7';

Version du 8 septembre 2015 à 11:26

Oracle

Install SQLPUS

rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
dans .bash_profile

Def

VLDB = Very Large Databases 
SGA = System Global Area
      Zone de mémoire à l'intérieur de la mémoire principale utilisée
      pour stocker des données pour un accès rapide. Oracle utilise le
      pool partagé pour allouer de la mémoire SGA en commun, les 
      procédures SQL et PL/SQL.
LOB = Objet Binaire Large (Fichier ex: photo, scan...)
DBMS = data bas manage system (programe fonctionel "Pakage" (ex: Sats))
Extent = Groupe de block contigus 
Segment = Ensemble d'extent > Objet
Table Space = Espace de stockage des données
Grid Computing = Ecriture en RAM
Shemat = Objet utilisateur
OFA = oracle flexible architecture
flash_recovery_area = repertoire "Corbeille" (Purge par def toutes les 15 min)
Dictionnaire de données = 
Explain = utilitaire "d'explication" de la requette (execute la requette)

Soft

SQL Developer (gratuit)
Data Modeler (gratuit)

Commandes de base

Connection a une base

connect "user"/"PW"

Connection avec utilisateur du groupe DBA

connect / as sysdba

Changement PW sys

alter user sys identified by "PW";

Arret Base

shutdown immediate

Démarrage Base

Startup

Afficher les tablespaces

desc dba_tablespaces

Creation de tablespace

CREATE SMALLFILE TABLESPACE "T_STAGE" DATAFILE 'C:\Stage\t_stage01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 150M LOGGING EXTENT MANAGEMENT 
LOCAL SEGMENT SPACE MANAGEMENT AUTO

Creation Table

Create Table "NOM"

Contrainte

CONSTRAINT "nom contrante" "Type contrainte"

Type Contrainte

PRIMARY KEY 	: Clé Primaire
FOREIGN KEY 	: Clé étrangère
UNIQUE 	: Unicité de valeur
NOT NULL	: La colonne est obligatoirement renseignée
CHECK 		: Contrôle d’une règle de gestion

Select

select "valeur" from "nom base"

Jointure

select ename, dname from emp, det where emp.deptno= det.deptno;
SQL Joins

Afficher catalog

select * from cat;

Descrition objet

desc "nom objet";

Créer un Role

CREAT ROLE "R_STAGE" NOT IDENTIFIED
GRANT CREATE PROCEDURE TO "R_STAGE"
GRANT CREATE SEQUENCE TO "R_STAGE"
GRANT CREATE SESSION TO "R_STAGE"
GRANT CREATE SYNONYM TO "R_STAGE"
GRANT CREATE TABLE TO "R_STAGE"
GRANT CREATE TABLESPACE TO "R_STAGE"
GRANT CREATE TRIGGER TO "R_STAGE"
GRANT CREATE TYPE TO "R_STAGE"
GRANT CREATE USER TO "R_STAGE"
GRANT CREATE VIEW TO "R_STAGE"

Créer un utilisateur avec Role

CREATE USER "Nom User" PROFILE "DEFAULT" IDENTIFIED BY "Password"  
DEFAULT TABLESPACE "TableSpace def" ACCOUNT UNLOCK

Attribuer un role a un utilisateur

GRANT "role" TO "nom user"

Modification Quota user

ALTER USER "SEBASTIEN" QUOTA 50 M ON "TBS_STAGE"

Consultation des données

SELECT empno, ename, job, sal
FROM   emp
WHERE  sal > 4500;
     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7839 KING       PRESIDENT       5000

Insertion de lignes

insert into dept 
values (50,’FORMATION’,’BIARRITZ’);

Modification des lignes

update emp
set sal = sal * 1.1 
WHERE sal > 4500

Suppression de lignes

delete from "nom table"
where "valeur dans collone" = 345

Supprimer une table

DROP TABLE "Nom table";

Supprimer une sequance

DROP SEQUENCE seq_frn;

Creation d'une sequance

 CREATE SEQUENCE seq_frn
 INCREMENT BY 2
 START WITH 1
 MAXVALUE 999
 NOCYCLE;

Insertion d'une donnée dans une table

insert into "Table" (Collone, ..., ...)
values (Val1, Val2, ...); #avec ' ' si AlphaNum, sans ' ' si num, null si vide

Trouver le DIRECTORY pour export

select * from dba_directories;


Afficher tous les shéma d'une base

select username from dba_users;

export de shema

expdp user/pwd DUMPFILE="nom_fichier_dump" DIRECTORY="SAV_DIR" SCHEMAS="nom du schema","nom du schema" LOGFILE="fichier_log"

SQL

SELECT

Le SELECT est la commande de base du SQL destinée à extraire des données d’une base ou calculer de nouvelles données à partir d’existantes...


Voici la syntaxe générale d'une commande SELECT :

SELECT [DISTINCT ou ALL] * ou liste de colonnes 
FROM nom de table ou de la vue
[WHERE prédicats] 
[GROUP BY ordre des groupes]
[HAVING condition] 
[ORDER BY ] liste de colonnes


NOTA : dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en minuscule et entre crochets on trouve les parties optionnelles


En fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optionnelles. Clauses de l'ordre SELECT :

SELECT Spécification des colonnes du résultat
FROM Spécification des tables sur lesquelles porte l'ordre
WHERE Filtre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat)
GROUP BY Définition d'un groupe (sous ensemble)
HAVING Filtre portant sur les résultats (conditions de regroupement des lignes)
ORDER BY ri des données du résultat


NOTA : La plupart du temps, la difficulté réside dans la compréhension de la différence entre le filtre WHERE et le filtre HAVING. Disons plus pragmatiquement que le filtre WHERE permet de filtrer les données des tables tandis que le filtre HAVING permet de filtrer les données du résultat...

REMARQUE : pour spécifier une valeur littérale il faut l'entourer de guillemets simples. Un premier exemple basique :

 Exemple 1
SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'
CLI_NOM CLI_PRENOM
DUPONT Alain
MARTIN Marc
BOUVIER Alain
DUBOIS Paul
DREYFUS Jean
FAURE Alain
PAUL Marcel
DUVAL Arsène
PHILIPPE André
CHABAUD Daniel
BAILLY Jean-François
...

Permet de trouver les noms et prénoms des clients dont le titre est ‘M.’ (monsieur).

NOTA : comme tous les paramètres à prendre sous forme de littéraux doivent être exprimés entourés d'apostrophes (simple côtes), il faut dédoubler un tel caractère s'il s'avère présent dans la chaîne utilisé.

Requête pour Easyvista

Info de status EZV

Status ID				        Status FR 
____________________________________________________________________
31                                              Retour Atelier
32						En quarantaine 
39						Dépollué par 3E
30						En maintenance
33						Manquant
34						Restitué au fournisseur
38						Post Migration
29						En commande
28						En activité - Nagios Préprod
22						En activité - Non Supervisé
26						Inactif
1						En panne
2						En réparation
3						Prêté
4 						En stock
5						Mis à jour
6						Retourné
7						Supprimé
8						En activité
9						Volé
10						En cours de transfert
11						A vérifier
12						A restituer
13						Doublon
15						Détruit
17						En construction

Changement status en supprimé

Update am_asset set status_id = 7 where asset_tag='N°CI';

Changement nature

Update am_asset set e_nature_obj_prod='NATURE', last_update=sysdate where asset_tag='N°CI'

Changement nature et arg1

Update am_asset set e_nature_obj_prod='NATURE', e_supervision_1='VALEUR' where asset_tag='N°CI';

Changement ARG (e_supervision_X champ argEZV)

Update am_asset set e_supervision_2='VALEUR', e_supervision_3='VALEUR', last_update=sysdate where asset_tag='N°CI';

Changement état en téléexploite

Update am_asset set e_teleexploit='1', last_update=sysdate where asset_tag='N°CI';

Ajout Commentaire par CI

update am_asset set COMMENT_ASSET='centreon', last_update=sysdate where asset_tag='N°CI'


Changement status

Update am_asset set status_id = 'Status ID', last_update=sysdate where asset_tag='N°CI';


Changement Status avec Commentaire

Update am_asset set status_id = 26, COMMENT_ASSET='Commantaire', last_update=sysdate where asset_tag='N°CI';

Copier le champ CI dans Nom HW si commence par S mais pas par SW

update am_asset set network_identifier=asset_tag where asset_tag like 'S%' and  asset_tag not like 'SW%';

Changement type colonne:

alter table am_asset modify (ban_1 varchar2(20), ban_2 varchar2(20), ban_3 varchar2(20), ban_4 varchar2(20), ban_5 varchar2(20), ban_6 varchar2(20), ban_7 varchar2(20), ban_8 varchar2(20));

Select des hard et host

select hard.asset_tag HARDWARE, hard.status_id STATUS_HARD, 
toto.child_ci_id , serveur.network_identifier SERVEUR, 
serveur.status_id STATUS_SERVER from am_asset hard left outer join configuration_item_link toto 
on hard.asset_id=toto.parent_ci_id left outer join am_asset serveur on toto.child_ci_id=serveur.asset_id where hard.status_id not like '7' 
and hard.asset_tag like 'HW%' order by 1 desc ;


Ou

select hard.asset_tag HARDWARE, serveur.network_identifier SERVEUR from am_asset hard left outer join configuration_item_link toto 
on hard.asset_id=toto.parent_ci_id left outer join am_asset serveur on toto.child_ci_id=serveur.asset_id where hard.asset_tag like 'HW%' 
or hard.asset_tag like 'AN%' order by 1 desc ;

Select Hardware par position et status

select A.ASSET_TAG, A.E_NUM_BAIE, A.E_NUM_BAIE_U, S.STATUS_FR 
from am_asset A left outer join AM_STATUS S on A.status_ID=S.STATUS_ID 
where A.asset_tag like 'HW%' order by A.E_NUM_BAIE desc;

Select Application Installé sur quel Host

select A.asset_tag CI, A.network_identifier APPLICATION_INSTALLE, serveur.network_identifier SERVEUR  
from am_asset A left outer join configuration_item_link toto
on A.asset_id=toto.CHILD_CI_ID left outer join am_asset serveur
on toto.PARENT_CI_ID=serveur.asset_id 
where A.network_identifier like 'JBOSS%' 
and A.ASSET_TAG like 'LG10%'
and serveur.ASSET_TAG like 'LG50%'
and serveur.STATUS_ID not like'7';