User Schemasının index yapısını çıkartmak

1.AMAÇ VE KAPSAM

Bir user schemasının butun ozellikleriyle index yapisini cikartmaya yarar.

2.UYGULAMA

2.1 Sqlscriptimizi yaratiyoruz.

vi /home/oracle/Desktop/olanindexlerigoster.sql

rem ———————————————————————–
rem # Sqlplus / as sysdba olarak giriyoruz
rem # Hangi schemanin index yapisini ogrenmek istiyorsak o schemayi sordugunda
rem # yazıyoruz script sizin uygulamayi calistirdiginiz klasorde
rem # ind_.lst isminde bir klasor yaratacaktir
rem ———————————————————————–

set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool ind_&&SCHEMA_NAME
set termout off
col y noprint
col x noprint
col z noprint
select ‘rem **** Create Index DDL for ‘||chr(10)||
‘rem **** ‘||username||””||’s tables’||chr(10)||chr(10)
from dba_users
where username = upper (‘&&1’)
/
select table_name z,
index_name y,
-1 x,
‘create ‘ || rtrim(decode(uniqueness,’UNIQUE’,’UNIQUE’,null))
|| ‘ index ‘ ||
rtrim(index_name)
from dba_indexes
where table_owner = upper(‘&&1’)
union
select table_name z,
index_name y,
0 x,
‘on ‘ ||
rtrim(table_name) ||
‘(‘
from dba_indexes
where table_owner = upper(‘&&1′)
union
select table_name z,
index_name y,
column_position x,
rtrim(decode(column_position,1,null,’,’))||
rtrim(column_name)
from dba_ind_columns
where table_owner = upper(‘&&1’)
union
select table_name z,
index_name y,
999999 x,
‘)’ || chr(10)
||’unrecoverable ‘ || chr(10)
||’STORAGE(‘ || chr(10)
||’INITIAL ‘ || initial_extent || chr(10)
||’NEXT ‘ || next_extent || chr(10)
||’MINEXTENTS ‘ || ‘1’ || chr(10)
||’MAXEXTENTS ‘ || max_extents || chr(10)
||’PCTINCREASE ‘|| ‘0’ ||’)’ || chr(10)
||’INITRANS ‘ || ini_trans || chr(10)
||’MAXTRANS ‘ || max_trans || chr(10)
||’PCTFREE ‘ || ‘0’ || chr(10)
||’TABLESPACE ‘ || tablespace_name ||chr(10)
||’PARALLEL (DEGREE ‘ || DEGREE || ‘) ‘ || chr(10)
||’/’||chr(10)||chr(10)
from dba_indexes
where table_owner = upper(‘&&1’)
order by 1,2,3
/

2.2 Sysdba olarak bağlanıp scripti çalıştırmamız gerekiyor, script calıstırıldıktan sonra hangi userın schemasını inceliyeceksek onu yazıyoruz.

[oracle@localhost ~]$ sqlplus / @/home/oracle/Desktop/olanindexlerigoster.sql

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Jun 15 00:16:34 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enter value for schema_name: schemanin_ismi
SQL> exit

2.3 Sqlplusa bağlanmadan önceki klasorunuzde otomatik olarak ind_.lst isimli bir dosya olusacaktir