How to Enable/Disable Unified Audit

Hello everyone, I want to explain how to enable/disable unified audit option on Oracle Databases. When you enable or disable unified audit option, all databases must be closed while operation time.

You can check the unified audit that open or closed status with the below code

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
NO

You can open a unified audit option with the below code.

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

When operation completed, you can check to unified audit status.

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
YES

Also, you can purge to logs on the databases with the below code.

BEGIN 
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp =>  FALSE);
END;
/

You can purge to logs on the OS with the below code.

EXEC DBMS_AUDIT_MGMT.DROP_PURGE_JOB('CLEANUP_OS_DB_AUDIT_RECORDS'); 


BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
AUDIT_TRAIL_TYPE   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/

Lastly, you can disable to unified audit with the below code.

$ cd $ORACLE_HOME/rdbms/lib 
$ make -f ins_rdbms.mk uniaud_off ioracle 

and check

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
NO

SQL Tune Nasıl Yapılır

Bu yazıda bir sql’i tune edip sorgu sonucunun hızlandırılması konusunda index ekleme yöntemini açıklayacağım. Burada anlatacağım yöntemi oracle, postgresql, mysql ve diğer veritabanlarında kullanabilirsiniz.

İlk olarak index nedir ve neden eklenmesi gerektiğini açıklamak istiyorum. İndex oluşturulmuş bir ağaç yapısıdır ve birden çok index türü vardır. B-tree index yapısını aşağıdaki şekilde inceleyebilirsiniz. Leaf block dediğimiz yerde ilgili tablonun içerisindeki rowların id leri bulunmaktadır. Bunu adres olarak düşünebiliriz. Branch blocks dediğimiz yer ise datanın belirli bir düzene göre gruplandığı yerdir. Aşağıdaki resimde görebileceğiniz gibi job_id kolonunda 2 değerine sahip satırların id numaraları bir blokta tutulmuş. Bu da job_id = 2 koşulu istendiğinde tabloyu full okumak yerine sadece ilgili bloklara gidip okuma yapacağı anlamına gelmektedir.

Tablo full okunmadığı için ilgili sql daha hızlı sonuç üretecektir. Daha hızlı sonuç üretmesi veritabanının fazla efor harcamamasına ve daha performanslı çalışmasına neden olacaktır.

Çok fazla transaction alan veritabanlarında sql’lerin sorgu sonuç süreleri kritik bir öneme sahiptir. Şu şekilde bir hesap yapacak olursak daha anlaşılır olacaktır. Elimizde çalışması 1 sn süren bir sql olsun ve bu sql uygulamadan veritabanına 10 bin kez gönderilsin. Bu işlem veritabanında 1 sn* 10 bin kez çalışacaktır ve veritabanını toplamda 10 bin saniye / 60 = 166 dakika meşgul edecektir. Tabi cpu’nun saniye de milyarlarca işlem yapabildiği için gerçek hayatta bu işlem 166 dakikada sürmeyecektir.

İlgili sql’e index ekleyip çalışma süresini yarıya düşürdüğümüzü varsayarsak toplamda 83 dakika kazanmış olacağız ki bu muazzam bir süre. Bu örnekte saniyeler üzerinden gidiyorum fakat sql’lerin veritabanında çalışma süreleri mili saniyeleri geçmemesi gerekiyor. 0.5 saniye bile bir sorgu için aşırı büyük bir değer.

Şimdi veritabanının bir sorguyu çalıştırırken sonucu getirmek için izlediği yolu göstereceğim. Örnek olarak elimizde aşağıdaki gibi bir sql sorgusu olsun.

select firts_name, last_name from hr.employee where job_id = 2;

Bu sql’i veritabanı şu şekilde yorumlayacaktır:

– job_id kolonunda değeri 2 olan
– hr.employee tablosundaki
– first_name ve last_name kolonundaki tüm verileri getir.

Sorgu bizim aksimize tersten okunarak istenilen dataları getirmek için işlenmeye başlandı. Bu kısımda job_id kolonunda 2 değerine sahip satırlar için tabloyu satır satır okuyup eşleşen kayıtları bulmaya çalışacaktır. İlgili kolonda eğer bir index yok ise explain plan da aşağıda da görebileceğiniz gibi “full table access” yöntemi kullanılacaktır.

OPERATIONOPTIONSOBJECT_NAMEPOSITION
SELECT STATEMENT2
..TABLE ACCESSTABLE ACCESS FULLEMPLOYEES1

Eğer employees tablosunun job_id kolonunda bir index olmuş olsaydı tabloyu full okumak zorunda kalmayacak ve explain plan’ı aşağıdaki gibi olacaktı.

OPERATIONOPTIONSOBJECT_NAMEPOSITION
SELECT STATEMENT2
..TABLE ACCESSINDEX RANGE SCANEMPLOYEES1

Şekilde görüldüğü gibi index range scan yöntemi ile tabloya yapılan erişim sorgu maliyetini düşürmektedir. Elinizdeki sql’leri bu doğrultuda inceleyerek gerekli olabilecek indexleri oluşturabilirsiniz.

Son olarak aşağıdaki gibi bir sql’i ele alalım.

select firts_name, last_name from hr.employee where job_id = 2 and first_name = 'Emrah';

Bu sqli çalıştırırken job_id kolonunda index olmasına rağmen sorgu çalıştırıldığında ilgili index kullanılmayabilir. Bu durumda bileşik index oluşturulması gerekir. İlk oluşturulan basit index tipini ifade etmekteydi. Bu durumda bileşik index oluşturmak zorunda kalınabilirdi. Oracle veritabanında basit ve bileşik index aşağıdaki gibi oluşturulabilir.

# basit index
create index [index_name] on [table_name] (column);

# bileşik index
create index [index_name] on [table_name] (column1,column2);

Oracle Restore to the Point

Bu yazıda oracle veritabanında restore point oluşturulması, oluşturulan restore pointe dönülmesini anlatacağım.

Eğer database’nin archive log modu ve flashback özelliği kapalı ise aşağıdaki şekilde bu özelliklerin açılması gerekiyor. Bu işlemlerin yapılabilmesi için db’nin mount modda olması gerekiyor.

SQL> shutdown immediate;
SQL> startup mount;

Database’i archive log moduna almak için:

SQL> alter database archivelog;

Flashback özelliğinin açılabilmesi için öncelikle aşağıdaki iki parametrenin ayarlanması gerekiyor.

SQL> alter system set db_recovery_file_dest_size = 2G;
SQL> alter system set db_recovery_file_dest = 'path...';

Flashback modunda database log dosyaları oluşturmaktadır. Bu log dosyaları değişen dataların eski görünümünü içermektedir. Logların tutulacağı dizin db_recovery_file_dest parametresiyle belirlenir. Ayrıca dikkat edilmesi gereken db_recovery_file_dest_size değeri diskteki free alan ve oluşacak logların boyutuna yetecek şekilde ayarlanması gerektiğidir.

Parametreler ayarlandıktan sonra flashback özelliği aşağıdaki gibi açılabilir.

SQL> alter database flashback on;

Bu işlem sonunda db açılabilir veya restore point oluşturacaksanız şimdilik veritabanını open moda alma adımını geçmeniz gerekmektedir.

SQL> alter database open;

Archive log modu ve flashback özelliğinin açık olup/olmadığı aşağıdaki gibi teyit edilebilir.

SQL> select flashback_on, log_mode from v$database;

FLASHBACK_ON  LOG_MODE
------------  ----------------
YES           ARCHIVELOG

Bu sonucun döndüğünden emin olduktan sonra restore point oluşturulabilir.

Restore Point Oluşturmak

Veritabanı üzerinde restore point aşağıdaki şekilde oluşturulabilir.

SQL> create restore point RESTORE_POINT_NAME guarantee flashback database;

Burada dikkat edilmesi gereken nokta; restore point create edildiği sırada veritabanının mount modda olması gerekmektedir. Aksi tahtirde oracle data tutarlılığı konusunda garanti vermemektedir.

Create edilen restore pointin teyidi v$restore_point viewinden yapılabilir.

SQL> select name,scn,gua,storage_size from v$restore_point;

NAME                SCN   GUA STORAGE_SIZE
------------------- ---- ---- ------------
RESTORE_POINT_NAME   129  YES      5242800

Restore Point’e Dönmek

Oluşturulan restore pointe dönme işlemi aşağıdaki şekilde yapılabilir.

 SQL> flashback database to restore point RESTORE_POINT_NAME;

Bu işlem sonucunda ilgili restore pointe dönüldüğü kontrolünü v$datafile viewindeki CHECKPOINT_TIME kolonunu kontrol ederek öğrenebilirsiniz. Bu alandaki değerin restore pointi oluşturduğunuz zamanı gösteriyor olması gerekiyor.

Oracle Redo Log Switch Map

Oracle veritabanında redo logların saatlik kaç kez switch olduğu bilgisini aşağıdaki sql komutu kullanarak öğrenebilirsiniz.

set linesize 300 
set pagesize 5000
column 00 format 999 heading "00" 
column 01 format 999 heading "01" 
column 02 format 999 heading "02" 
column 03 format 999 heading "03" 
column 04 format 999 heading "04" 
column 05 format 999 heading "05" 
column 06 format 999 heading "06" 
column 07 format 999 heading "07" 
column 08 format 999 heading "08" 
column 09 format 999 heading "09" 
column 10 format 999 heading "10" 
column 11 format 999 heading "11" 
column 12 format 999 heading "12" 
column 13 format 999 heading "13" 
column 14 format 999 heading "14" 
column 15 format 999 heading "15" 
column 16 format 999 heading "16" 
column 17 format 999 heading "17" 
column 18 format 999 heading "18" 
column 19 format 999 heading "19" 
column 20 format 999 heading "20" 
column 21 format 999 heading "21" 
column 22 format 999 heading "22" 
column 23 format 999 heading "23" 
column 24 format 999 heading "24" 
column "Day" for a5 
column INST_ID for a7 
column DAY_S for a8 
column date_s for a14 
prompt 
prompt Redo Log Switches 
prompt 

select  
CASE INST_ID WHEN 1 THEN date_str END date_s,
to_char(INST_ID) INST_ID, 
CASE INST_ID WHEN 1 THEN "Day" END day_s, 
"00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"  from  (SELECT trunc (first_time) date_str,  INST_ID, 
to_char (trunc (first_time),'Dy') "Day", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 0, 1)) "00", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 1, 1)) "01", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 2, 1)) "02", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 3, 1)) "03", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 4, 1)) "04", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 5, 1)) "05", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 6, 1)) "06", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 7, 1)) "07", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 8, 1)) "08", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 9, 1)) "09", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 10, 1)) "10", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 11, 1)) "11", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 12, 1)) "12", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 13, 1)) "13", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 14, 1)) "14", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 15, 1)) "15", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 16, 1)) "16", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 17, 1)) "17", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 18, 1)) "18", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 19, 1)) "19", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 20, 1)) "20", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 21, 1)) "21", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 22, 1)) "22", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 23, 1)) "23" 
from gv$log_history 
where trunc(first_time) > sysdate-30 
group by INST_ID, trunc(first_time)  
order by trunc(first_time) DESC, INST_ID );

Redo logların anlık durumlarını kontrol etmek için aşağıdaki komutu kullanabilirsiniz.

set linesize 2000
set pagesize 2000
col REDOLOG_FILE_NAME for a70 
SELECT 
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    (a.BYTES/1024/1024) AS SIZE_MB,
    b.MEMBER    AS REDOLOG_FILE_NAME
FROM v$log a 
JOIN v$logfile b ON a.Group#=b.Group# 
ORDER BY 2, a.GROUP# ASC; 

Zaman içerisinde sql lerin devamını ekleyeceğim.

Oracle Enable/Disable Unified Audit

Merhaba arkadaşlar, bu yazıda oracle veritabanlarında unified auditing özelliğini açmayı göstereceğim. Unified özelliğinin açılması sunucu üzerinde bulunan tüm veritabanlarının kapalı olması gerekmektedir.

Veritabanı üzerinde unified auditin açık olup olmadığı kontrol aşağıdaki gibi yapılabilir.

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
NO

Unified audit özelliği aşağıdaki şekilde açılır.

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

İşlem sonrasında unified audit özelliği açılmış olacaktır.

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
YES

Veritabanı üzerinde bulunan logları aşağıdaki şekilde purge edebilirsiniz.

BEGIN 
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp =>  FALSE);
END; 
/ 

OS üzerinde bulunan auditleri aşağıdaki şekilde purge edebilirsiniz

EXEC DBMS_AUDIT_MGMT.DROP_PURGE_JOB('CLEANUP_OS_DB_AUDIT_RECORDS'); 


BEGIN 
DBMS_AUDIT_MGMT.DEINIT_CLEANUP( 
AUDIT_TRAIL_TYPE   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL); 
END;
/

Son olarak açık olan unified audit aşağıdaki gibi kapatılabilir.

$ cd $ORACLE_HOME/rdbms/lib 
$ make -f ins_rdbms.mk uniaud_off ioracle 
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
NO

[Resolved]: Uncaught Error: Call to undefined function oci_connect()

Php ile oracle veritabanlarına bağlanmaya balıştığınızda aşağıdaki gibi bir hata alıyorsanız çözümü bu yazıda bulabilirsiniz.

<?php

$dbconn = pg_connect("host=localhost port=5432 dbname=testdb user=testusr password=12345");
Fatal error: Call to undefined function oci_connect() in /var/www/html/index.php on line 3

İlk olarak oracle download sayfasından ihtiyacınız olan client sürümünü indirebilirsiniz. Basic ve sdk paketini mutlaka indirmelisiniz. Diğer paketleri ihtiyacınız doğrultusunda indirebilirsiniz. Ben aşağıdaki tüm paketleri indirdim.

instantclient-basic-linux.x64-19.3.0.0.0dbru.zip
instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip
instantclient-tools-linux.x64-19.3.0.0.0dbru.zip
instantclient-sdk-linux.x64-19.3.0.0.0dbru.zip
instantclient-jdbc-linux.x64-19.3.0.0.0dbru.zip
instantclient-odbc-linux.x64-19.3.0.0.0dbru.zip

İndirdiğiniz tüm paketleri /opt/oracle dizini altına çıkartın.

# mkdir /opt/oracle
# unzip instantclient-basic-linux.x64-19.3.0.0.0dbru.zip -d /opt/oracle/
# unzip instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip -d /opt/oracle/
# unzip instantclient-tools-linux.x64-19.3.0.0.0dbru.zip -d /opt/oracle/
# unzip instantclient-sdk-linux.x64-19.3.0.0.0dbru.zip -d /opt/oracle/
# unzip instantclient-jdbc-linux.x64-19.3.0.0.0dbru.zip -d /opt/oracle/
# unzip instantclient-odbc-linux.x64-19.3.0.0.0dbru.zip -d /opt/oracle/

# ls /opt/oracle
instantclient_19_3

# mv instantclient_19_3 instantclient

Dizin yapısı yukarıdaki gibi olacaktır.

Eğer httpd kullanıyorsanız aşağıdaki dosyaların en altına ilgili komutları kopyalayın.

# cat /etc/sysconfig/httpd

LD_LIBRARY_PATH=/opt/oracle/instantclient
PATH=/opt/oracle/instantclient:$PATH
ORACLE_HOME=/opt/oracle/instantclient 
# cat /etc/bashrc 

export LD_LIBRARY_PATH=/opt/oracle/instantclient
export PATH=/bin:/opt/oracle/instantclient:$PATH
export ORACLE_HOME=/opt/oracle/instantclien

Bu ayarlamaları yaptıktan sonra oci8 modülü kurulması gerekmektedir. Bu modülü pecl veya remi repository’i tanıtarak yum install şeklinde kurabilirsiniz. pecl ile aşağıdaki şekilde kurabilirsiniz.

# pecl download oci8
# tar -zxf oci8-1.4.10.tgz
# cd oci8-1.4.10

phpize ile paketi kurulum için hazırlamak gerekiyor. phpize kurulu değilse yum install phpize şeklinde kurabilirsiniz.

# phpize
# ./configure -with-oci8=shared,instantclient,/opt/oracle/instantclient

veya

# ./configure -with-oci8=shared,$ORACLE_HOME

Son aşama olarak paket kurulumunu aşağıdaki gibi gerçekleştirebilirsiniz.

# make install

Procedure İçerisinden Dictionary Viewlere Erişim

Oracle db içerisinde procedure oluşturup scheduler job içerisinden çağırıldığı durumlarda yetki hatası ile karşılaşılabilir. (Eğer procedure içerisinden v$session gibi dictionary viewlere select çekiliyorsa)

Procedure içerisinden dictionary objelere ulaşmak istiyorsanız aşağıdaki yetkinin ilgili kullanıcıya tanımlanması gerekmektedir.

Kullanıcı DBA rolüne sahip olsa bile bu yetkilerin verilmesi gerekmektedir.

-- Tüm dictionary viewlere erişim yetkisi için
SQL> grant select any dictionary to dbuser;

-- Aşağıdaki şekilde de tanımlayabiliriz
SQL> grant select on v_$session to dbuser;


SQL> grant select_catalog_role to dbuser;

Oracle Snapshot Standby Oluşturma

Oracle’nin flashback teknolojisi sayesinde pyhsical standby olarak ayarlanmış db lerin read/write modda açmak mümkün. Bilindiği üzere pyhsical standby db leri en fazla read only modda açıp sadece select komutlarını çalıştırabilir konuma getirebiliriz. Bu durumda varolan data üzerinde herhangi bir değişikliğe izin verilmeyecektir.

Snapshot standby olarak ayarlanan bir physical standby db; read/write moda geçerek production makinesinin o andaki verileri üzerinde istenilen tüm islemleri yapmayı mümkün kılacaktır.

Örneğin t anında snyc olan bir pyhsical standby dbsi snapshot standby durumuna alındığında t1 anında production makinesinde yapılan bir değişiklik standby tarafında görünmeyecektir.

Önemli Not 1: Snapshot standby moduna alınacak db nin flashback özelliğinin açık olması gerekmektedir.

Önemli Not 2: Production db üzerinde snapshot özelliğini açmadan önce mutlaka db nin kapatılıp tekrar açılması gerekmektedir. Aksi taktirde oracle data tutarlılığı konusunda garanti vermemektedir.

Önemli Not 3: Bu özellik sadece Oracle Database Enterprise Edition sürümlerinde desteklenmektedir.

Önemlii Not 4: Flashback dosyalarının nereye oluşturulacağı kontrolü ve belirtilen dizinin doluluk oranının db_recovery_file_dest_size parametresinden düşük olduğundan mutlaka emin olun.

Pyhsical standby db nin flashback özelliği açık olmayabilir. Snapshot moduna alınacak db nin flashback özelliğinin açılmasının gözden kaçmaması adına aşağıdaki komutlara eklenmiştir.

SQL> select open_mode,database_role,flashback_on from v$database;

Result:
open_mode database_role flashback_on
MOUNT PHYSICAL STANDBY NO

Pyhsical standby dan snapshot standy moduna almak için aşamalar aşağıdaki şekildedir. Aşamaları sırayla uygulayarak pyhsical standby db yi read/write moda alabilirsiniz.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
SQL> ALTER DATABASE OPEN;

Aşamalar sırasıyla yapıldığı taktirde db read/write modda açılacaktır.

SQL> select open_mode,database_role,flashback_on from v$database;

Result:
open_mode database_role flashback_on
READ/WRITE SNAPSHOT STANDBY YES

Snapshot standby olarak ayarlanmış bir db nin tekrar physical standby’a çevirilmesi durumunda aşağıdaki adımları takip edebilirsiniz.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> select open_mode,database_role,flashback_on from v$database;

Result:
open_mode database_role flashback_on
MOUNT PHYSICAL STANDBY NO

Tablespace İçerisindeki Objelerin Taşınması

Tablespace içerisinden büyük objelerin silinmesi durumunda disk üzerinde alan kazanımı olmadığı için tablespace’i recreate etme yoluna gidebiliriz. Bu durumda diskten alan kazanmanın yanında tablespace içerisinde bulunan objeler defragmente olup db üzerinde performans artışlarını beraberinde getirecektir. Tablespaceyi recreate etmenin aşamaları şu şekildedir:

  1. Yeni tablespace create etmek (bundan sonra TS_DATA_NEW olarak adlandırılacaktır)
  2. default_tablespace’i TS_DATA olan schemaların alter edilmesi ve gerekli quota ayarlarının yapılması.
  3. Varolan tablespace (TS_DATA olarak adlandırılacaktır) içerisindeki tüm objelerin move edilmesi.
    1. Tablo partition ve subpartitionların taşınması
    2. Lobların taşınması
    3. Lob partitionların taşınması
    4. İndexlerin taşınması
    5. Partitionlu indexlerin taşınması
  4. TS_DATA’yı offline konumuna almak
  5. Gerekli olması durumunda modify default attributes yapmak
  6. TS_DATA’yı drop etmek
  7. TS_DATA_NEW’i rename edip TS_DATA olarak değiştirmek.
  8. İndexlerin kontrolü

1. Create Tablespace

Yeni oluşturulacak TS_DATA_NEW tablespace için örnek komut aşağıdaki gibidir. Tabiki varolan tablespace üzerinde tanımlanan kota, yetki v.s gibi yetkilerin de varolan tablespace den alınıp yeni oluşturulan tablespaceye tanımlanması gerekmektedir. Aksi halde objeler taşındıktan sonra yetki problemleri nedeniyle başka sorunlara sebebiyet verebiliriz.

CREATE TABLESPACE TS_DATA_NEW 
DATAFILE '+DATA'
SIZE 31G;

2. Default Tablespace Değiştirmek

select username,default_tablespace,'alter user '||username||' default tablespace TS_DATA_NEW;' 
from dba_users
where default_tablespace='TS_DATA';

3.1. Tablo Partitionların Taşınması

Tablo partition ve sub partitionların taşınması için örnek komutaşağıdaki şekildedir. Burada klasik yöntem ile taşınacaktır. Sistemin kesintiye uğramaması için redef gibi taşıma yöntemlerini de tercih edebilirsiniz.

select 'ALTER TABLE '||table_owner||'.'||table_name||'  MOVE PARTITION '||partition_name||' TABLESPACE TS_DATA_NEW parallel 8;'
from dba_tab_partitions
where tablespace_name = 'TS_DATA';

Subpartitionların taşınması aşağıdaki şekilde gerçekleştirilebilir.

select 'ALTER TABLE '||table_owner||'.'||table_name||'  MOVE SUBPARTITION '||subpartition_name||' TABLESPACE TS_DATA_NEW parallel 8;' 
from dba_tab_subpartitions
where tablespace_name = 'TS_DATA';

3.2. Lob ve Lobparitionların Taşınması

Loblar aşağıdaki şekilde taşınabilir.

select owner,table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME, 'alter table '||owner||'.'||table_name||' move lob ('||COLUMN_NAME||') store as '||SEGMENT_NAME||' (tablespace TS_DATA_NEW);' 
from dba_lobs
where tablespace_name ='TS_DATA';

Lob partitionlar aşağıdaki şekilde taşınabilir.

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as  (tablespace TS_DATA_NEW);' 
FROM DBA_LOB_PARTITIONS
WHERE TABLESPACE_NAME = 'TS_DATA' ;

3.3. İndexlerin Taşınması

İndexler aşağıdaki şekilde taşınabilir.

select distinct 'ALTER INDEX '||owner||'.'||index_name||' REBUILD ONLINE TABLESPACE TS_DATA_NEW PARALLEL 16; ALTER INDEX '||owner||'.'||index_name||' NOPARALLEL;' 
from dba_indexes
where tablespace_name = 'TS_DATA';

İndex partitionları aşağıdaki şekilde taşınabilir.

select 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' TABLESPACE TS_DATA_NEW  
ONLINE PARALLEL 16; ALTER INDEX '||index_owner||'.'||index_name||' NOPARALLEL;'
from dba_ind_partitions
where tablespace_name = 'TS_DATA';

4. Tablespace’i Offline durumuna getirmek

Aşağıdaki şekilde TS_DATA’yı offline konuma getirebiliriz. Burada normal şeçeneğini kullanmak mantıklı olabilir. Çünkü tablespace içerisinde unutulan bir obje olduğu durumlarda uyarı verecektir.

ALTER TABLESPACE TS_DATA OFFLINE NORMAL/IMMEDIATE;

5. Modify Default Attribute

Varolan TS_DATA tablespace’i drop ettikten sonra dba_part_tables ve dba_part_indexes viewlerinden taşınan objelerin ownerleri altında bulunan objelerin kontrolünün yapılması gerekmektedir. Tablespace drop edildikten sonra bu viewlerin içerisinde bulunan DEF_TABLESPACE_NAME kolonunda bulunan değerlerin bazınlar veya bir çoğu “_$deleted$25$3” gibi bir değere dönebilir.

Bunun bu şekilde değişmesinin nedeni partitionlu tablo ve indexlerin move edilmesi sonucunda metadata diyebileceğimiz adresin değişmemesinden kaynaklanmaktadır. Buradan bu kayıtları bulup aşağıdaki şekilde alter edilmesi gerekmektedir. Aksi halde insert gibi transactionlarda “ORA-00959: tablespace ‘_$deleted$25$3’ does not exist” gibi hatalar alabilirsiniz.

alter index SCHEMA.INDEX_NAME modify default attributes tablespace TBS_EXISTING;

alter table SCHEMA.TABLE_NAME modify default attributes tablespace TBS_EXISTING;

6. Drop Tablespace

Varolan tablespace aşağıdaki şekilde drop edilebilir.

DROP TABLESPACE TS_DATA INCLUDING CONTENTS AND DATAFILES;

7. Rename Tablespace

Varolan tablespace aşağıdaki şekilde rename edilebilir. Oracle’nin rename tablespace komutunu 11g ve üzeri sürümlerde desteklediğini unutmamak gerekir.

ALTER TABLESPACE TS_DATA_NEW RENAME TO TS_DATA;

Bunun çalıştırılmasının ardından 2. aşamada değiştirdiğimiz default tablespace değeri de otomatik değişecektir. Yine de her ihtimale karşı dba_users view’inden kontrol edilmesi daha sağlıklı olacaktır.

8. İndexlerin Kontrolü

Son olarak indexlerin kontrolünü yapmak gerekiyor. Aşağıdaki şekilde yapılabilir.

select  * from dba_indexes where tablespace_name = 'TS_DATA' and status <> 'USABLE'; 

select * from dba_ind_partitions where tablespace_name = 'TS_DATA' and status <> 'USABLE';

Objelerin Otomatik Taşınması

Aşağıdaki sql komutu ile tablespace içerisindeki tüm ojeleri kolayca taşıyabilirsiniz.

SELECT *
     FROM (SELECT 0 sira,
                  owner,
                  table_name,
                     'alter table ' || owner || '.' || table_name || ' modify default attributes tablespace  TBS_EXISTING ;'    move_sql
             FROM dba_part_tables
            WHERE DEF_TABLESPACE_NAME = '&&tbsname'
           UNION ALL
           SELECT 1 sira,
                  owner,
                  table_name,
                     'alter index ' || owner || '.' || index_name || ' modify default attributes tablespace  TBS_EXISTING ;'    move_sql
             FROM dba_part_indexes
            WHERE DEF_TABLESPACE_NAME = '&&tbsname'
           UNION ALL
           SELECT 2 sira,
                  owner,
                  table_name,
                     'ALTER TABLE ' || owner || '.' || table_name || '  MOVE TABLESPACE  TBS_EXISTING parallel 8;'    move_sql
             FROM dba_tables
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 3 sira,
                  table_owner                                   owner,
                  table_name,
                     'ALTER TABLE ' || table_owner || '.' || table_name || '  MOVE PARTITION ' || partition_name || ' TABLESPACE  TBS_EXISTING parallel 8;'    move_sql
             FROM dba_tab_partitions
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 4 sira,
                  owner,
                  table_name,
                     'alter table ' || owner || '.' || table_name || ' move lob (' || COLUMN_NAME || ') store as ' || SEGMENT_NAME || ' (tablespace  TBS_EXISTING );'    move_sql
             FROM dba_lobs
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 5 sira,
                  owner,
                  table_name,
                     'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE  TBS_EXISTING PARALLEL 16;
  ALTER INDEX ' || owner  || '.' || index_name || ' NOPARALLEL;'    move_sql
             FROM dba_indexes
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 6 sira,
                  i.owner,
                  i.table_name,
                     'ALTER INDEX ' || p.index_owner || '.' || p.index_name || ' REBUILD PARTITION ' || p.partition_name || ' TABLESPACE  TBS_EXISTING PARALLEL 16;
  ALTER INDEX ' || p.index_owner || '.' || p.index_name || ' NOPARALLEL;'
             FROM dba_ind_partitions p, dba_indexes i
            WHERE     p.tablespace_name = '&&tbsname'
                  AND p.index_owner = i.OWNER
                  AND p.index_name = i.index_name) aa
 ORDER BY 2, 3, 1;

Not: Bu yazı zaman içerisinde tekrar güncellenecektir.

Resource Privileges in Oracle Database

If you don’t have resource privileges in oracle databases, you don’t create the following objects:

SQL> select privilege from dba_sys_privs where grantee='RESOURCE';

CREATE TABLE
CREATE TYPE
CREATE TRIGGER
CREATE CLUSTER
CREATE SEQUENCE
CREATE OPERATOR
CREATE PROCEDURE
CREATE INDEXTYPE

Also, you must have unlimited tablespace within the resource role. 

You can use the following syntax to give resource privileges:

SQL> grant resource to user;