Oracle日常维护操作【转载】

一．表空间

1.创建表空间

 

create tablespace xjzhang datafile 'd:\oracle\product\10.1.0\oradata\xjzhang.dbf' size 20m autoextend off

segment space management auto

 

autoextend off—不自动扩展

segment space management auto—自动段管理推荐

 

 1.1 创建临时表空间

 

create temporary tablespace temp1 tempfile 'd:\oracle\product\10.1.0\oradat

a\auc\temp.dbf' size 5m autoextend off

 

用于存放扩展信息

 

1.2 创建UNDO 表空间

 

 

create undo tablespace zzq_undo1 datafile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\AUC\ZZQ_UNDO2.DBF' size 20m autoextend off

 

 

2.增加表空间

 

alter tablespace BCS_LOG add datafile '/disk_array/oracle/oracle/oradata/auc/BCS_LOG13.dbf' size 1024m autoextend off

 

--路径根据操作系统的不听进行填写

3.删除表空间

 

drop tablespace zzq ---- 直接删除表空间，而不删除对应的数据文件

 

drop tablespace zzq INCLUDING CONTENTS ---加上该选项则连同数据文件一起删除了

 

4.查询表空间状态

col tablespace_name for a15

 

select tablespace_name,block_size,status,contents,logging from dba_tablespa

ces;

 

查询结果 STATUS 为 ONLINE 表示为联机状态正常如果为OFFLINE 说明表空间不被使用

 

5.查询数据文件路径

 

select file_id,file_name,tablespace_name,status,bytes from dba_data_files

6.移动表空间中数据文件的路径

 

1.        首先确定数据文件的状态要为OFFLINE

select tablespace_name,status,contents from dba_tablespaces

 

select file_id,file_name,tablespace_name from dba_data_files where file_name like '%ORACLE%' order by file 

---通过该语句查询数据文件的路径

 

2.        将该表空间修改为OFFLINE

alter tablespace USERS offline

 

select tablespace_name,status,contents from dba_tablespaces --查看表空间状态确定修改成功

 

3.        移动数据文件

host copy D:\oracle\product\10.1.0\oradata\auc\USERS01.DBF d:\oracle\product\10.1.0\oradata\aucdbf

 

4.        重新命名该表空间的路径和名称

alter tablespace users rename datafile 'd:\oracle\product\10.1.0\oradata\auc\USERS01.DBF' to 'd:\oracle\pr

oduct\10.1.0\oradata\aucdbf\USERS01.DBF'

 

5.        修改表空间的状态为 ONLINE 状态

alter tablespace users online

 

 

 

 

第二种方法

在数据库位 mount的模式下

1.                               host copy d:\oracle\product\10.1.0\oradata\auc\system01.dbf d:\oracle\product\10.1.0\oradata\aucsystem

2. alter database rename file 'd:\oracle\product\10.1.0\oradata\auc\system01.dbf' to 'd:\oracle\product\10.1.

0\oradata\aucsystem\system01.dbf'

 

3.alter database open

 

4. select file_name,tablespace_name from dba_data_files

 

7.移动表和索引到其他表空间

 

1.        查询该对象存放在哪个表空间

select segment_name,tablespace_name,extents,blocks from dba_segments where owner='ZHANG'

 

2.查询该对象是索引，还是表

select object_id,object_name,object_type,status,created from dba_objects where owner='ZHANG'

 

3.查询索引或者表存放在哪个表空间

select index_name,table_name,tablespace_name,status from dba_indexes where owner='ZHANG'

 

4.移动表到另一个表空间

alter table zhang.zzq_1 move tablespace zhang_zzq

 

6.        查询该表是否移动到该表空间

select segment_name,tablespace_name,extents,blocks from dba_segments where owner='ZHANG'

7.        检查表是否有效

select object_id,object_name,object_type,status,created from dba_objects where owner='ZHANG'

状态为 VALID 是有效

 

8.        重建索引并且将索引移动到另一个表空间

alter index zhang.zzq_index rebuild tablespace zhang_zzq

 

9.        查询索引对应的表空间

select index_name,table_name,tablespace_name,status from dba_indexes where owner='ZHANG'

 

 

8.查看表空间的使用率

有两个脚本都可以使用

1.

 

col f.tablespace_name format a15

col d.tot_grootte_mb format a10

col ts-per format a8

select upper(f.tablespace_name) "TS-name",

       d.tot_grootte_mb "TS-bytes(m)",

       d.tot_grootte_mb - f.total_bytes "TS-used (m)",

       f.total_bytes "TS-free(m)",

       to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,

                     2),

               '990.99') "TS-per"

         from (select tablespace_name,

               round(sum(bytes) / (1024 * 1024), 2) total_bytes,

               round(max(bytes) / (1024 * 1024), 2) max_bytes

          from sys.dba_free_space

         group by tablespace_name) f,

       (select dd.tablespace_name,

               round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb

          from sys.dba_data_files dd

         group by dd.tablespace_name) d

where d.tablespace_name = f.tablespace_name

order by 5 desc

 

 

2.

 

SELECT D.TABLESPACE_NAME,

       SPACE "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

       SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",

       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",

       FREE_SPACE "FREE_SPACE(M)"

 FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

               SUM(BLOCKS) BLOCKS

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) D,

       (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE

          FROM DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) F

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL --if have tempfile

SELECT D.TABLESPACE_NAME,

       SPACE "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

       USED_SPACE "USED_SPACE(M)",

       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",

       NVL(FREE_SPACE, 0) "FREE_SPACE(M)"

 FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

               SUM(BLOCKS) BLOCKS

          FROM DBA_TEMP_FILES

         GROUP BY TABLESPACE_NAME) D,

       (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

          FROM V$TEMP_SPACE_HEADER

         GROUP BY TABLESPACE_NAME) F

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

 ORDER BY 5 DESC

 

二．用户和权限

 

1.创建用户

create user yqm identified by ora1234 default tablespace ts_yqm

temporary tablespace ts_temp_yqm

 

创建用户 yqm 密码为 ora123 默认的表空间维 ts_yqm 默认的临时表空间为 ts_yqm

默认的 临时表空间维 ts_temp_yqm

2.修改用户的密码

alter user yqm identified by yqm1234

 

 

3.给用户授权

grant connect to yqm --授予用户 connect 的权限

 

grant dba to yqm 授予用户 DBA 的权限

 

revoke dba from yqm --收回用户DBA 的权限

 

grant select on scott.emp to kxht   --授予用户能查询SCOTT 下的 EMP 表的权限

 

grant select any table to solo --授予用户能查询所有表的权限

grant delete any table to solo

grant create any table to solo

 

 

 

4. 查询数据库系统上有多少用户，文件名和创建时间

 

select username,created from dba_users

三．归档和非归档模式

1.查看数据库的归档模式

在数据库正常启动的模式下

 

Archive log list

 

2.修改数据库的归档模式

 

在数据库为 mount 的模式下进行修改

alter database archivelog   --将非归档模式修改为归档模式

 

alter system set log_archive_dest_1='location=d:\oracle\log' scope=spfile

修改数据库的归档日志路径

 

四．日志文件

1.查询日志文件信息

Select * from v$logfile

 

SQL> desc v$logfile;

 名称                                      是否为空? 类型

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

 GROUP#                                             NUMBER

 STATUS                                             VARCHAR2(7)

 TYPE                                               VARCHAR2(7)

 MEMBER                                             VARCHAR2(513)

 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

 

可以查询出日志文件属于哪个组，日志文件的路径

1.1查询日志文件大小

select bytes/1024/1024 from v$log   --查询日志文件的大小

 

1.2 日志切换

alter system switch logfile    ---手工切换日志

 

1.3 重做日志文件详细

 

SQL> select group#,members,bytes,status,archived from v$log;

 

 

查询结果：

 

    GROUP#    MEMBERS      BYTES STATUS           ARC

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

         1          2   10485760 CURRENT          NO

         2          2   10485760 INACTIVE         YES

         3          1   10485760 INACTIVE         YES

         4          1   10485760 INACTIVE         YES

 

结果显示，有四组重做日志，1组2组有两个成员，3组4组有一个成员，大小10485760 字节。

正在使用的是第一组日志，（状态为CURRENT),1组没有归档，2，3，4组都归档了。(ARC 为NO，没有归档，YES，为归档）CURRENT 表示正在被使用

 

 

2.增加日志文件配置信息

 

 

 

alter database add logfile group 4 'd:\oracle\product\10.1.0\oradata\auc\re

do04' size 10m

 

查询出原来日志组中日志成员的大小增加日志组日志成员的大小和原有的日志成员大小一致

 

 

3.增加日志成员

 

 

alter database add logfile member 'd:\oracle\product\10.1.0\oradata\auc\red

o011' to group 1

 

为日志一组增加一个日志成员

 

4.删除一组日志

alter database drop logfile group 4

 

 

五．密码文件

1.创建密码文件

 

SQL> host orapwd file=d:\oracle\product\10.1.0\db_1\dbs\initdw.ora password=oracle entries=5

 

 

六．参数文件（spfile pfile）

1.查看数据库使用参数文件（SPFILE 还是PFILE）

 

Show parameter spfile

 

查询结果如果VALUE 有路劲的话说明数据库的参数文件使用的是 SPFILE

 

2.创建SPFILE

 

Create spfile from pfile='D:\oracle\product\10.1.0\admin\auc\pfile\init.ora.1111200818

5256'

 

当数据库启动使用 PFILE 启动的时候通过以上方式创建 SPFILE

 

3.通过PFILE 启动数据库

 

startup pfile='D:\oracle\product\10.1.0\admin\auc\pfile\init.ora.1111200818

5256';

 

 

七．statspack

1.安装STATSPACK

@?/rdbms/admin/spcreate.sql

 

 

2.数据采集

 

　　采样数据

 

　　SQL> exec statspack.snap

 

　　后隔几分钟后再次采样数据

 

　　SQL> exec statspack.snap

 

　　生成报表

 

　　SQL>@?/rdbms/admin/spreport.sql

 

3.设置自动快照

 

\oracle\product\10.1.0\db_1\rdbms\admin\spauto.sql

 

4.设置数据采集的时间

 

修改该脚本中的内容，

variable jobno number;

variable instno number;

begin

    select instance_number into :instno from v$instance;

    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

    commit;

end;

/

主要是 24 系统默认的是一个小时自动执行一次，如果设置为半个小时执行一次的话，就将24修改为48 就可以了

 

-----------

然后执行

@?/rdbms/admin/spauto

 

 

八．ORACLE信息查询

1.查询ORACLE数据库的名字，创建日期

 

 

select name,created,log_mode,open_mode from v$database

 

2. 查询ORACLE所在操作系统的主机名，实例名，版本

 

select host_name,instance_name,version from v$instance

 

 

3.查询ORACLE数据库系统版本详细信息

 

 

select * from v$version

 

 

九．控制文件

1.查询控制文件

 

 col name for a45

select * from v$controlfile

 

2.备份控制文件

 

alter database backup controlfile to trace

 

备份控制文件为 TRC 文件在 BDUMP 中可以找到

 

 

十．索引

1.创建普通索引

 

create index zhang.zzq_1_index on zhang.zzq_1(a3) pctfree 25 storage (initial 500k) tablespace zzq_index

 

2.创建位图索引

create bitmap index zhang.zzq_2_index on zhang.zzq_2(aname) pctfree 25 storage (initial 500k) tablespace zzq_index

 

3.查询索引所在的表，表空间，索引类型

 

SQL> col tablespace_name for a15

SQL> select index_name,index_type,table_name,tablespace_name,uniqueness,status from dba_indexes where owner='ZHANG';

 

INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     TABLESPACE_NAME UNIQUENES STATUS

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

ZZQ_2_INDEX                    BITMAP          ZZQ_2                          ZZQ_INDEX       NONUNIQUE VALID

BIN$JzT/4eOlRteD8fJ2TYznbw==$0 NORMAL          BIN$uZNQmZH5SSa6pO3YXAGNLA==$0 ZHANG_ZZQ       NONUNIQUE VALID

ZZQ_1_INDEX                    NORMAL          ZZQ_1                          ZZQ_INDEX       NONUNIQUE VALID

 

查询结果可以看出，索引zzq_2_index 是BITMAP 索引，位于表zzq_2，位于ZZQ_INDEX表空间  是非唯一索引（NONUNIQUE），状态 VALID 表示正常

                  索引ZZQ_1_INDEX 是普通索引，位于表ZZQ_1 位于ZZQ_INDEX 表空间  是非唯一索引（NONUNIQUE），状态 VALID 表示正常

 

 

4.查询索引所在的列

 

 

SQL> select index_name,table_name,column_name,index_owner,table_owner from dba_ind_columns where table_owner='ZHANG';

 

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME     INDEX_OWNER     TABLE_OWNER

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

ZZQ_2_INDEX                    ZZQ_2                          ANAME           ZHANG           ZHANG

BIN$JzT/4eOlRteD8fJ2TYznbw==$0 BIN$uZNQmZH5SSa6pO3YXAGNLA==$0 NAME_ID         ZHANG           ZHANG

ZZQ_1_INDEX                    ZZQ_1                          A3              ZHANG           ZHANG

 

查询结果看出，ZZQ_2_INDEX 该索引在表ZZQ_2中，所在的列为ANAME，所在的用户为ZHANG。

十一.主键

1.定义主键

 

定义zzq_3 表中 A1字段为主键约束主键名称为 A3_primary_key

 

SQL> alter table zhang.zzq_3 ADD constraint zzq_3_primary_key primary key (a1) deferrable using index tablespace zzq_primary_key;

 

该错误是主键约束

ORA-00001: 违反唯一约束条件

 

2.查询主键索引

 

SQL> col segment_name for a20

SQL> select segment_name,segment_type,tablespace_name from dba_segments where tablespace_name='ZZQ_PRIMARY_KEY'

 

 

3.查询约束信息

 

SQL> select constraint_name,table_name,constraint_type,status,deferred,validated from dba_constraints where owner='ZHANG';

 

CONSTRAINT_NAME TABLE_NAME      CONSTRAINT_TYPE      STATUS          DEFERRED

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

VALIDATED

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

ZZQ_3_PRIMARY_K ZZQ_3           P                    ENABLED         IMMEDIATE

EY

 

 

状态为 ENABLED 表示主键有效（主键打开），状态为DISABLED，表示主键关闭

 

 

4.禁止约束

 

 

alter table zhang.zzq_3 disable novalidate constraint zzq_3_primary_key;

 

 

5.开启主键

 

 

alter table zhang.zzq_3 enable novalidate constraint zzq_3_primary_key