生产库中索引和表是IO分离存储的.索引不需要备份,而且表的更改 索引也会联动更改,即使备份也毫无意义
即使索引表空间损坏,索引的定义也存储在数据字典中,取出元数据重建即可
如何取元数据
dbms_metadata.get_ddl('类型','对象','模式')
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL>
取scott用户下所有表的元数据
SQL> select (select dbms_metadata.get_ddl('TABLE',table_name,owner) from dual)
from dba_tables where owner='SCOTT';
取scott用户下所有索引的元数据
SQL> select (select dbms_metadata.get_ddl('INDEX',index_name,owner) from dual)
from dba_indexes where owner='SCOTT';
SQL> create tablespace ind_tbs datafile '/db254/ind_tbs01.dbf' size 10M;
SQL> create index i1 on scott.emp(ename) tablespace ind_tbs;
SQL> create index i2 on scott.emp(sal) tablespace ind_tbs;
SQL> create index i3 on scott.emp(job) tablespace ind_tbs;
SQL> select segment_type,segment_name,owner from dba_segments where tablespace_name='IND_TBS';
SEGMENT_TYPE SEGMENT_NAME OWNER
------------------ ------------------------- ------------------------------
INDEX I1 SYS
INDEX I2 SYS
INDEX I3 SYS
SQL> select * from scott.emp where sal < 1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 809 20
7900 JAMES CLERK 7698 03-DEC-81 959 30
SQL>
SQL> ! cp 1.txt /db254/ind_tbs01.dbf
SQL> alter system switch logfile;
System altered.
SQL> select * from scott.emp where sal <1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 809 20
7900 JAMES CLERK 7698 03-DEC-81 959 30
SQL> alter system checkpoint;
System altered.
SQL> select * from scott.emp where sal <1000;
select * from scott.emp where sal <1000
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/db254/ind_tbs01.dbf'
6号文件有问题 6号文件在哪个表空间
SQL> select tablespace_name ,file_id from dba_data_files where file_id=6;
TABLESPACE_NAME FILE_ID
--------------- ----------
IND_TBS 6
存的是什么内容
SQL> select segment_type,segment_name,owner from dba_segments where tablespace_name='IND_TBS';
SEGMENT_TYPE SEGMENT_NAME OWNER
------------------ ------------------------- ------------------------------
INDEX I1 SYS
INDEX I2 SYS
INDEX I3 SYS
SQL>
SQL> 获取元数据
SQL> SELECT DBMS_METADATA.GET_DDL(segment_type,segment_name,owner)||';' FROM dba_segments where tablespace_name='IND_TBS';
DBMS_METADATA.GET_DDL(SEGMENT_TYPE,SEGMENT_NAME,OWNER)||';'
--------------------------------------------------------------------------------
CREATE INDEX "SYS"."I1" ON "SCOTT"."EMP" ("ENAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IND_TBS"
;
CREATE INDEX "SYS"."I2" ON "SCOTT"."EMP" ("SAL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IND_TBS"
;
CREATE INDEX "SYS"."I3" ON "SCOTT"."EMP" ("JOB")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IND_TBS"
;
删除原表空间
SQL> DROP TABLESPACE IND_TBS;
DROP TABLESPACE IND_TBS
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL> DROP TABLESPACE IND_TBS INCLUDING CONTENTS;
Tablespace dropped.
SQL> CREATE TABLESPACE IND_TBS DATAFILE '/db254/ind_tbs01.dbf' size 10M reuse;
Tablespace created.
执行元数据
SQL> select segment_type,segment_name,owner from dba_segments where tablespace_name='IND_TBS';
SEGMENT_TYPE SEGMENT_NAME OWNER
------------------ ------------------------- ------------------------------
INDEX I1 SYS
INDEX I2 SYS
INDEX I3 SYS
SQL> select * from scott.emp where sal < 1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 809 20
7900 JAMES CLERK 7698 03-DEC-81 959 30
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29523859/viewspace-1102666/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29523859/viewspace-1102666/