This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
db:oracledb [2024/01/16 11:06] tomas [Oracle soucasti a ovladani Oracle] |
db:oracledb [2024/01/16 11:14] (current) tomas |
||
---|---|---|---|
Line 83: | Line 83: | ||
DBMS_SESSIO, | DBMS_SESSIO, | ||
- | ===[[https:// | + | ===Systemove procesy=== |
+ | [[https:// | ||
ps -ef | grep smon ... ([[http:// | ps -ef | grep smon ... ([[http:// | ||
ora_mman_INSTANCE ... ([[http:// | ora_mman_INSTANCE ... ([[http:// | ||
Line 152: | Line 154: | ||
- | <a name=" | ||
===SQL Plus - pristup do Oracle=== | ===SQL Plus - pristup do Oracle=== | ||
sqlplus username/ | sqlplus username/ | ||
Line 171: | Line 172: | ||
CONNECT / | CONNECT / | ||
/D | /D | ||
+ | |||
===Instance - proces Oracle, ktery se muze pripoji k nejake databazi a pracuje nad ni=== | ===Instance - proces Oracle, ktery se muze pripoji k nejake databazi a pracuje nad ni=== | ||
STARTUP ... startovani instance, hleda SPFILE v $ORACLE_HOME/ | STARTUP ... startovani instance, hleda SPFILE v $ORACLE_HOME/ | ||
Line 208: | Line 210: | ||
SELECT * FROM V$SGA_CURRENT_RESIZE_OPS; | SELECT * FROM V$SGA_CURRENT_RESIZE_OPS; | ||
- | ===Spousteni SQL, [[http:// | + | ===Spousteni SQL, PL/SQL skriptu a shell prikazu=== |
+ | [[[[http:// | ||
@/ | @/ | ||
!ls ... spusti prikaz ls z shellu\\ | !ls ... spusti prikaz ls z shellu\\ | ||
Line 234: | Line 238: | ||
SELECT * FORM [DBA_SERVICES, | SELECT * FORM [DBA_SERVICES, | ||
- | <a name=" | ||
====DATABAZE==== | ====DATABAZE==== | ||
Line 267: | Line 270: | ||
DATAFILE '/ | DATAFILE '/ | ||
SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;</ | SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;</ | ||
- | <b>Jednodussi vytvoreni DB:</ | + | |
+ | ==Jednodussi vytvoreni DB== | ||
CREATE DATABASE mydb2; ... Vytvori databazi mydb2, s TS: SYSTEM, SYSAUX v DB_CREATE_FILE_DEST a REDO + Control files tolikrat, kolik je definovano DB_CREATE_ONLINE_DEST_n v tet olokaci. Vse velikost 100MB. Pokud je UNDO_MANAGEMENT povolen, vytvori se i UNDO TS o velikosti 10MB.\\ | CREATE DATABASE mydb2; ... Vytvori databazi mydb2, s TS: SYSTEM, SYSAUX v DB_CREATE_FILE_DEST a REDO + Control files tolikrat, kolik je definovano DB_CREATE_ONLINE_DEST_n v tet olokaci. Vse velikost 100MB. Pokud je UNDO_MANAGEMENT povolen, vytvori se i UNDO TS o velikosti 10MB.\\ | ||
Line 276: | Line 280: | ||
SELECT * FROM GLOBAL_NAME ... ukaze jmeno databaze\\ | SELECT * FROM GLOBAL_NAME ... ukaze jmeno databaze\\ | ||
select NAME from V$DATAFILE; ... seznam datovych souboru\\ | select NAME from V$DATAFILE; ... seznam datovych souboru\\ | ||
- | <b>Parametry pro CREATE DATABASE</ | + | |
+ | ==Parametry pro CREATE DATABASE== | ||
RESETLOGS ... Oracle vytvori sam soubory pro REDO\\ | RESETLOGS ... Oracle vytvori sam soubory pro REDO\\ | ||
NORESETLOGS ... REDO soubory se musi specifikovat pri vytvareni DB pomoci klicoveho slova LOGFILE\\ | NORESETLOGS ... REDO soubory se musi specifikovat pri vytvareni DB pomoci klicoveho slova LOGFILE\\ | ||
Line 312: | Line 317: | ||
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4=' | ALTER SYSTEM SET LOG_ARCHIVE_DEST_4=' | ||
- | ALTER SYSTEM SET LOG_ARCHIVE_DEST_$='' | + | %%ALTER SYSTEM SET LOG_ARCHIVE_DEST_$='' |
ALTER SYSTEM SET DB_BLOCK_CHECKSUM TRUE; ... dybamicky prepne prepinac\\ | ALTER SYSTEM SET DB_BLOCK_CHECKSUM TRUE; ... dybamicky prepne prepinac\\ | ||
SHOW PARAMETERS ... ukaze aktualni parametry\\ | SHOW PARAMETERS ... ukaze aktualni parametry\\ | ||
Line 373: | Line 378: | ||
/ | / | ||
- | ===[[http:// | + | ===Tablespace - Tabulkove prostory=== |
+ | [[http:// | ||
Tabulky maji ulozene data v tabulkovych prostorech (Tablespace - TS - TBS) v datovych segmentech. Datovy segment je mnozina extentu (rozsahu), ktere mohou byt v roznych datovych souborech na ruznych mistech. Datovy extent je souvisly usek datovych bloku. | Tabulky maji ulozene data v tabulkovych prostorech (Tablespace - TS - TBS) v datovych segmentech. Datovy segment je mnozina extentu (rozsahu), ktere mohou byt v roznych datovych souborech na ruznych mistech. Datovy extent je souvisly usek datovych bloku. | ||
Line 409: | Line 415: | ||
alter tablespace tblspc TEMPORARY; ... zmeni tabulkovy prostor na docasny tabulkovy prostor\\ | alter tablespace tblspc TEMPORARY; ... zmeni tabulkovy prostor na docasny tabulkovy prostor\\ | ||
alter tablespace tblspc TABLESPACE GROUP tblgroup1; ... prida tabulkovy prostor do skupiny tabulkovych prostoru\\ | alter tablespace tblspc TABLESPACE GROUP tblgroup1; ... prida tabulkovy prostor do skupiny tabulkovych prostoru\\ | ||
- | alter tablespace tblspc TABLESPACE GROUP ''; | + | %%alter tablespace tblspc TABLESPACE GROUP '' |
alter tablespace tblspc RENAME TO users; ... prejmenuje tabulkovy prostor (COMPACTIBLE parametr musi byt >= 10\\ | alter tablespace tblspc RENAME TO users; ... prejmenuje tabulkovy prostor (COMPACTIBLE parametr musi byt >= 10\\ | ||
alter tablespace tblspc RENAME DATAFILE '/ | alter tablespace tblspc RENAME DATAFILE '/ | ||
Line 526: | Line 532: | ||
ALTER SYSTEM SET UNDO_RETENTION=2400; | ALTER SYSTEM SET UNDO_RETENTION=2400; | ||
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2; | ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2; | ||
- | ALTER SYSTEM SET UNDO_TABLESPACE=''; | + | %%ALTER SYSTEM SET UNDO_TABLESPACE='' |
ALTER TABLESPACE undotbs2 RETENTION NOGUARANTEE; | ALTER TABLESPACE undotbs2 RETENTION NOGUARANTEE; | ||
CREATE UNDO TABLESPACE undotbs2 RETENTION GUARANTEE; vytvori Undo TS undotbs2, ktery garantuje undo proceduru pro datove operace. Pokud opearce nemuze jiz zapisovat do undo, tak se stornuje\\ | CREATE UNDO TABLESPACE undotbs2 RETENTION GUARANTEE; vytvori Undo TS undotbs2, ktery garantuje undo proceduru pro datove operace. Pokud opearce nemuze jiz zapisovat do undo, tak se stornuje\\ | ||
Line 540: | Line 546: | ||
select TO_CHAR(BEGIN_TIME ' | select TO_CHAR(BEGIN_TIME ' | ||
UNDO_POOL ... kvoty pro UNDO prostor pro jednotlive uzivatele\\ | UNDO_POOL ... kvoty pro UNDO prostor pro jednotlive uzivatele\\ | ||
- | <b>odhad velikosti Undo TS</ | + | |
+ | ==odhad velikosti Undo TS== | ||
< | < | ||
tid NUMBER; | tid NUMBER; | ||
Line 555: | Line 562: | ||
Vystup lze videt v DBA_ADVISOR_*, | Vystup lze videt v DBA_ADVISOR_*, | ||
- | <b>Balicek Flashback</ | + | |
+ | ==Balicek Flashback== | ||
DBMS_FLASHBACK ... balicek na vraceni zmen pri omylem spustenych prikazech ci pri vyvoji\\ | DBMS_FLASHBACK ... balicek na vraceni zmen pri omylem spustenych prikazech ci pri vyvoji\\ | ||
FLASHBACK TABLE ... umozni uzivatelum obnovit tabulku do predchoziho casoveho bodu\\ | FLASHBACK TABLE ... umozni uzivatelum obnovit tabulku do predchoziho casoveho bodu\\ | ||
- | <b>Prechod na automaticky undo management</ | + | |
+ | ==Prechod na automaticky undo management== | ||
< | < | ||
utbsiz_in_MB NUMBER; | utbsiz_in_MB NUMBER; | ||
Line 564: | Line 573: | ||
utbsiz_in_BM ;= DBMS_UNDO_ADV.RBU_MIGRATION; | utbsiz_in_BM ;= DBMS_UNDO_ADV.RBU_MIGRATION; | ||
end;</ | end;</ | ||
- | + | ||
- | <a name=" | + | |
====Objekty v databazich==== | ====Objekty v databazich==== | ||
Line 590: | Line 598: | ||
===Tabulky - Table=== | ===Tabulky - Table=== | ||
+ | SELECT table_name FROM user_tables; | ||
CREATE TABLE test (name VARCHAR(20)); | CREATE TABLE test (name VARCHAR(20)); | ||
create table sales (invoice_no NUMBER, s_y INT NOT NULL, s_m INT NOT NULL, s_d INT NOT NULL) PARTITION BY RANGE (Ss_y, _m, s_d) (partition jan04 values less than (2004, 2, 1), partition feb04 values less than (2004, 3, 1)); ... vytvori tabulku, ktera je spojenim vice tabulek. Pouziva se napriklad ve skladisti (warehouse) dat\\ | create table sales (invoice_no NUMBER, s_y INT NOT NULL, s_m INT NOT NULL, s_d INT NOT NULL) PARTITION BY RANGE (Ss_y, _m, s_d) (partition jan04 values less than (2004, 2, 1), partition feb04 values less than (2004, 3, 1)); ... vytvori tabulku, ktera je spojenim vice tabulek. Pouziva se napriklad ve skladisti (warehouse) dat\\ |