Pràctica ASI-C6-ASGBD: Sessió remota d'Oracle

De wikijoan
Salta a la navegació Salta a la cerca

Objectius

  • Unitat Didàctica: UD 1. Introducció a Oracle

Introducció d'una sessió a Oracle. Connexió remota via SSH en el servidor d'Oracle. Fer login a Oracle localment.

Els alumnes, que encara no tenen instal.lat l'Oracle com a client o com a servidor, sempre podran practicar connectant-se al servidor 192.168.0.10 via SSH, i executar el sqlplus en la màquina remota. D'aquesta manera els alumnes sempre podran treballar contra el servidor Oracle.

Desenvolupament

1. Logar-se via SSH al servidor Oracle. Des de LINKAT amb un client de ssh que s'executa en la consola. Des de Windows, pots utilitzar el putty.

$ ssh asi2aXX@192.168.0.10
login: asi2aXX
password: asi

$ . oraenv

Nota: la comanda oraenv és per inicialitzar les variables d'entorn per poder-te connectar correctament a la instància d'Oracle, i per tal que el PATH trobi la ruta dels fitxers executables. En el cas dels alumnes no cal doncs el script que s'executa en l'inici de sessió ja ho fa automàticament.

La instància a la que et connectaràs és BBDD, la única instància oracle que hi ha en el servidor 192.168.0.10

2. iniciar en el servidor Oracle una sessió de consola:

$ sqlplus asi2aXX
password: asi
SQL>

Ara estàs logat dins el teus esquema. Pots comprovar els objectes que et pertanyen fent:

SQL> SELECT * FROM TAB;

3. Crear el script vist a classe. Per crear-lo necessites un editor: vi, joe o un altre que estigui instal.lat en el servidor.

4. executar el script vist a classe. Taules ALUMNE, ASSIGNATURA, MATRICULA.

SQL> @/var/www/alumnes/asi2aXX/script.sql

5. modificar aquest script.

6. implementar un script que segueixi el mateix esquema que el vist anteriorment, basat en les taules AULA i ORDINADOR. La idea és tenir ben controlats on estan els ordinadors dins de les aules. És una relació 1:M: una alula té molts ordinadors, un ordinador pertany a una sola aula. Com implementes aquesta relació 1:M?

7. Fés una llista dels diferents tipus de dades que pots utilitzar a l'hora de crear una taula. Fés servir una guia de SQL d'Oracle.

8. El script ha de respondre les següents preguntes:

  • Digues els ordinadors i la MAC respectiva que hi ha a l'A46.
  • En quines aules hi ha ordinadors amb disc dur superior a 320 GB?
  • Classifica les aules ordenades per la capacitat total de la suma dels seus discs durs.
  • Esborra l'aula 46, i tot el seu contingut (ordinadors). En quin ordre ho has de fer per no tenir problemes d'integritat referencial? Un altre solució és implementar l'ordre ON DELETE CASCADE. La idea és que quan esborres l'A46 estaràs esborrant automàticament tots els ordinadors que hi pertanyen. Implementa-ho.

Consells per treballar

  • Quan treballis en el servidor necessites un editor. Per exemple, tens disponibles el joe i el vim. Si utilitzes el joe, les principals tecles de funció són: Ctrl-H (ajuda) i Ctrl-KX (sortir i guardar)
  • Una altra possibilitat és treballar el script en el teu ordinador, i enviar el script al servidor. Per fer-ho pots utilitzar un client de FTP (servidor: 192.168.0.10, login: asi2aXX, password: asi), o bé utilitzar la utilitat pscp.
  • pscp.exe és una utilitat per copiar fitxers via SSH, i és del mateix paquet que el putty. Per descarregar el binari, buscar en el google putty download. Si vols copiar un fitxer del servidor remot a l'ordinador local:
C:\> pscp.exe asi2aXX@192.168.0.10:script.sql C:\script.sql
  • El sqlplus té moltes opcions per formatar la sortida. A vegades la sortida per pantalla no és molt aclaridora (columnes que passen a la línia següent,...). sqlplus proporciona comandes per formatar la sortida. Concretament la comanda COLUMN:

http://www.adp-gmbh.ch/ora/sqlplus/column.html

SQL> help index

Enter Help [topic] for help.

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET                      XQUERY
 CONNECT       PASSWORD     SHOW
SQL> help column

 COLUMN
 ------

 Specifies display attributes for a given column, such as:
     - text for the column heading
     - alignment for the column heading
     - format for NUMBER data
     - wrapping of column data
 Also lists the current display attributes for a single column
 or all columns.

 COL[UMN] [{column | expr} [option ...] ]

 where option represents one of the following clauses:
     ALI[AS] alias
     CLE[AR]
     ENTMAP {ON|OFF}
     FOLD_A[FTER]
     FOLD_B[EFORE]
     FOR[MAT] format
     HEA[DING] text
     JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
     LIKE {expr | alias}
     NEWL[INE]
     NEW_V[ALUE] variable
     NOPRI[NT] | PRI[NT]
     NUL[L] text
     OLD_V[ALUE] variable
     ON|OFF
     WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

Compara aquestes dues sortides:

SELECT member FITXER,V$LOG.STATUS FROM V$LOG, V$LOGFILE WHERE V$LOG.GROUP#=V$LOGFILE.GROUP# AND V$LOG.STATUS='CURRENT';

FITXER
--------------------------------------------------------------------------------
STATUS
----------------------------------------------------------------
/u01/app/oracle/oradata/BBDD/redo03.log
CURRENT

column FITXER format a50
column STATUS format a10

SELECT member FITXER,V$LOG.STATUS FROM V$LOG, V$LOGFILE WHERE V$LOG.GROUP#=V$LOGFILE.GROUP# AND V$LOG.STATUS='CURRENT';

FITXER                                    STATUS
----------------------------------------  ---------
/u01/app/oracle/oradata/BBDD/redo03.log   CURRENT

Entrega

  • entregar els scripts SQL que has generat, indicant clarament el nom i el número de classe. Exercici individual o per parelles. En el nom del fitxer s'haurà d'identificar clarament el número de pràctica i el número de classe. A dins del script, amb un comentari (REM) ficaràs el número de classe i el nom. Si has d'entregar diferents fitxers m'envies un sol fitxer comprimit (rar, zip).
  • entregar al Moodle: http://192.168.0.15/moodle

Recursos

Guia de SQL d'Oracle

El script vist a classe és:

DROP TABLE MATRICULA;
DROP TABLE ALUMNE;
DROP TABLE ASSIGNATURA;

CREATE TABLE ALUMNE (
id NUMBER(2) PRIMARY KEY,
nom VARCHAR2(15) NOT NULL,
cognom1 VARCHAR2(20) NOT NULL,
cognom2 VARCHAR2(20),
data_naix DATE
);

CREATE TABLE ASSIGNATURA (
id NUMBER(2) PRIMARY KEY,
nom VARCHAR2(15)
);

CREATE TABLE MATRICULA (
id_alumne NUMBER(2) REFERENCES ALUMNE,
id_assig NUMBER(2) REFERENCES ASSIGNATURA,
nota NUMBER(2),
PRIMARY KEY(id_alumne,id_assig)
);

INSERT INTO ALUMNE(id, nom, cognom1, cognom2, data_naix) VALUES(1,'Pere','Ramiro','Pou','23/03/1980');
INSERT INTO ALUMNE VALUES(2,'Maria','Guixers','Perelló','11/12/1980');
INSERT INTO ALUMNE VALUES(3,'Rita','Pau','','23/03/1980');
INSERT INTO ALUMNE VALUES(4,'Jordi','Compte',NULL,'23/03/1970');
INSERT INTO ALUMNE(id, cognom1, nom, data_naix) VALUES(5,'Sans','Pau','12/04/1990');

INSERT INTO ASSIGNATURA VALUES (1,'DFSI');
INSERT INTO ASSIGNATURA VALUES (2,'IAIG');

INSERT INTO MATRICULA VALUES(1,1,NULL);
INSERT INTO MATRICULA VALUES(1,2,NULL);
INSERT INTO MATRICULA VALUES(2,1,NULL);
INSERT INTO MATRICULA VALUES(2,2,NULL);

SELECT * FROM ALUMNE;
SELECT * FROM ALUMNE WHERE cognom2 IS NULL;
SELECT * FROM ALUMNE WHERE cognom2 = '';
SELECT * FROM ALUMNE WHERE cognom2 <> '';
SELECT * FROM ALUMNE WHERE data_naix > '01/01/1980';
SELECT * FROM ALUMNE WHERE cognom1 LIKE '%a%';

SELECT * FROM ASSIGNATURA;

SELECT A.nom,A.cognom1, ASSIG.nom FROM ALUMNE A, ASSIGNATURA ASSIG, MATRICULA M WHERE A.id=M.id_alumne AND ASSIG.id=M.id_assig;

UPDATE ALUMNE SET cognom2='Perello' WHERE cognom2='Perelló';
UPDATE MATRICULA SET nota=6 WHERE id_alumne=2 and id_assig=2;

DELETE FROM ALUMNE WHERE id=5;
SELECT * FROM ALUMNE;

DELETE FROM MATRICULA;
DELETE FROM ASSIGNATURA;
DELETE FROM ALUMNE;

SELECT * FROM ALUMNE;

Durarda

2 hores



creat per Joan Quintana Compte, setembre 2009