Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
db:oracledb [2022/01/26 11:37] 127.0.0.1 external edit |
db:oracledb [2024/01/16 11:14] (current) tomas |
||
|---|---|---|---|
| Line 23: | Line 23: | ||
| [[http:// | [[http:// | ||
| + | ====Oracle soucasti a ovladani Oracle==== | ||
| + | Oracle Enterprise Manager - webova aplikace na management Oracle databazi v prostredi | ||
| - | <a name=" | ||
| - | ====Oracle soucasti a ovladani Oracle==== | ||
| - | < | ||
| - | |||
| - | <a name=" | ||
| ===I/ | ===I/ | ||
| rpm -i oracle-xe-10.2.0.1-1.0.i386.rpm\\ | rpm -i oracle-xe-10.2.0.1-1.0.i386.rpm\\ | ||
| Line 86: | 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 155: | Line 154: | ||
| - | <a name=" | ||
| ===SQL Plus - pristup do Oracle=== | ===SQL Plus - pristup do Oracle=== | ||
| sqlplus username/ | sqlplus username/ | ||
| Line 174: | 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 211: | 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 237: | Line 238: | ||
| SELECT * FORM [DBA_SERVICES, | SELECT * FORM [DBA_SERVICES, | ||
| - | <a name=" | ||
| ====DATABAZE==== | ====DATABAZE==== | ||
| Line 270: | 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 279: | 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 315: | 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 376: | 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 412: | 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 529: | 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 543: | 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 558: | 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 567: | 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 593: | 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\\ | ||
Last modified: 2022/01/26 11:37
Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3