1.AMAÇ VE KAPSAM
Bir user schemasının butun ozellikleriyle index yapisini cikartmaya yarar.
2.UYGULAMA
2.1 Sqlscriptimizi olusturuyoruz.
vi /home/oracle/Desktop/olanindexlerigoster.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
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_<schema_ismi>.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.
1 2 3 4 5 6 7 8 |
[oracle@localhost ~]$ <span style="color: #000000;">sqlplus / @/home/oracle/Desktop/olanindexlerigoster.sql</span> 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: <span style="color: #000000;">schemanin_ismi</span> SQL> <span style="color: #000000;"> exit</span> |
2.3
Bir Cevap Yazın