gzip, tar on unix

compressing: gzip dosya_adi.txt

extracting: gzip -d dosya_adi.txt.gz

all file compressing: gzip *.txt

all file extracting: gzip -d *.txt

all .txt file archiving to .tar file : tar -cvf dosya_adi.tar *.txt

folder archiving to .tar file : tar -cvf dosya_adi.tar klasör_adi/

.tar file exctracting: tar -xf dosya_adi.tar

.tar file compressing: gzip dosya_adi.tar

.tar.gz compressing: tar cvf - /tarlanıp sıkıştıracağın dizin | gzip > ~/deneme.tar.gz

-------------------------------------------------

all_users_tables_scripts.sh

------bazı kullanıcıların tüm tablolarının scriptleri----

#!/usr/bin/ksh
. $HOME/.profile

sqlplus -s /nolog << EOF > /dev/null
connect ORHAN/PASSWORD@ORCL

set verify off;
set termout off;
set feedback off;
set pagesize 0;
set heading off
set long 40000

SPOOL /tmp/orhan/all_users_tables_scripts.txt

select dbms_metadata.get_ddl('TABLE',u.table_name,u.owner) from dba_tables u
where u.owner in('OERIPEK','AATES','MCAN');

set heading off
set recsep off
col text format a80 word_wrap
spool off;
----------------------------------------------

tables,indexes scripts

----kullanıcının belirtilen tablosunun scripti için-----

set feedback off
set heading off
set long 4000
select dbms_metadata.get_ddl('TABLE','TABLO1') from dual;
----------------------------------------------

----belirtilen kullanıcının belirtilen tablosunun scripti için-----

set feedback off
set heading off
set long 4000
select dbms_metadata.get_ddl('TABLE','TABLO2','MEHMET') from dual;
----------------------------------------------

----kullanıcının tüm tablolarının scripti için----

set feedback off
set heading off
set long 4000
select dbms_metadata.get_ddl('TABLE',a.table_name) from USER_TABLES a;
--------------------------------------------------

----kullanıcının tüm indexlerinin scripti için----

set feedback off
set heading off
set long 4000
select dbms_metadata.get_ddl('INDEX',a.index_name) from USER_INDEXES a;
--------------------------------------------------

------ORHAN ve MEHMET kullanıcılarının tüm
tablolarının scriptini çıkaran sql sorgusu için------------

set feedback off
set heading off
set long 4000
select 'select DBMS_METADATA.GET_DDL(''TABLE'','''||a.table_name||''','''||a.owner||''') from dual;'
from dba_tables a where owner in('ORHAN','MEHMET');
---------------------------------------------------

tablespace_scripts.sh

database'imizde bulunan tüm tablespace'lerimizin scriptini
aşağıdaki örnek .sh dosyasında spool ile .txt dosyasına alabiliriz.
-----------------------------------------------
#!/usr/bin/ksh
. $HOME/.profile

sqlplus -s /nolog << EOF > /dev/null
connect orhan/password@orcl

set verify off;
set termout off;
set feedback off;
set pagesize 0;

create table ts_temp (lineno number, ts_name varchar2(40),text long);

DECLARE
CURSOR ts_cursor IS select tablespace_name,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
status
from sys.dba_tablespaces
where tablespace_name != 'SYSTEM'
and status != 'INVALID'
order by tablespace_name;
CURSOR df_cursor (c_ts VARCHAR2) IS select file_name,
bytes
from sys.dba_data_files
where tablespace_name = c_ts
and tablespace_name != 'SYSTEM'
order by file_name;

lv_tablespace_name sys.dba_tablespaces.tablespace_name%TYPE;
lv_initial_extent sys.dba_tablespaces.initial_extent%TYPE;
lv_next_extent sys.dba_tablespaces.next_extent%TYPE;
lv_min_extents sys.dba_tablespaces.min_extents%TYPE;
lv_max_extents sys.dba_tablespaces.max_extents%TYPE;
lv_pct_increase sys.dba_tablespaces.pct_increase%TYPE;
lv_status sys.dba_tablespaces.status%TYPE;
lv_file_name sys.dba_data_files.file_name%TYPE;
lv_bytes sys.dba_data_files.bytes%TYPE;
lv_first_rec BOOLEAN;
lv_string long;
lv_lineno number := 0;

procedure write_out(p_line INTEGER, p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into ts_temp (lineno, ts_name, text) values
(p_line, p_name, p_string);

end;

BEGIN
OPEN ts_cursor;
LOOP
FETCH ts_cursor INTO lv_tablespace_name,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_status;
EXIT WHEN ts_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := ('CREATE TABLESPACE '||lower(lv_tablespace_name));
lv_first_rec := TRUE;
write_out(lv_lineno, lv_tablespace_name, lv_string);
OPEN df_cursor(lv_tablespace_name);
LOOP
FETCH df_cursor INTO lv_file_name,
lv_bytes;
EXIT WHEN df_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
lv_string := 'DATAFILE ';
else
lv_string := lv_string || ',';
end if;
lv_string:=lv_string||''''||lv_file_name||''''||
' SIZE '||to_char(lv_bytes) || ' REUSE';
END LOOP;
CLOSE df_cursor;
lv_lineno := lv_lineno + 1;
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' DEFAULT STORAGE (INITIAL ' ||
to_char(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_string := (' '||lv_status);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='/';
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_tablespace_name, lv_string);
END LOOP;
CLOSE ts_cursor;
END;
/
SPOOL /temp/orhan/tablespace_scripts.txt
set heading off
set recsep off
col text format a80 word_wrap

select text
from ts_temp
order by ts_name, lineno;

spool off;

drop table ts_temp;
-----------------------------------------

eğer tablespace'lerimiz üzerinde zamanla değişiklikler
yapmışsak (datafile'ların boyutunun artırılması gibi,
ALTER DATABASE DATAFILE ...) o zaman en güncel haliyle
tablespace'lerimizin scriptlerini dbms_metadata.get_ddl
ile çıkarabiliriz.

-------------------------------------
set lines 132
set pages 200
set long 4000

select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) DDL from dba_tablespaces
-------------------------------------

bir örnek script daha..
-------------------------------------------
DECLARE

CURSOR get_ts IS SELECT * FROM dba_tablespaces
WHERE tablespace_name != 'SYSTEM';

CURSOR get_df (p_ts VARCHAR2) IS
SELECT * from dba_data_files
WHERE tablespace_name = p_ts;

l_str VARCHAR2(10);

BEGIN

FOR ts_rec IN get_ts LOOP

dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name);

FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP

IF get_df%ROWCOUNT = 1 THEN
l_str := 'DATAFILE';
ELSE
l_str := ',';
END IF;

dbms_output.put_line (l_str||' '
||chr(39)||df_rec.file_name||chr(39)
||' SIZE '||df_rec.bytes||' REUSE ');

if df_rec.autoextensible = 'YES' then

dbms_output.put_line (' AUTOEXTEND ON'
||' NEXT '||df_rec.increment_by );

if df_rec.maxbytes = 68719443968 then
dbms_output.put_line (' MAXSIZE UNLIMITED');
else
dbms_output.put_line (' MAXSIZE '||df_rec.maxbytes);
end if;

end if;

END LOOP;


dbms_output.put_line ('EXTENT MANAGEMENT ' ||ts_rec.extent_management );

if ts_rec.extent_management = 'LOCAL' then

if ts_rec.allocation_type = 'SYSTEM' then

dbms_output.put_line (' AUTOALLOCATE ');

else

dbms_output.put_line (' UNIFORM SIZE '||ts_rec.initial_extent);

end if;

end if;

if ts_rec.extent_management = 'DICTIONARY' then

dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent
||' NEXT '||ts_rec.next_extent
||' MINEXTENTS '||ts_rec.min_extents
||' MAXEXTENTS '||ts_rec.max_extents
||' PCTINCREASE '||ts_rec.pct_increase||' ) ');

end if;

dbms_output.put_line (' ONLINE;');
dbms_output.new_line;

END LOOP;

END;
/
-------------------------------------------------

duplica kayıtları silmek

drop table orhan.personel;

create table orhan.personel (id number,adi varchar2(20),gorevi varchar2(50));

insert into orhan.personel (id,adi,gorevi) values (10,'orhan','dba');
insert into orhan.personel (id,adi,gorevi) values (20,'ahmet','hizmetci');
insert into orhan.personel (id,adi,gorevi) values (10,'orhan','dba');
insert into orhan.personel (id,adi,gorevi) values (20,'ahmet','hizmetci');
insert into orhan.personel (id,adi,gorevi) values (10,'orhan','dba');

delete from orhan.personel where rowid not in (select min(rowid) from orhan.personel group by id);
---------------------------------------------------
7 gb a kadar artan rollback segmentlerde duplica kayıtları silmek için;

delete from orhan.personel where rowid in
(select rid from (select rowid rid, row_number() over (partition by id order by rowid) rn from orhan.personel )where rn <> 1 );

UNIX'TE TEK SATIR SELECT KOMUTU

ordba@ordbap1>/home/orhan$ echo "select * from all_directories;" | sqlplus -s USER/PASSWORD@SID

DBMS_OUTPUT.PUT_LINE

Birden fazla satır döndüren select cümlelerini output ile
ekrana çıkarmak için for döngüsü kullanmak gerekiyor.
(Ali'ye atıfta bulunayım... :)))
----------------------------------------------------
DECLARE
text VARCHAR2(200);
v_text long;
BEGIN
for text in(select column_name from user_tab_columns where table_name = 'TABLE_NAME')
loop
v_text:=v_text||text.column_name||chr(10);
end loop;
dbms_output.put_line(v_text);
END;
-------------------------------------------
eğer tek değer döndüren bir select cümlesi ise
bildiğimiz gibi into kullanılır...

---------------------------------------------
DECLARE
text VARCHAR2(200);
v_text long;
BEGIN
v_text:= 'select column_name as text from user_tab_columns where table_name =''TABLE_NAME''';
execute immediate v_text into text;
dbms_output.put_line(text);
END;
------------------------------------------------------------

drop some tables

Tüm constraint yapıları ile birlikte belirtilen user'ın bazı
tablolarını drop etmek için aşağıdaki gibi bi plsql bloğu kullanılabilir.
-----------------------------------------------------------
declare
cursor c1 is
select 'drop table '||table_name|| ' cascade constraints' text from all_tables where owner ='USERNAME' and table_name like 'PM%';
begin
for i in c1 loop
BEGIN
dbms_output.put_line(i.text);
EXECUTE IMMEDIATE (i.text);
EXCEPTION
When others Then
DBMS_OUTPUT.PUT_LINE('ERROR='||i.text);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
--RAISE;
END;
end loop;
end;
------------------------------------------------------

SQLERRM AND USER DEFINED EXCEPTION

Aşağıdaki plsql bloğu ile Oracle'da tanımlanan Ora-
hatalarının tümünü dbms_output ile ekrana çıkarabiliriz.

------------------------------------------
DECLARE
v_error_desc VARCHAR2 (200);
v_error_code NUMBER;
BEGIN
FOR errno IN REVERSE -32799 .. 0
LOOP
v_error_code := errno;
v_error_desc := SQLERRM (errno);

IF v_error_desc NOT LIKE '%Message % not found;%'
AND v_error_desc != 'ORA' || TO_CHAR (errno, 'FM09999') || ': '
THEN
DBMS_OUTPUT.put_line (v_error_desc);

END IF;
END LOOP;
END;
----------------------------------------------
Kullanıcı tanımlı hatalar'da ise 'raise _applicaion_error(errorID, errorDesc)' kullanılır.'errorID' ve 'errorDesc' alanlarını kullanıcı belirler.'errorID' [-20000,-20999] arasında olmalıdır.'errorDesc' alanı ise en fazla 2000 karakterlik 'varchar2' veri tipinde açıklama yazılabilir.

declare
v_value number :=110;
begin
if(v_value > 100) then
raise_application_error(-20010,'Değer büyük!');
end if;
end;

-----------------------------------------------------------------
Bir diğer yol, hatayı tanımlayıp raise etmek;

declare
v_value number :=110;
deger exception;
begin
if(v_value > 100) then
raise deger;
end if;
exception
when deger then
raise_application_error(-20010,'Değer büyük!');
end;

----------------------------------------------------------------