Tomas Lipensky - vedomostni koutek

Trace:

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
db:oracledb [2024/01/16 11:08]
tomas [Konfigurace] 		db:oracledb [2024/01/16 11:14] (current)
tomas
Line 270: Line 270:
       DATAFILE '/mydb/undotbs01.dbf'       DATAFILE '/mydb/undotbs01.dbf'
       SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;</code>       SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;</code>
-<b>Jednodussi vytvoreni DB:</b>\\+ 
 +==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</b>\\+ 
 +==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='LOCATION=/u02/oracle/rbdb1/'MANDATORY,'REOPEN=2' SCOPE=SPFILE ... specifikuje, kam se bude ukladat archiv logu c. 4\\ ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/'MANDATORY,'REOPEN=2' SCOPE=SPFILE ... specifikuje, kam se bude ukladat archiv logu c. 4\\
-ALTER SYSTEM SET LOG_ARCHIVE_DEST_$='' ... smazani parametru\\+%%ALTER SYSTEM SET LOG_ARCHIVE_DEST_$=''%% ... smazani parametru\\
 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://aov.webzdarma.cz/oracle/tablespaces.html|Tablespace - Tabulkove prostory]]===+===Tablespace - Tabulkove prostory=== 
 +[[http://aov.webzdarma.cz/oracle/tablespaces.html|]]\\
  
 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 ''; ... odebere tabulkovy prostor ze skupiny tabulkovych prostoru\\+%%alter tablespace tblspc TABLESPACE GROUP ''%%; ... odebere tabulkovy prostor ze skupiny tabulkovych prostoru\\
 alter tablespace tblspc RENAME TO users; ... prejmenuje tabulkovy prostor (COMPACTIBLE parametr musi byt &gt;= 10\\ alter tablespace tblspc RENAME TO users; ... prejmenuje tabulkovy prostor (COMPACTIBLE parametr musi byt &gt;= 10\\
 alter tablespace tblspc RENAME DATAFILE '/u1/1.dbf' '/u1/2.dbf' TO '/u2/1.dbf' '/u2/2.dbf'; ... prejmenuje Datove soubory v definicich, po te, co jsou fyzicky prejmenovane<. Musi byt v OFFLINE\\ alter tablespace tblspc RENAME DATAFILE '/u1/1.dbf' '/u1/2.dbf' TO '/u2/1.dbf' '/u2/2.dbf'; ... prejmenuje Datove soubory v definicich, po te, co jsou fyzicky prejmenovane<. Musi byt v OFFLINE\\
Line 529: Line 532:
 ALTER SYSTEM SET UNDO_RETENTION=2400; ... zmeni UNDO_RETENTION - min. cas po ktery se uchovavaji data pri zmenach zpusobenych operacemi s daty\\ ALTER SYSTEM SET UNDO_RETENTION=2400; ... zmeni UNDO_RETENTION - min. cas po ktery se uchovavaji data pri zmenach zpusobenych operacemi s daty\\
 ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2; ... zmeni tabulkovy prostor pro UNDO logy\\ ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2; ... zmeni tabulkovy prostor pro UNDO logy\\
-ALTER SYSTEM SET UNDO_TABLESPACE=''; ... Vypne soucasny tabulkovy prostor z uzivani\\+%%ALTER SYSTEM SET UNDO_TABLESPACE=''%%; ... Vypne soucasny tabulkovy prostor z uzivani\\
 ALTER TABLESPACE undotbs2 RETENTION NOGUARANTEE; ... Zmeni garantovani zpetnych logu na vypnuto\\ ALTER TABLESPACE undotbs2 RETENTION NOGUARANTEE; ... Zmeni garantovani zpetnych logu na vypnuto\\
 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 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, TO_CHAR(END_TIME 'MM/DD/YYYY HH24:MI:SS') END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURENCY as 'MAXCON" FROM v$UNDOSTAT WHERE rownum <= 144; ... ukaze statistiky uzivani unda\\ select TO_CHAR(BEGIN_TIME 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, TO_CHAR(END_TIME 'MM/DD/YYYY HH24:MI:SS') END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURENCY as 'MAXCON" FROM v$UNDOSTAT WHERE rownum <= 144; ... ukaze statistiky uzivani unda\\
 UNDO_POOL ... kvoty pro UNDO prostor pro jednotlive uzivatele\\ UNDO_POOL ... kvoty pro UNDO prostor pro jednotlive uzivatele\\
-<b>odhad velikosti Undo TS</b>\\+ 
 +==odhad velikosti Undo TS==
 <code>DECLARE <code>DECLARE
   tid NUMBER;   tid NUMBER;
Line 558: Line 562:
  
 Vystup lze videt v DBA_ADVISOR_*, nebo v Automatic Database Diagnostic Monitor v Enterprise Manager\\. Velikost UndoTS se doporucuje: UndoSpace_bloky = Undo_retention * Undo_blocks_per_second + overhead. Vystup lze videt v DBA_ADVISOR_*, nebo v Automatic Database Diagnostic Monitor v Enterprise Manager\\. Velikost UndoTS se doporucuje: UndoSpace_bloky = Undo_retention * Undo_blocks_per_second + overhead.
-<b>Balicek Flashback</b>\\+ 
 +==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</b>\\+ 
 +==Prechod na automaticky undo management==
 <code>DECLARE <code>DECLARE
   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;</code> end;</code>
- + 
-<a name="objekty|]]+
 ====Objekty v databazich==== ====Objekty v databazich====
  
Line 593: Line 598:
  
 ===Tabulky - Table=== ===Tabulky - Table===
 +SELECT table_name FROM user_tables; ... seznam tabulek\\
 CREATE TABLE test (name VARCHAR(20)); ... vytvori tabulku test s jednim sloupcem\\ CREATE TABLE test (name VARCHAR(20)); ... vytvori tabulku test s jednim sloupcem\\
 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: 2024/01/16 11:08
GNU Free Documentation License 1.3 Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3