Lisa tabelisse veerg koos foregin key-ga:

alter table ipdb.ports add rootdevice_id number(9) references ipdb.devices(device_id) not null;

Eemalda veerg:

alter table ipdb.ports drop column root_device_id;

Lisa tabel:

CREATE TABLE ipdb.vlan_connections
(
    vlan_connection_id          NUMBER   (9)                     PRIMARY KEY,
    connection1_id                NUMBER   (9)                     REFERENCES ipdb.connections(connection_id),
    connection2_id                NUMBER   (9)                     REFERENCES ipdb.connections(connection_id)
  , creator_id                          NUMBER   (9)                     NOT NULL
  , created                             TIMESTAMP(6) WITH TIME ZONE      DEFAULT SYSDATE NOT NULL
  , modifier_id                        NUMBER   (9)                     
  , modified                            TIMESTAMP(6) WITH TIME ZONE      
);

Muuda veergu:

alter table ipdb.devices modify root_device_id not null;

Lisa index:

CREATE UNIQUE INDEX anto_common_klient_kl_id_idx ON anto_common_klient(kl_id);
CREATE INDEX anto_common_klient_kl_nimi_idx ON anto_common_klient(kl_nimi);

Lisa kasutaja:

CREATE USER mingi_username IDENTIFIED BY mingi_password DEFAULT tablespace users TEMPORARY tablespace temp;
GRANT CONNECT, resource TO mingi_username;
-- anna 6igus tabelit kasutada (prefix "ipdb" n2itab kasutajat, kellele see tabel kuulub)
GRANT SELECT, UPDATE ON ipdb.ip_networks TO mingi_username;
 
-- v6ta 6igused 2ra
REVOKE ALL ON ipdb.ip_networks FROM mingi_username;
 
-- anna 6igus package-s olevaid funktsioone kasutada
GRANT EXECUTE ON ipdb_ip TO adsldb;
 
-- p2ringu tegemine (NB! tabelile alati vaja omaniku prefix lisada)
SELECT * FROM ipdb.ip_networks;

Materialized view'de tegemine:

CREATE MATERIALIZED VIEW "IPDB"."ANTO_COMMON_KLIENT"
NOCACHE
NOLOGGING
REFRESH COMPLETE
START WITH SYSDATE
NEXT TRUNC(SYSDATE)+1+7/24   -- uuendatakse iga p2ev kell 07:00
WITH ROWID
AS
SELECT * FROM COMMON.KLIENT@ANTODB.CC.ELION.EE;

Trigger:

CREATE OR REPLACE TRIGGER ti_device_addports
AFTER INSERT ON devices
FOR EACH ROW
DECLARE
BEGIN
  IPDB_MODEL_PORTS.NEW_DEVICE(:NEW.device_id, :NEW.device_model_id, :NEW.creator_id);
END;

Custum exeption:

raise_application_error(-20002, 'Sloti tüüp ei kata seadme tüüpi');

Leiab mingi alamseadme ülima parenti. Näiteks kui on vaja teada millises reaalses kastis asub võrgukaart. Võrkari parentiks on näiteks emaplaat, aga meie tahame teada hoopis kasti ennast ehk siis emaplaadi parentit.

                SELECT
                    DISTINCT CONNECT_BY_ROOT device_id
                FROM
                    devices
                WHERE
                    device_id = :device_id
                START WITH parent_device_id IS NULL
                CONNECT BY parent_device_id = PRIOR device_id

Väljastab kõik antud tüübi alamtüübid. Kasutus: mis tüüpidega seadmed sobivad antud slotti.

 SELECT type_id FROM types 
   START WITH type_id = :TYPE 
   CONNECT BY parent_id = PRIOR type_id;

Väljastab kõik tüübid, millega seade ühilduv on, s.t. mis tüüpi slottidesse saab seadet panna. Mudeli järgi:

SELECT type_id
  FROM types
  START WITH type_id IN (
    SELECT type_id FROM device_model_types WHERE device_model_id = :model_id)
  CONNECT BY PRIOR parent_id = type_id;

Seadme järgi:

SELECT type_id
  FROM types
  START WITH type_id IN (
    SELECT type_id 
      FROM devices INNER JOIN device_model_types USING (device_model_id) 
      WHERE device_id = :device_id
    )
  CONNECT BY PRIOR parent_id = type_id;

Kui mingis skriptis UTF-8 stringid ei taha läbi tulla, siis vaja selline variable paika panna:

export NLS_LANG=.AL32UTF8

SELECT REGEXP_REPLACE('M123L04-12-05', '(.*)-(.*)-(.*)-(.*)', '\1-Lxx-\3-\4') FROM dual;
 
SELECT SUBSTR('M123-L04-12-05', REGEXP_INSTR('M123-L04-12-05', '-L',1, 1, 0, 'i')) RESULT FROM dual;
 
SELECT instr('ABCD-L01-12','-') FROM dual; -- leiab mis positsioonil tekstis asub ESIMENE sidekriips
SELECT instr('ABCD-L01-12','-',1,2) FROM dual; -- leiab mis positsioonil tekstis asub TEINE sidekriips