CHEATSHEET
Administracja bazami danych Oracle I
www.jsystems.pl
Start i wyłączanie instancji
Shutdown immediate
Wyłączenie instancji jednocześnie
odcinając wszystkie sesje i wycofując
wszystkie transakcje.
Shutdown normal lub shutdown
Wyłączenie ale z oczekiwaniem aż
wszyscy użytkownicy się rozłączą. Nie
pozwala na łączenie się nowym
użytkownikom.
Shutdown abort
Wyłączenie niemal jakbyś wyciągną
wtyczkę z kontaktu.
Shutdown transactional
Wyłączenie instancji, czeka na
zakończenie wszystkich rozpoczętych
transakcji.
Startup lub startup open
Uruchomienie instancji do trybu open
Startup nomount
Uruchomienie instancji do trybu
nomount
Startup mount
Uruchomienie instancji do trybu
mount
Alter database mount
Przejście do trybu mount z trybu
nomount
Alter database open
Przeście do trybu open z trybu mount
Startup pfile='
Uruchomienie instancji z użyciem
pliku parametrów wskazanego w
ścieżce.
1/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
SGA i architektura
Select * from v$sgainfo;
Informacje o ustawieniach wielkości
buforów.
Alter system set sga_max_size=600M; Ustawienie wielkości maksymalnej
sga.
Alter system set sga_target=550M;
Ustawienie docelowej wielkości SGA
przy automtycznym strojeniu
wielkości buforów.
Alter system set db_cache_size=10M; Ustawienie wielkości bufora db dla
danej instancji i po restarcie.
Alter system set db_cache_size=10M
scope=spfile;
Ustawienie wielkości bufora db ze
skutkiem po restarcie.
Alter system set java_pool_size=10M
scope=memory;
Ustawienie wielkości bufora javowego
ze skutkiem tylko dla ktualnej
instancji.
Alter system set
db_keep_cache_size=1M;
Włączenie dodatkowego podbufora
KEEP do bufora DB.
alter table employees
storage(buffer_pool keep);
Konfiguracja tabeli w taki sposób by
po wczytaniu z dysku lądowała w
podbuforze KEEP.
alter table employees
storage(buffer_pool default);
Przywrócenie normalnej konfiguracji
w.w.
Create pfile='
' from spfile;
Eksport spfile do pfile wskazanego w
ścieżce.
Create spfile from pfile='c:\plik.txt';
Nadpisanie spfile parametrami z pfile
wskazanego w ścieżce
Select * from v$controlfile;
Położenie plików kontrolnych
Select * from v$datafile;
Położenie plików danych
Select * from v$tempfile;
Położenie plików tablespace'a
tymczasowego.
Select * from v$parameter where
name='background_dump_dest';
Położenie alert logu
Select * from logfile;
Położenie plików dziennika powtórzeń
2/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Podstawy flashback
Select * from employees as of
timestamp to_timestamp('22/03/2012
13:14:00','dd/mm/yyyy hh24:mi:ss');
Zawartość tabeli z określonego
momentu.
Select * from v$parameter where
name='undo_retention';
Sprawdzenie czasu przechowywania
danych w przestrzeni UNDO
Alter system set undo_retention=3600; Ustawienie czasu przechowywania
danych w przestrzeni undo (w
sekundach)
Alter table employees enable row
movement;
Przygotowanie tabeli do przywrócenia
do punktu w czasie. Wykonuje się to
tylko raz dla każdej przywracanej
tabeli.
Flashback table employees to
timestamp to_timestamp('22/03/2012
13:14:00','dd/mm/yyyy hh24:mi:ss');
Przywracanie tabeli do punktu w
czasie.
Select * from user_recyclebin;
Usunięte obiekty użytkownika.
Select * from dba_recyclebin;
Wszystkie usunięte obiekty w całej
bazie.
Flashback table employees to before
drop;
Przywrócenie tabeli po usunięciu.
3/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Użytkownicy i uprawnienia
Create user rafal identified by pa$$w0rd Tworzenie użytkownika rafal z hasłem
pa$$w0rd
Grant connect, resource to rafal
Nadanie podstawowych ról
użytkownikowi rafal, tak by mógł łączyć
się do bazy i tworzyć obiekty w swoim
schemacie.
Alter user rafal account lock;
Blokowanie konta użytkownika rafal
Alter user rafal account unlock;
Odblokowanie konta użytkownika rafal
Alter user rafal quota 10M on dane;
Ustawienie maksymalnej przestrzeni w
tablespace dane którą może zająć
użytkownik rafal.
Grant select on hr.employees to rafal;
grant update , delete on hr.employees to
rafal;
Revoke select on hr.employees from
rafal;
Przykłady nadawania i odbierania
uprawnień obiektowych
Grant create materialized view to hr;
grant select any dictionary to hr;
grant create any session to hr;
Przykłady nadawania i odbierania
uprawnień systemowych
Select * from all_directories;
create directory importy as
'c:\dane\importy';
grant read, write on directory importy to
hr;
Sprawdzenie dostępnych z poziomu
Oracle katalogów, stworzenie aliasu dla
kolejnego i nadanie uprawnień zapisu i
odczytu z niego użytkownikowi hr;
Select * from user_ts_quotas;
Sprawdzenie dostępnych limitów
przestrzeni z poziomu użytkownika
Select * from dba_ts_quotas;
Sprawdzenie dostępnych limitów
przestrzeni dla wszystkich użytkowników
z poziomu admina.
4/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Role
Create role ksiegowa;
Stworzenie roli księgowa
Drop role ksiegowa;
Kasowanie roli księgowa
Create role uber_ksiegowa identified by
tajne_haslo;
Stworzenie roli uber_księgowa z hasłem.
Grant select,update, delete on
hr.employees to ksiegowa;
Nadanie uprawnień do roli księgowa
Grant ksiegowa to rafal;
Nadanie roli użytkownikowi
Grant uber_ksiegowa to rafal;
Nadanie roli z hasłem użytkownikowi
Revoke ksiegowa from rafal;
Odebranie roli użytkownikowi
Alter user rafal default role all except
uber_ksiegowa;
Ustawienie wszystkich ról jako
domyślnych poza rolą uber_księgowa
która ma założone hasło. Jeśli tego nie
zrobimy , użytkownik będzie miał od
razu wszystkie uprawnienia wynikające i
nie będzie musiał włączać tej roli z
podaniem hasła.
Alter user rafal default role none except
connect, resource
Ustawienie takie by domyślnie włączane
tylko role connect i resource a pozostałe
role użytkownik musiał sobie włączyć.
Set role uber_ksiegowa identified by
tajne_haslo;
Właczanie roli z hasłem przez
użytkownika.
Set role nazwa_roli;
Włączanie roli bez hasła przez
użytkownika.
Select * from v$session;
Wszystkie podłączone sesje. Tutaj
szukamy wartości sid i serial potrzebne
do wycinania sesji.
Alter system kill session '10,43';
Killowanie sesji. Użytkownik dostanie
komunikat o zabiciu jego sesji. Pierwsza
wartość to sid, a druga to serial dla sesji.
Alter system disconnect session '10,43'
immediate;
Wycinanie sesji natychmiast. Pierwsza
wartość to sid, a druga to serial dla sesji.
Alter system disconnect session '10,43'
post_transaction;
Wycinanie sesji po zakończeniu
transakcji użytkownika. Jeśli użytkownik
nie ma nic w transakcji, jego sesja jest
wycinana natychmiast. Pierwsza wartość
to sid, a druga to serial dla sesji.
5/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Obiekty bazodanowe
Create or replace view raport1 as
select count(*), avg(salary),
department_name from employees
join departments using
(department_id);
Tworzenie widoku.
Create sequence moja1 start with 10
increment by 1;
Tworzenie sekwencji która zacznie
odliczać od 10 i będzie zwiększała
wartość o 1 za każdym pobraniem.
Create table sprzety (
nr number primary key,
nazwa varchar2(50) not null,
nr_departamentu number
references
departments(department_id)
);
Tworzenie tabeli z kluczem głównym
(nr) , kolumną która nie może być
pusta (nazwa) oraz kluczem obcym
(nr_departamentu).
Create global temporary table
(
pole1 number,
pole2 varchar2(50)
) on commit preserve rows;
Tworzenie tabeli tymczasowej której
zawartość nie będzie kasowana po
commicie.
Create global temporary table
(
pole1 number,
pole2 varchar2(50)
) on commit delete rows;
Tworzenie tabeli tymczasowej której
zawartość będzie kasowana po
commicie. Tak jest domyśnie , czyli
nie musicie pisać tego on commit
delete rows.
Create materialized view raport2
as select * from employees where
salary>(select avg(salary) from
employees);
Tworzenie widoku
zmaterializowanego , który będzie
można odświeżać. Taki rodzaj widoku
przechowuje dane.
Create materialized view log on
employees;
To zakładamy na tabele uwzględnione
w zapytaniach w widokach
zmaterializowanych , jeśli widoki te
będziemy chcieli odświeżać
przyrostowo.
Execute
dbms_mview.refresh('raport2');
Odświeżenie pełne widoku
zmaterializowanego.
6/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Execute
dbms_mview.refresh('raport2','F');
Odświeżenie przyrostowe widoku
zmaterializowanego.
Create index nazwiska on
employees(last_name, first_name);
Tworzenie indeksu na kolumnach
last_name, first_name.
Create database link oddzialGdynia
connect to hr identified by hr
using '231.64.32.45/xe';
Tworzenie linku bazodanowego do
innej bazy.
7/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Elementy SQL przydatne administratorom
Select * from employees where
hire_date>to_date('1990-02-
01','yyyy-mm-dd');
Funkcja to_date konwertuje tekst do
typu date. Datę wpisujemy w
pierwszym parametrze, format w
drugim. Według tego formatu zostanie
zinterpretowana data wpisana jako
parametr pierwszy.
Select last_name, department_name
from employees join departments
using(department_id)
Łączenie tabel employees i
departments z użyciem kolumny
department_id;
Select last_name, salary, (select
avg(salary) from employees) from
employees;
Podzapytanie zwracające średnią
zarobków w całej firmie dla każdego
wiersza.
select
s.sid,s.serial#,lmode,l.request,s.statu
s,osuser,s.machine,s.terminal,s.progr
am,schemaname, o.name
from v$lock l join obj$ o on
(l.id1=o.obj#)
join v$session s on (s.sid=l.sid);
Wyciąganie zablokowanych zasobów
wraz z sesjami które je blokują.
Select avg(salary), department_id
from employees group by
department_id;
Grupowanie
Select avg(salary),
department_name from employees
join departments
using(department_id) group by
department_name;
Grupowanie z łączeniem tabel
Insert into regions values
(6,'Afryka');
Wstawienie wiersza do tabeli
uzupełniając wszystkie kolumny.
Insert into regions(region_id) values
(7);
Wstawienie wiersza do tabeli
uzupełniając tylko wybrane kolumny.
Delete from regions where region_id
in (6,7);
Kasowanie wybranych wierszy
Update employees set salary=1000
where employee_id=100;
Zmiana danych w tabeli
8/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Commit;
Zatwierdzenie zmian
Rollback;
Wycofanie zmian z transakcji
9/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Backup & recovery
Backup database;
Backup bazy danych
Backup as compressed backupset
database;
Backup skompresowany
Backup database format 'c:\backup%u-
%D-%M-%Y.bck';
Backup ze wskazaniem gdzie ma
wylądować plik backupu
Backup as compressed backupset
database format 'c:\backup%u-%D-
%M-%Y.bck';
Backup skompresowany ze
wskazaniem gdzie ma wylądować
plik backupu
Backup incremental level 0 database;
Backup przyrostowy poziomu 0 –
czyli backup który jest podstawą do
backupów przyrostowych.Zasadniczo
pełny bckup
Backup incremental level 1 database;
Backup przyrostowy
Backup as compressed backupset
incremental level 0 database;
Backup przyrostowy poziomu 0
skompresowany – czyli backup który
jest podstawą do backupów
przyrostowych.Zasadniczo pełny
bckup
Backup as compressed backupset
incremental level 1 database;
Backup przyrostowy skompresowany
Backup cumulative incremental level
1 database;
Backup kumulatywny
Backup as compressed backupset
cumulative incremental level 1
database;
Skompresowany kumulatywny
Configure controlfile autobackup on;
Włączenie autobackupu pliku
kontrolnego i spfile
Shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
Odtwarzanie całej bazy
Shutdown immediate;
startup nomount;
Odtwarzanie controlfile i całej bazy
(konieczne)
10/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
restore controlfile from autobackup;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
Restore datafile 1
recover datafile 1
Odtwarzanie pliku danych
Backup tablespace dane, crm
Backup tablespaców dane i crm
Sql 'alter tablespace dane offline';
restore tablespace dane;
recover tablespace dane;
sql 'alter tablespace dane online';
Odtwarzanie tablespace
Shutdown immediate;
startup mount;
set until time „to_date('22-03-2012
13:05:00','dd-mm-yyyy hh24:mi:ss');
restore database;
recover database;
alter database open resetlogs;
Odtwarzanie bazy do punktu w czasie
11/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Eksport i import - narzędzia EXP i IMP
Exp full=y userid=system/haslo
file='c:\pelen_export.exp'
Eksport całej bazy
Exp userid=system/haslo
file='
' owner=hr
Eksport pojedynczego schematu
Exp userid=hr/haslo file='
tables=employees,departments
Eksport wybranych tabel ze schematu
użytkownika na którego się
autoryzujemy
Imp full=y destroy=y
userid=system/haslo
file='c:\pelen_export.exp'
Import całej bazy , z podmianą
obiektów jeśli już istnieją. Podmiana
nie dotyczy obiektów systemowych
Imp userid=system/haslo fromuser=hr
touser=hrkopia
file='c:\pelen_export.exp'
Import schematu do innego schematu
Imp userid=hr/haslo
tables=employees,departments
file='c:\tabele.exp'
Import tabel do schematu użytkownika
na którego się autoryzujemy.
12/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl
Export i import – Oracle Data Pump
Expdp system/haslo full=y
directory=katalog_exportow
dumpfile=pelenesport.dmp
Export całej bazy do katalogu dla
którego alias to katalog_exportow
Expdp system/haslo schemas=hr,sh
directory=katalog_exportow
dumpfile=schematy.dmp
Export schematów do katalogu dla
którego alias to katalog_exportow
Expdp hr/haslo
tables=employees,departments
directory=katalog_exportow
dumpfile=tabele.dmp
Export tabel do katalogu dla którego
alias to katalog_exportow
Expdp system/oracle
tablespaces=crm,dane
directory=katalog_exportow
dumpfile=tablespace.dmp
Export tablespaców do katalogu dla
którego alias to katalog_exportow
impdp system/haslo full=y
directory=katalog_exportow
dumpfile=pelenesport.dmp
Import bazy
impdp system/haslo schemas=hr,sh
directory=katalog_exportow
dumpfile=schematy.dmp
Import schematów
impdp hr/haslo
tables=employees,departments
directory=katalog_exportow
dumpfile=tabele.dmp
Import tabel
impdp system/oracle
tablespaces=crm,dane
directory=katalog_exportow
dumpfile=tablespace.dmp
Import tablespaców
13/13
Cheatsheet Administracja bazami danych Oracle I www.jsystems.pl