Instalace DB schématu (oracle)

Neurčeno » Instalace » Instalace DB schématu (oracle)

1. založení TABLESPACE
CREATE TABLESPACE STUDENT_DATA DATAFILE '/opt/oracle/oradata/studtest/student_data.dbf'
SIZE 1024M AUTOEXTEND ON NEXT 128M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE STUDENT_LOG DATAFILE '/opt/oracle/oradata/studtest/student_log.dbf'
SIZE 512M AUTOEXTEND ON NEXT 128M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE STUDENT_MVIEW DATAFILE '/opt/oracle/oradata/studtest/student_mview.dbf'
SIZE 100M AUTOEXTEND ON NEXT 20M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
2. založení uživatele STDOWNER
CREATE USER STDOWNER
IDENTIFIED BY ___heslo___
DEFAULT TABLESPACE STUDENT_DATA
QUOTA UNLIMITED ON STUDENT_DATA;

GRANT CONNECT TO STDOWNER WITH ADMIN OPTION;
GRANT RESOURCE TO STDOWNER WITH ADMIN OPTION;

GRANT CREATE USER TO STDOWNER WITH ADMIN OPTION;
GRANT ALTER USER TO STDOWNER WITH ADMIN OPTION;
GRANT DROP USER TO STDOWNER WITH ADMIN OPTION;

GRANT CREATE PUBLIC SYNONYM TO STDOWNER WITH ADMIN OPTION;
GRANT DROP PUBLIC SYNONYM TO STDOWNER WITH ADMIN OPTION;

GRANT CREATE ANY JOB TO STDOWNER;
GRANT SELECT ANY DICTIONARY TO STDOWNER;

GRANT CREATE VIEW TO STDOWNER;

GRANT CREATE PUBLIC SYNONYM TO STDOWNER;
GRANT DROP PUBLIC SYNONYM TO STDOWNER;
GRANT CREATE ANY SYNONYM TO stdowner;
GRANT DROP ANY SYNONYM TO stdowner;

GRANT UNLIMITED TABLESPACE TO STDOWNER;

GRANT CREATE ROLE TO STDOWNER;

GRANT SELECT_CATALOG_ROLE TO STDOWNER WITH ADMIN OPTION;
3. založení uživatele WEBAUTH (log)
CREATE USER WEBAUTH
IDENTIFIED BY ___heslo___
DEFAULT TABLESPACE STUDENT_LOG
QUOTA UNLIMITED ON STUDENT_LOG;

GRANT CONNECT TO WEBAUTH;
GRANT CREATE TABLE TO webauth;
GRANT CREATE SEQUENCE TO webauth;
GRANT CREATE PROCEDURE TO webauth;
GRANT CREATE PUBLIC SYNONYM TO webauth;
4. založení logovacího mechanismu, pod uživatelem WEBAUTH spustit:
  • laser_table.sql
  • lase_proc.sql
  • laserp_grant.sql
5. programem REPLIKY založit všechny tabulky a pohledy (www.erudio.cz/student/ftp/tabulky)
6. nastavit (naplnit) parametry instalace, tabulka PARINST
INSERT INTO parinst (pitag, pimulti) VALUES ('11000', 'T');
7. naplnit všechny číselníky *.db (www.erudio.cz/student/ftp/tabulky)
8. nastavit generátory, založit triggery (programem REPLIKY, nebo DBCREATE)
9. připravit tabulku OSOBAWEB (naplnit, založit triggery)
  • naplnit
INSERT INTO osobaweb (oident, oidos, ologin, oprijmeni, ojmeno, onazev, otitul, otitulza, omail, ourl)
SELECT oident, oidos, ologin, oprijmeni, ojmeno, NULL, otitul, otitulza, omail, ourl
FROM osoba;

INSERT INTO osobaweb (oident, oidos, ologin, oprijmeni, ojmeno, onazev, otitul, otitulza, omail, ourl)
SELECT oident, oidos, ologin, oprijmeni, ojmeno, onazev, otitul, otitulza, omail, ourl
FROM osobauziv;
  • spustit trg_osoba_web.sql (založení triggerů)
10. založit programem SPRÁVCE
  • role pro web uživatele (php - Anonym, php - Ucitel, php - Student)
  • založit web uživatele WANO, WSTU, WUCI a přidělit jim příslušné role
  • programem SPRÁVCE nagrantovat vše