oracle dba 常用命令

1最基本的

oracle dba 常用命令

 

1 运行SQLPLUS工具

sqlplus

 

2 以OS的默认身份连接

/ as sysdba

 

3 显示当前用户名

show user

 

4 直接进入SQLPLUS命令提示符

sqlplus /nolog

 

5 在命令提示符以OS身份连接

connect / as sysdba

 

6 以SYSTEM的身份连接

connect system/xxxxxxx@服务名

 

7 显示当然用户有哪些表

select * from tab;

 

8 显示有用户名和帐户的状态

select username,account_status from dba_users;

 

9 将SCOTT帐号解锁(加锁)

alter user scott account unlock(lock);

 

10 以SCOTT的身份连接并且查看所属表

connect scott/tiger

select * from tab;

 

 

 

11 查看EMP的表结构及记录内容

desc emp

select empno,ename from emp;

 

12 以OS的身份登看SGA,共享池,CACHE的信息

connect / as sysdba

show sga

select name,value/1024/1024 from v$sga;

show parameter shared_pool_size

select value/1024/1024 from v$parameter where name ='shared_pool_size';

show parameter db_cache_size

select value/1024/1024 from v$parameter where name ='db_cache_size';

 

13 查看所有含有SIZE的信息

show parameter size

bitmap_merge_area_size integer 1048576

create_bitmap_area_size integer 8388608

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_block_size integer 4096

db_cache_size big integer 33554432

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

 

NAME TYPE VALUE

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

global_context_pool_size string

hash_area_size integer 1048576

java_max_sessionspace_size integer 0

java_pool_size big integer 33554432

large_pool_size big integer 8388608

max_dump_file_size string UNLIMITED

object_cache_max_size_percent integer 10

object_cache_optimal_size integer 102400

olap_page_pool_size integer 33554432

oracle_trace_collection_size integer 5242880

parallel_execution_message_size integer 2148

 

NAME TYPE VALUE

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

sga_max_size big integer 143727516

shared_pool_reserved_size big integer 2516582

shared_pool_size big integer 50331648

sort_area_retained_size integer 0

sort_area_size integer 524288

workarea_size_policy string AUTO

 

 

14 显示SGA的信息

select * from v$sgastat;

POOL NAME BYTES

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

fixed_sga 453532

buffer_cache 33554432

log_buffer 656384

shared pool subheap 46884

shared pool KGK heap 3756

shared pool KQR M PO 586792

shared pool KQR S PO 180232

shared pool KQR S SO 5128

shared pool sessions 410720

shared pool sql area 2144664

shared pool 1M buffer 2098176

 

POOL NAME BYTES

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

shared pool KGLS heap 901756

shared pool parameters 8352

shared pool free memory 38687204

shared pool PL/SQL DIANA 420816

shared pool FileOpenBlock 695504

shared pool PL/SQL MPCODE 135692

shared pool library cache 2985576

shared pool miscellaneous 4889396

shared pool MTTR advisory 21164

shared pool PLS non-lib hp 2068

shared pool XDB Schema Cac 4966300

 

POOL NAME BYTES

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

shared pool joxs heap init 4220

shared pool kgl simulator 563260

shared pool sim memory hea 44184

shared pool table definiti 1728

shared pool trigger defini 1896

shared pool trigger inform. 1140

shared pool trigger source 448

shared pool type object de 69120

shared pool Checkpoint queue 282304

shared pool VIRTUAL CIRCUITS 265160

shared pool dictionary cache 1610880

 

POOL NAME BYTES

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

shared pool KSXR receive buffers 1033000

shared pool character set object 323724

shared pool FileIdentificatonBlock 323292

shared pool message pool freequeue 834752

shared pool KSXR pending messages que 841036

shared pool event statistics per sess 1718360

shared pool fixed allocation callback 180

large pool free memory 8388608

java pool free memory 33554432

 

已选择42行。

 

15 显示PGA的信息

select * from v$pgastat;

NAME VALUE UNIT

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

aggregate PGA target parameter 16777216 bytes

aggregate PGA auto target 7640064 bytes

global memory bound 838656 bytes

total PGA inuse 8293376 bytes

total PGA allocated 13106176 bytes

maximum PGA allocated 22090752 bytes

total freeable PGA memory 0 bytes

PGA memory freed back to OS 0 bytes

total PGA used for auto workareas 0 bytes

maximum PGA used for auto workareas 4096 bytes

total PGA used for manual workareas 0 bytes

 

NAME VALUE UNIT

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

maximum PGA used for manual workareas 4096 bytes

over allocation count 0

bytes processed 8783872 bytes

extra bytes read/written 0 bytes

cache hit percentage 100 percent

 

已选择16行。

 

17 在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入环境变量,以后每次启动生效

define _editor=vi

set line 2000

 

 

18 将当前命令随加到文件中

save c:a.sql append

 

19 将指定文件的命读出缓冲区

get c:a.sql

 

20 执行脚本语句

@ c:a.sql

 

21 将输入保存到指定文件中

spool c:O.LOG

select * from v$sga;

spool off

 

22 设定行大小

set linesize 2000

 

23 设定页大小

set pagesize 10

 

24 设定字符列格式

col ename format a30

 

25 设定数字列格式

col sal format 999,999.999

 

26 10G查看文件$ORACLE_HOME/install/protlist显示端口

http://127.0.0.1:5560/isqlplus

9i查看文件$ORACLE_HOME/Apache/Apache/ports.ini显示端口

http://127.0.0.1:7778/isqlplus

http://127.0.0.1:7778/isqlplusdba

 

27 启动Oracle 9i监听程序

Oracle的监听程序主要是为客户端的连接提供接口

$ lsnrctl start

 

28 关闭Oracle 9i监听程序

$ lsnrctl stop

 

29 启动Oracle Web Server

$ cd $ORACLE_HOME/Apache/Apache/bin

$ ./startJServ.sh

/database/oracle/product/9i/Apache/Apache/bin/apachectl start: httpd started

 

30 关闭Oracle Web Server

$ cd $ORACLE_HOME/Apache/Apache/bin

$ ./stopJServ.sh

/database/oracle/product/9i/Apache/Apache/bin/apachectl stop: httpd stopped

 

31 启动Oracle Web Server后默认的端口号是7777

unix

cd $ORACLE_HOME/Apache/Apache/bin/

htpasswd $ORACLE_HOME/sqlplus/admin/iplusdba.pw admin

windows

cd D:oracleora92ApacheApachebin

htpasswd D:oracleora92/sqlplus/admin/iplusdba.pw admin

New password: *****

Re-type new password: *****

Adding password for user admin

 

32 pfile: $ORACLE_BASE/admin/实例名/Pfile

spfile: $ORACLE_HOME/dbs

监听器配置文件:listener.ora: $ORACLE_HOME/network/admin/

tnsnames.ora: $ORACLE_HOME/network/admin/

 

33 指定用户的表空间

SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='用户名'

 

 

34 当前用户

select user from dual;show user

 

35 当前用户的缺省表空间

SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=(select user from dual);

 

37 显示正在使用的初始化参数文件

show parameter spfile

 

NAME TYPE VALUE

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

spfile string %ORACLE_HOME%DATABASESPFILE%

ORACLE_SID%.ORA

38 用SPfile转Pfile

create pfile='c:init.ora' from spfile;

create pfile from spfile;

UNIX将生成在$ORACLE_HOME/dbs目录下

WINDOWS将生成在$ORACLE_HOME/DATABASE目录下

 

39 显示cache的值

show parameter cache

前面两个下划线的是动态调整的参数

前面一个下划线的是内部参数

 

40 显示正在使用的参数和文本参数值

select * from v$parameter;

select * from v$spparameter;

 

41 显示动态性能视图的结构

desc v$parameter;

名称

NUM

NAME

TYPE

VALUE

ISDEFAULT

ISSES_MODIFIABLE

ISSYS_MODIFIABLE

ISMODIFIED

ISADJUSTED

DESCRIPTION

UPDATE_COMMENT

 

42 有条件的显示动态参数

select * from v$parameter where name like '%db_cache%';

 

43 修改参数语句

alter system set sga_max_size=256m scope=spfile; &&写入SPFILE,重启生效

alter system set sga_max_size=256m scope=memory; &&写入内存区,立即生效

alter system set sga_max_size=256m scope=both; &&即写入内存区,又写入SPFILE,默认为BOTH.

 

44 动态调整large_pool_size的语句

alter system set large_pool_size=16M;

 

45 当前正在使用的SGA的大小

select sum(bytes)/1024/1024 from v$sgastat;

 

46 设置db_cache_size的大小保存在SPFILE

alter system set db_cache_size=128m scope=spfile;

 

47 设置shared_pool_size的大小保存在SPFILE中

alter system set shared_pool_size=80m scope=spfile;

 

48 显示PGA的状态

select * from v$pgastat;

show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 16777216

 

49 带PFILE来启动资料库,?代表ORACLE的主目录

startup pfile=?databaseinitmydb.ora

 

50 显示当前数据库的状态,mount的状态下就可以查看数据库的状态

select open_mode from v$database;

 

51 显示当前实例 nomount状态下就可以查看实例

select * from v$instance;

 

52 显示 background_dump_dest的路径 ,

show parameter background_dump_dest

NAME TYPE VALUE

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

background_dump_dest string D:oracleadminthwerpbdump

 

53 显示当有用户的表

select * from user_tables;

 

54 显示当有用户可以访问表

select * from all_tables;

 

55 显示用户为SCOTT的表

select * from dba_tables where wner='SCOTT';

 

56 显示所有用户信息

select * from dba_users;

 

57 建立用户并指定密码

create user edpthw identified by edpthw;

 

58 给用户授权

grant connect,resource to edpthw;

 

59 建立一个表

create table a(a int);

 

60 显示数据字典中表名为USER开头的表名

select table_name from dict where table_name like 'USER%';

 

61 在UNIX下oerr ora 错误号查看错误的帮助信息.

 

62 在unix 查看日志中有哪些错误

grep ORA alert_PROD.log|tail -10

 

63 显示当前实例

SELECT * FROM V$INSTANCE;

 

64 显示当前数据库信息

SELECT * FROM V$DATABASE;

 

65 显示当前ORACLR资料的版本

SELECT * FROM V$VERSION;

 

66 显示当前的选项

SELECT * FROM V$OPTION;

 

67 显示当前实例的初始化参数

SELECT * FROM V$PARAMETER;

 

68 显示数据文件

SELECT * FROM V$DATAFILE;

 

69 显示日志文件

SELECT * FROM V$LOGFILE

 

70 显示控制文件

SELECT * FROM V$CONTROLFILE;

 

71 显示当有有哪些会话

SELECT * FROM V$SESSION;

 

72 显示当前有哪些进程

SELECT * FROM V$PROCESS;

 

73 显示当前后台进程

SELECT * FROM V$BGPROCESS;

 

74 显示SGA的大小

SELECT * FROM V$SGA;

 

75 显示SGA的统计数

SELECT * FROM V$SGASTAT;

 

76 显示PGA的统计数

SELECT * FROM V$PGASTAT;

 

77 显示有哪些控制文件

SHOW PARAMETER CONTROL_FILES;

 

78 增加一个控制文件一定要在NOMOUNT状态下进行.语句如下.

alter system set control_files='D:ORADATAMYDBCONTROL01.CTL',

'D:ORADATAMYDBCONTROL02.CTL',

'D:ORADATAMYDBCONTROL03.CTL',

'E:CONTROL04.CTL' SCOPE=SPFILE;

 

alter system set control_files='D:oracleoradatathwerpCONTROL01.CTL',

'D:oracleoradatathwerpCONTROL02.CTL',

'D:oracleoradatathwerpCONTROL03.CTL',

'D:oracleoradatathwerpCONTROL04.CTL' scope=spfile;

 

79 数据重新启动

STARTUP FORCE

 

80 显示当前控制文件

SELECT * FROM V$CONTROLFILE;

SHOW PARAMETER CONTROL

 

81 删除一个控制文件

alter system set control_files='D:ORADATAMYDBCONTROL01.CTL',

'D:ORADATAMYDBCONTROL02.CTL',

'D:ORADATAMYDBCONTROL03.CTL' SCOPE=SPFILE

 

82 显示当前使用的日志

SELECT * FROM V$LOG;

 

83 显示实例的归档模式

ARCHIVE LOG LIST;

 

84 显示重做日志文件

SELECT * FROM V$LOGFILE;

 

85 手工切换重做日志文件

ALTER SYSTEM SWITCH LOGFILE;

 

86 发出检查点

ALTER SYSTEM CHECKPOINT;

 

87 显示重做日期的历史记录

select * from v$log_history;

 

88 更改会语的日期格式

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

89 增加日志组

ALTER DATABASE ADD LOGFILE GROUP 4 ( 'D:ORADATAMYDBredo4.log') SIZE 10240K;

 

92 删除日志组

ALTER DATABASE DROP LOGFILE GROUP 4 ;

 

 

90 增加日志组成员

ALTER DATABASE ADD LOGFILE MEMBER 'D:ORADATAMYDBredo11.log' TO GROUP 1;

 

91 删除日志组成员

ALTER DATABASE DROP LOGFILE MEMBER 'D:ORADATAMYDBredo14.log' ;

 

 

92 显示系统表空间

select * from dba_tablespaces;

 

93 显示系统表空间所拥有的文件

select * from dba_data_files;

 

94 显示系统临时表空间

select * from dba_temp_files;

 

95 显示表空间的已被使用多少

select tablespace_name,sum(bytes)/1024/1024 m

from dba_data_files group by tablespace_name;

 

96 显示表空间还有多少没有使用

select tablespace_name,

sum(bytes)/1024/1024 m

from dba_free_space group by tablespace_name;

 

97 创建表空间

10G CREATE BIGFILE TABLESPACE "MYTBS1" DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

9I CREATE TABLESPACE "MYTBS1" DATAFILE 'D:oracleORADATAthwerpmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE "MYTBS1" DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M , 'D:ORADATAMYDBmytbs02.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

98 在指字表空间里建表

create table t1 (a int) tablespace mytbs1;

 

99 在指字表空间里建索引

create index t1_ind on t1(a) tablespace mytbs1;

 

100 在指定表空间里增加数据文件

ALTER TABLESPACE "MYTBS1" ADD DATAFILE 'D:ORADATAMYDBmytbs03.dbf' SIZE 100M

 

100 修改表空间的大小

ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS_01.DBF' RESIZE 200M

 

101 表空间改文件的自动扩展

ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

 

102 删除表空间和表空间所属文件

drop tablespace mytbs1 including contents and datafiles;

 

103 建立临时表空间

CREATE SMALLFILE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1M

CREATE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1M

 

104 更改用户的临时表空间

ALTER USER "GARY" TEMPORARY TABLESPACE "MYTEMP"

 

105 设置成默认的表空间

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "MYTEMP"

 

106 创建临时表空间组

ALTER TABLESPACE MYTEMP TABLESPACE GROUP MYTEMP_GROUP

ALTER TABLESPACE TEMP TABLESPACE GROUP MYTEMP_GROUP

 

107 创建重做表空间

CREATE UNDO TABLESPACE "MYUNDO" DATAFILE 'D:ORADATAMYDBmyundo01.dbf' SIZE 200M

 

108 显示重做参数

show parameter undo

 

109 回滚段的使用情况

select * from dba_rollback_segs;

 

110 更改重做表空间

alter system set undo_tablespace=myundo;

 

111 建立一个表

create table a tablespace users as select * from dba_objects;

 

112 插入一些记录

insert into a select * from a;

 

113 有条件查询表名放在哪个表空间

select * from dba_tables where table_name='A' and wner='SYS';

 

114 有条件查询段名放在哪个表空间

select * from dba_segments where segment_name='A' and wner='SYS';

 

115 查询有哪些段类型

select distinct segment_type from dba_segments;

 

116 查询段和表空间的对应关系

select * from dba_extents where segment_name='A' and wner='SYS';

 

117 建立一个16K表空间

CREATE TABLESPACE "MYTBS3" DATAFILE 'D:oracleORADATAthwerpmytbs3.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 16384;

 

118 指字参数建立表

create table b (a int,b varchar2(10)) tablespace users INITRANS 2 PCTFREE 5;

 

119 显示当前所使用的块大小

show parameter block_size;

 

120 释放表的高水位的空间

alter table a deallocate unused;

 

121 高水位前移.速度快

truncate table a;

 

122 分配空间给表

alter table a allocate extent (datafile 'D:ORADATAMYDBusers01.dbf' size 1m);

 

123 指定参数建立表

create table C (a int) tablespace system pctfree 10 pctused 60 storage(freelists 2);

CREATE TABLE "SYS"."D" ( "A" VARCHAR2(10)) TABLESPACE "SYSTEM" PCTFREE 5 PCTUSED 60 INITRANS 2 MAXTRANS 100 STORAGE ( FREELISTS 2)

124 生成一个测试表

create table a tablespace users as select * from dba_objects;

 

125 查看表行的物理地址

select rowid form. a;

 

126 用包的命令查看表行的物理地址

select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM A

 

127 建立表索引

CREATE INDEX A_IND ON A(OBJECT_NAME) TABLESPACE MYTBS2;

 

128 查看表的索引

SELECt * FROM DBA_INDEXES WHERE TABLE_NAME='A';

SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A_IND';

 

129 移动重组表空间

ALTER TABLE A MOVE TABLESPACE USERS;

 

130 重建表索引

ALTER INDEX A_IND REBUILD;

 

131 设置行可以内部移动属性

ALTER TABLE A ENABLE ROW MOVEMENT;

 

132 在原地进行行的移动

ALTER TABLE A SHRINK SPACE;

 

133 查看表所存储的信息

SELECT BYTES FORM. DBA_SEGMENTS WHERE SEGMENT_NAME='A';

SELECT OWNER,SEGMENT_NAME,BYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A';

 

134 截取表

TRUNCATE TABLE A;

 

135 删除表

DROP TABLE A CASCADE CONSTRAINTS;

 

136 显示垃圾桶(10G才有)

show recyclebin

SELECT * FROM DBA_RECYCLEBIN;

SELECT * FROM USER_RECYCLEBIN;

 

137 恢复已删除的表

flashback table a to before drop;

 

138 恢复较早已前的已删除的表

flashback table "BIN$hWrBq1irS0COp10n22NZJg==$0" to before drop rename to a1;

 

139 查询指定表的索引

select * from dba_indexs where table_name='A';

 

140 清除垃圾桶

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;

 

141 删除表并清除垃圾桶(10G)

DROP TABLE A PURGE;

 

142 删除表的列

ALTER TABLE A DROP COLUMN COMMENTS CASCAGE CONSTRAINTES CHECKPOINT 1000;

 

 

143 重命名列

ALTER TABLE A RENAME COLUMN HIRE_DATE TO START_DATE;

 

144 标记不可使用的列

ALTER TABLE A SET UNUSED COLUMN COMMENTS CASCADE CONSTRAINTS;

 

145 删除不再使用的的列

ALTER TABLE A DROP UNUSED COLUMNS CHECKPOINT 10000;

 

146 继续列的删除操作

ALTER TABLE A DROP COLUMNS CONTINUE CHECKPOINT 10000;

 

147 增加表的主索引

ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID);

 

148 删除表的主索引

ALTER TABLE A DROP PRIMARY KEY;

 

149 索引表的主索引并指定索引所使用的表空间

ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID) USING INDEX TABLESPACE USERS;

 

150 建立一个普通索引

CREATE INDEX A_NAME_IND ON A(OWNER,OBJECT_NAME);

 

151 建立一个函数索引

SELECT /*+ INDEX(A A_NAME2_IND) */ * FROM A WHERE UPPER(OBJECT_NAME)='A';

 

152 建立一个位图索引

CREATE BITMAP INDEX A_NAME_BT ON A(OWNER);

 

153 查询数据库的信息

SELECT /*+ INDEX(A A_NAME_BT) */ * FROM A WHERE WNER='SYS' OR WNER='SYSTEM';

SELECT /*+ FULL(A) */ * FROM A WHERE WNER='SYS' OR WNER='SYSTEM';

 

154 生成一个脚本来建立索引重整

SELECT 'ALTER INDEX '||INDEX_NAME||' COALESCE;' FROM DBA_INDEXES WHERE TABLE_NAME='A' AND WNER='SYS'

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ONLINE;' FROM DBA_INDEXES WHERE TABLE_NAME='A' AND WNER='SYS'

 

155 显示所有用户的信息

SELECT * FROM DBA_USERS;

 

156 显示当前数据库的用户

SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;

 

160 建立一个新用户

create user edpthw identified by "123456"

default tablespace users

temporary tablespace temp

quota unlimited on users

quota 10m on mytbs2;

 

161 查询限额信息

select * from dba_ts_quotas;

select * from user_ts_quotas;

 

162 用户改密码

alter user edpthw identified by edpthw;

 

163 锁用户

alter user edpthw account lock;

 

164 使密码过期,迫使改下一次进入改密码

alter user edpthw password expire;

 

165 取消限额

alter user edpthw quota unlimited on mytbs2;

 

166 删除用户

drop user hmtong cascade;

 

167 授权用户

grant create session,create table,create view to edpthw;

 

168 分别授权

GRANT CREATE ANY TABLE TO "hmtong"

GRANT CREATE TABLE TO "hmtong"

GRANT DROP ANY TABLE TO "hmtong"

GRANT SELECT ANY TABLE TO "hmtong"

 

169 折消授权

REVOKE UNLIMITED TABLESPACE FROM "hmtong"

 

170 对象授权

GRANT SELECT ON "hmtong"."A" TO "edpthw"

 

171 折消对象授权

REVOKE SELECT ON "hmtong"."A" FROM "edpthw"

 

172 取消进入系统直接用默认密码,而一定要自己输入密码才可以进入

unix $ORACLE_HOMENETWORKADMINSQLNET.ORA

windows D:oracleproduct10.1.0Db_1NETWORKADMINSQLNET.ORA

SQLNET.AUTHENTICATION_SERVICES= (NTS,NONE)

修改$ORACLE_HOME/network/admin/sqlnet.ora文件

　　将原有的：SQLNET.AUTHENTICATION_SERVICES= (NTS)

　　改为:SQLNET.AUTHENTICATION_SERVICES= (NONE)

　　或者直接注释：#SQLNET.AUTHENTICATION_SERVICES= (NTS)

 

173 查询Pwfile中存放的用户信息

select * from v$pwfile_users;

 

174 授权sysdba给指定用户

grant sysdba to hmtong;

 

175 取消指定用户的sysdba权限

revoke sysdba from hmtong;

 

176 修改系统的授权的属性

alter system set remote_login_passwordfile=exclusive; 能sysdba登录,能授权

alter system set remote_login_passwordfile=shared scope=spfile; 只能sysdba登录,不能授权

alter system set remote_login_passwordfile=NONE; 取消

 

177 重建口令文件

orapwd file=PWDmydb.ora password=itpub

 

178 创建一个用户

create user hmtong identified by abcdefg;

 

179 授权连接给指定用户

GRANT "CONNECT" TO "hmtong";

GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO HMTONG;

 

180 创建表

create table t1(a int);

create table t2(a int);

 

181 查看系统特权

select * from user_sys_privs;

 

182 查看对象特权

select * from user_tab_privs;

 

183 查看被授予的角色

select * from user_role_privs;

 

184 查看角色的有什么权限

select * from role_sys_privs;

 

185 查看角色的有哪些表

select * from role_tab_privs;

 

186 查看当前会话的权限

select * from session_privs;

 

187 监听器的状态

lsnrctl start

$ORACLE_HOME/network/admin/listener.ora

 

188 监听器的启动和关闭

lsnrctl start [名字]

lsnrctl stop [名字]

 

189 监听器的状态和服务信息

lsnrctl stat

lsnrctl service

 

190 配置监听器的工具

netca

netmgr

 

192 动态注册监听器

alter system register;

 

193 配置监听器的两个参数

show parameter local_listener

show parameter remote_listener

 

194 追加设置一个服务名,服务名可以多个对应一个实例

alter system set service_names=mydb,appdb

 

195 客户连接配置文件

$ORACLE_HOME/network/admin/tnsname.ora

 

196 检查指定的监听器是否存在

TNSPING MYDB

 

 

197 将SQLNET.ORA配置好就可以connectsystem/testdb1@192.168.100.80:1521/testdb1方式连接

SQLNET.AUTHENTICATION_SERVICES= (NTS,NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

 

198 快速重启资料库

shutdown immediate

 

199 将资料库开启到mount的状态

startup mount

 

200 显示数据库的归档还是非归档模式

archive log list

数据库日志模式 存档模式

自动存档 禁用

存档终点 D:oracleora92RDBMS

最早的概要日志序列 64

下一个存档日志序列 69

当前日志序列 69

 

201 在数据库mount的状态下更改数据库的归档模式

alter database archivelog; 归档

alter database noarchivelog; 非归档

 

202 打开数据库

alter database open;

 

203 查看归档的路径的

SQL> show parameter log_archive_dest_x

 

204 动态的配置归档路径在两个不同的目录下同时归档

alter system set log_archive_dest_1='location=e:archive';

alter system set log_archive_dest_2='location=d:archive';

 

205 切换系统的下一个日志文件

alter system switch logfile; (10G就会产生归档文件)

 

206 定义自动归档

alter system set log_archive_start=true scope=spfile; (9i)(10G不用设)

 

207 查看数据库的归档的信息

select * from v$log;

 

208 查看曾经做过哪些归档及路径

select * from v$archived_log;

 

209 查看有哪还没有做归档的信息

select * from v$archive;

 

210 设置归档路径2暂时不归档和启用归档

alter system set log_archive_dest_state_2=defer;

alter system set log_archive_dest_state_2=enable;

 

211 查看最少路径归档数目的参数

show parameter log_archive_min_succeed_dest

 

212 查看归档路径的文件名格式参数

show parameter log_archive_format

 

213 查改归档文件名的格式

alter system set log_archive_format='mydb_%s_%r_%t.log' scope=spfile;

 

214 显示出快速恢复区的大小和路径

show parameter db_recovery_file_dest

show parameter db_recovery_file_dest_size

 

215 更改归档路径到到快速恢复区

alter system set log_archive_dest_1='location=use_db_recovery_file_dest';

 

216 更改快速恢复区的路径

alter system set db_recovery_file_dest='e:recover';

 

217 查看快速恢复区的使用情况

select * from v$recovery_file_dest;

 

218 更改快速恢复区的大小

alter system set db_recovery_file_dest_size=10g;

219 查看数据库的归档模式

select log_mode from v$database;

archive log list

 

220 查看归档文件的归档路径

show parameter log_archive_dest

select * from v$archive_dest;

 

221 查看归档文件

select * from v$archive;

 

222 查看曾经归档过的记录

select * from v$archived_log;

 

223 启动在线备份表空间

alter tablespace users begin backup;

 

224 查询指定表空间的文件路径

select file_name from dba_data_files where tablespace_name='USERS';

 

225 在SQL状态上暂时离开返回到SHELL

host ocopy user*.dbf e:backup

 

226 结束生成备份表空间

alter tablespace users end backup;

 

227 备份数据库的控制文件

alter database backup controlfile to 'e:backupcontrol01.ctl';

 

228 生成创建控制文件的脚本

alter database backup controlfile to trace as 'e:backupcontrol01.sql';

 

229 查询哪个文件需要恢复

select * from v$recover_file;

 

230 查询指定文件号的详细信息

select * from v$datafile where file#=6;

 

231 查询有哪些需要归档

select * from v$recovery_log;

 

232 恢复指字的数据文件

recover datafile 6;

 

233 创建一个例子表

create table a11 tablespace mytbs3 as select * from dba_objects;

 

233 查询有没有指定的文件

select * from dba_extents where file_id=6;

 

224 自动找到归档日志恢复相应数据文件

recover AUTOMATIC datafile 6;

 

225 将一个表空间设置成不可用

ALTER TABLESPACE USERS OFFLINE IMMEDIATE

 

226 恢复指定表空间

RECOVER TABLESPACE USERS;

 

227 将指定的表文件设置成不可用

ALTER DATABASE DATAFILE 6 OFFLINE;

 

228 将指定的表文件设置成在线

ALTER DATABASE DATAFILE 6 ONLINE;

 

229 创建一个测试表

CREATE TABLE TEST (A DATE);

 

230 插入当前系统日期

INSERT INTO TEST VALUES(SYSDATE);

 

231 查询TEST的值

SELECT * FROM TEST;

 

232 在注册表里的设置日期格式

NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

 

233 恢复到指定的时间点

RECOVER DATABASE UNTIL TIME '2006-11-26 10:49:43';

 

234 打开资料库并重置重做日志

alter database open resetlogs;

 

235 恢复控制文件

RECOVER DATABASE using backup controlfile UNTIL cancel;

 

236 追加临时表空间

alter tablespace temp add tempfile 'd:oradatamydbtemp01.dbf' size 10m reuse;

 

237 Rman的连接

connect target /

 

238 显示要备份的数据库文件

report schema;

 

239 在RMAN的指定位置来备份文件

run {

allocate channel ch1 type disk format 'e:rman%U.bak' maxpiecesize=2g;

backup database;

}

 

240 在RMAN的显示威者默认设置

show all;

 

backup database;

 

241 配置缺省的路径

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:RMAN%U.BAK' MAXPIECESIZE 2G;

 

242 清除缺省配置

CONFIGURE CHANNEL DEVICE type DISK clear;

 

243 配置并行数

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

 

244 配置自动备份控制文件

CONFIGURE CONTROLFILE AUTOBACKUP on;

 

245 配置有益度

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

 

246 配置可以恢复到7天内的保留窗口

CONFIGURE RETENTION POLICY TO recovery window of 7;

 

247 备份表空间

backup tablespace users;

 

248 备份数据文件

backup datafile 6;

 

249 备份前一天的重做日志

backup archivelog until time 'sysdate-1';

 

250 备份SP文件

backup spfile;

 

251 备份当前的控制文件

backup current controlfile;

 

252 列出所有的备份集

list backupset ;

 

253 列出所有的汇总备份集

list backupset summary;

 

254 列出具体的备份信息

list backupset 16

 

255 删除备份集13

delete backupset 13;

 

256 不提示删除所有的备份集

delete noprompt backupset;

 

257 凡是USERS的备份都删除

delete backup of tablespace users;

 

258 列出哪些不需要的备份集

report obsolete ;

 

259 边备份边压缩数据库

backup as compressed backupset database;

 

260 增量备份时的0级备份一个表空间

backup incremental level 0 tablespace users;

 

261 2级备份累积备份

backup incremental level 2 cumulative tablespace users;

 

262 删除指定日期的归档日志

DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1/24';

 

 

263 在RMAN恢复指定表空间

RUN {

SQL 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';

RESTORE TABLESPACE USERS;

RECOVER TABLESPACE USERS;

SQL 'ALTER TABLESPACE USERS ONLINE';

}

 

264 在RMAN的恢复控制文件

STARTUP NOMOUNT

RESTORE CONTROLFILE FROM AUTOBACKUP;

ALTER DATABASE MOUNT;

RESTORE DATABASE VALIDATE;

RECOVER DATABASE ;

ALTER DATABASE OPEN RESETLOGS;

 

265 查询数据库的字符集

select * from database_properties where property_name like 'NLS_CHA%';

NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

266 导出数据到一个文件中

exp userid=system/oracle file=full.dmp full=y

exp userid=system/oracle file=full1.dmp,full2.dmp,full3.dmp filesize=2g full=y direct=y log=full.log feedback=10000

 

267 导出指定用户的表

exp userid=system/oracle file=gary.dmp wner=gary,wei direct=y log=gary

exp userid=system/oracle file=gary.dmp wner=gary direct=y log=gary.log

 

268 导出指定表

exp userid=system/oracle file=gary_a.dmp tables=gary.a direct=y log=gary

 

269 导出指定表

exp userid=system/oracle file=gary_a1.dmp tables=gary.a query='where wner=''GARY''' log=gary.LOG

 

270 查看导出的帮助信息

exp -help

 

271 删除gary的用户

drop user gary cascade;

 

272 重建用户GARY

CREATE USER "GARY" PROFILE "DEFAULT" IDENTIFIED BY "GARY" ACCOUNT UNLOCK

 

273 授权用户GARY

GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO "GARY";

 

274 导入用户GARY的数据

imp userid=system/oracle fromuser=gary touser=gary show=y

imp userid=system/oracle fromuser=gary touser=gary file=full.dmp log=imp.log

imp userid=system/oracle fromuser=gary touser=wei file=gary.dmp log=imp.log

 

275 查询状态

select object_name,object_type,owner from dba_objects where status='INVALID';

 

EXEC UTL_RECOMP.RECOMP_PARALLEL

 

276 建立对象,允许可以用这个目录来导入导出

create directory mydir as 'e:expdata';

 

277 授权用户的权限

grant read,write on directory mydir to gary;

 

278 导出数据

expdp userid=gary/gary dumpfile=gary directory=mydir PARALLEL=2 schemas=gary job_name=gary_job

expdp userid=gary/gary attach=gary_job

 

268 查询任务

SELECT * FROM V$DATAPUMP_JOB;

 

269 导入数据

impdp userid=system/oracle dumpfile=gary directory=mydir job_name=gary_job

 

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

一． DayCheckSummaryReport.sh维护内容

 

1.     检查oracle进程 $ps -ef | grep ora_

 

UID    PID     PPID  C     STIME TTY      TIME       CMD

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

oracle  26679     1    0      Dec 01 ?      0:22   ora_snp3_wapmail

  oracle  26677     1    0      Dec 01 ?      0:22   ora_snp2_wapmail

  oracle  26671     1    0      Dec 01 ?      0:00   ora_reco_wapmail

  oracle  26661     1    0      Dec 01 ?      0:01   ora_pmon_wapmail

  oracle  26663     1    0      Dec 01 ?      1:42   ora_dbw0_wapmail

  oracle  26673     1    0      Dec 01 ?      0:18   ora_snp0_wapmail

  oracle  26665     1    0      Dec 01 ?      1:16   ora_lgwr_wapmail

  oracle  26667     1    0      Dec 01 ?      1:18   ora_ckpt_wapmail

  oracle  26669     1    0      Dec 01 ?      0:03   ora_smon_wapmail

  oracle  26675     1    0      Dec 01 ?      0:17   ora_snp1_wapmail

 

2.     检查oracle 共享内存 $ipcs | grep oracle

 

T      ID         KEY          MODE        OWNER        GROUP

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

m     1892     0x7ed96c3c   --rw-r-----    oracle      oinstall

s     786433   0x4b21b1d8   --ra-r-----    oracle      oinstall

s     196610   0x4b21b1d9   --ra-r-----    oracle      oinstall

s     131075   0x4b21b1da   --ra-r-----    oracle      oinstall

 

3.    检查oracle Listener状态

 

1) 切换用户 $su – oracle

2) 执行$lsnrctl status

 

LSNRCTL for Solaris: Version 8.1.7.0.0 - Production on 05-DEC-2006 11:28:28

 

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))

 

STATUS of the LISTENER

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

Alias             LISTENER

Version           TNSLSNR for Solaris: Version 8.1.7.0.0 - Production

Start Date        01-DEC-2006 14:52:22

Uptime            3 days 20 hr. 36 min. 6 sec

Trace Level       off

Security          OFF

SNMP               OFF

Listener Parameter File

                  /u01/app/oracle/product/8.1.7/network/admin/listener.ora

Listener Log File           

/u01/app/oracle/product/8.1.7/network/log/listener.log

 

Services Summary...

  PLSExtProc            has 1 service handler(s)

  wapmail               has 1 service handler(s)

  wapmail               has 1 service handler(s)

 

The command completed successfully

You have new mail in /usr/mail/oracle

 

4.    查看alert日志

1)     $cd /u01/app/oracle/admin/wapmail/bdump

2)     查看alert_wapmail.log

 

5.    查看datafile状态

1)     切换用户 $su – oracle

2)     sqlplus system/Ou03LuDH@wapmail

*如果在命令行编辑状态下，敲错了字符，用右手小键盘的“del”键删除

 

3)     让结果集按行输出（设置每行显示110个字符）

sql>col file_name format a60

sql>col tablespace_name format a30

sql>set line 110

 

4)     查看数据表空间信息及状态（dba_data_files只包含数据库和undo类型）

sql>select tablespace_name,file_name,status from dba_data_files order by tablespace_name;

 

TABLESPACE_NAME            FILE_NAME                 STATUS

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

BOSS_WAPMAIL      /disk1/oracle/oradata/boss_wapmail01.dbf   AVAILABLE

DRSYS             /disk1/oracle/oradata/drsys01.dbf          AVAILABLE

NEWSTA            /disk1/oracle/oradata/newsta01.dbf         AVAILABLE

PERFSTAT          /disk1/oracle/oradata/perfstat01.dbf       AVAILABLE

RBS01             /disk1/oracle/oradata/RBS01.dbf            AVAILABLE

SYSTEM            /disk1/oracle/oradata/system01.dbf         AVAILABLE

TOOLS             /disk1/oracle/oradata/tools01.dbf          AVAILABLE

UNIJAWAP          /disk1/oracle/oradata/UNIJAWAP.dbf         AVAILABLE

USERS             /disk1/oracle/oradata/users01.dbf          AVAILABLE

WAPMAIL           /disk1/oracle/oradata/WAPMAIL.dbf          AVAILABLE

XIAO              /disk1/oracle/oradata/xiao09.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao007.dbf          AVAILABLE

XIAO              /disk1/oracle/oradata/xiao006.dbf          AVAILABLE

XIAO              /disk1/oracle/oradata/xiao005.dbf          AVAILABLE

XIAO              /disk1/oracle/oradata/xiao08.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao07.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao06.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao05.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao04.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao004.dbf          AVAILABLE

XIAO              /disk1/oracle/oradata/xiao001.dbf          AVAILABLE

XIAO              /disk1/oracle/oradata/xiao10.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/XIAO.dbf             AVAILABLE

XIAO              /disk1/oracle/oradata/xiao01.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao02.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao003.dbf          AVAILABLE

XIAO              /disk1/oracle/oradata/xiao03.dbf           AVAILABLE

XIAO              /disk1/oracle/oradata/xiao002.dbf          AVAILABLE

XIAO_INDEX        /disk1/oracle/oradata/xiao_index01.dbf     AVAILABLE

 

5)     查看临时表空间信息

sql>select tablespace_name,file_name,status from dba_temp_files order by tablespace_name

   

TABLESPACE_NAME              FILE_NAME                       STATUS

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

TEMP              /disk1/oracle/oradata/TEMP_01.dbf        AVAILABLE

XIAO_TMP          /disk1/oracle/oradata/XIAO_TMP_02.dbf    AVAILABLE

XIAO_TMP          /disk1/oracle/oradata/XIAO_TMP_01.dbf    AVAILABLE

 

 

6)     查找状态异常的表空间

 

SQL>

select * from

 ( select tablespace_name,file_name,status from dba_data_files

  union select tablespace_name,file_name,status from dba_temp_files

  )  where status not in ('AVAILABLE')

  order by status,tablespace_name,file_name;

 

no rows selected

 

6.    检查控制文件

1)      sql>select name, status from v$controlfile order by name;

 

NAME                                             STATUS

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

/u01/app/oracle/oradata/wapmail/control01.ctl

/u01/app/oracle/oradata/wapmail/control02.ctl

/u01/app/oracle/oradata/wapmail/control03.ctl

 

2)      检查控制表是否有异常

SQL> select name,decode(status,null,'AVAILABLE',status) status from  v$controlfile where status is not null;

 

no rows selected

 

7.    检查redolog文件

1)     查看logfile

Sql>select to_char(group#) group#, member, status

from v$logfile order by group#;

 

GROUP#                   MEMBER                             STATUS

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

1           /u01/app/oracle/oradata/wapmail/redo01.log

2           /u01/app/oracle/oradata/wapmail/redo02.log

3           /u01/app/oracle/oradata/wapmail/redo03.log

4           /u01/app/oracle/oradata/wapmail/redo04.log

 

2)     检查logfile是否有异常

SQL>select to_char(group#) as group#,member,

decode(status,null,'AVAILABLE',status) as status

from v$logfile where status = 'INVALID';

 

no rows selected

 

8.    检查数据库任务队列状态

1)     查看DBA_JOBS表中任务信息

Sql>select to_char(job)job,log_user, priv_user,schema_user,last_date,

 this_date,next_date,broken,what,nls_env,misc_env,instance

from DBA_JOBS;

 

    job           log_user          priv_user         schema_user 

last_date     this_date         next_date 

broken        what                                instance

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

1             WAPMAIL           WAPMAIL           WAPMAIL

05-DEC-06     06-DEC-06

N             P_WAPMAIL_HIH_INCHARGEREPORT;       0

 

2             WAPMAIL          WAPMAIL           WAPMAIL

05-DEC-06     06-DEC-06

N             P_WAPMAIL_HIH_CHARGEREPORT;        0

 

3             WAPMAIL          WAPMAIL           WAPMAIL

05-DEC-06     06-DEC-06

N             P_WAPMAIL_HIH_STATISTIC;           0

 

21            XIAO             XIAO              XIAO

05-DEC-06     06-DEC-06

N             p_mail_reg_err;                    0

 

71            PERFSTAT         PERFSTAT          PERFSTAT

12-APR-06     01-JAN-00

Y             statspack.snap;                    1

 

70            XIAO             XIAO              XIAO

05-DEC-06     06-DEC-06

N             p_make_send_cnt;                   0

 

2)     检查dba_jobs中是否有执行异常的任务

sql>SELECT to_char(JOB) as job, LOG_USER,

decode(BROKEN,'N','AVAILABLE','Y','NOT AVAILABLE',BROKEN) as BROKEN,

to_char(LAST_DATE,'yyyy-mm-dd hh24:mi:ss') as LAST_DATE,

to_char(NEXT_DATE,'yyyy-mm-dd hh24:mi:ss') as NEXT_DATE

FROM DBA_JOBS

where BROKEN != 'N'

order by BROKEN;

 

JOB  LOG_USER     BROKEN           LAST_DATE              NEXT_DATE

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

71   PERFSTAT  NOT AVAILABLE   2006-04-12 09:00:34   4000-01-01 00:00:00

 

9.    检查object状态

1)     查看object所属用户

Sql>select owner from dba_objects group by owner;

 

OWNER

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

AURORA$JIS$UTILITY$

BOSS_EXCA

CTXSYS

DBSNMP

DUTYVISITDBA

MDSYS

NEWSTA

ORDPLUGINS

ORDSYS

OSE$HTTP$ADMIN

OUTLN

PERFSTAT

PUBLIC

ROLETEST

SURVEY

SYS

SYSTEM

TEMP_IVR

WAPMAIL

WAPMAIL_156

WAPMAIL_RO

WAPMAIL_RW

XIAO

 

2)     查看object类型

Sql> select object_type from dba_objects group by object_type;

 

OBJECT_TYPE

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

CLUSTER

CONSUMER GROUP

DATABASE LINK

FUNCTION

INDEX

INDEXTYPE

JAVA CLASS

JAVA DATA

JAVA RESOURCE

JAVA SOURCE

LIBRARY

LOB

OPERATOR

PACKAGE

PACKAGE BODY

PROCEDURE

QUEUE

RESOURCE PLAN

SEQUENCE

SYNONYM

TABLE

TRIGGER

TYPE

TYPE BODY

VIEW

 

3)     查看数据对象信息

Sql>select * from dba_objects where owner=’WAPMAIL’ and object_type=’PROCEDURE’;

 

OWNER         OBJECT_NAME        SUBOBJECT_NAME 

OBJECT_ID     DATA_OBJECT_ID     OBJECT_TYPE     

CREATED       LAST_DDL_TIME      TIMESTAMP

STATUS        TEMPORARY          GENERATED                SECONDARY   

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

WAPMAIL       P_WAP_FREE                          

25144                             PROCEDURE      

04-SEP-04     30-JUN-05           2005-03-01:17:24:38

VALID         N                   N                        N

 

WAPMAIL       P_WAP_NEW_REG

25131                             PROCEDURE         

04-SEP-04     30-JUN-05           2004-12-25:03:09:14

VALID         N                   N                        N

 

4)     检查异常对象

Sql> select owner,object_name,object_type,status,

to_char(created,'yyyy-mm-dd hh24:mi:ss') as created,

to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') as last_ddl_time

from dba_objects

where status = 'INVALID'

and sysdate - last_ddl_time  < 60

order by owner,object_type;

 

OWNER         OBJECT_NAME                  OBJECT_TYPE

STATUS        CREATED                      LAST_DDL_TIME

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

BOSS_EXCA    BOSSPK_WAPMAIL_INTEGRATION    PACKAGE BODY      

INVALID      2006-10-10 14:20:23           2006-10-10 14:20:25

 

10.检查用户状态

1)     查看用户信息

Sql>

select username,account_status,lock_date,expiry_date,

default_tablespace,temporary_tablespace,profile,

initial_rsrc_consumer_group,external_name

    from dba_users;

 

USERNAME                      ACCOUNT_STATUS 

PROFILE                       LOCK_DATE                     EXPIRY_DATA

DEFAULT_TABLESPACE            TEMPORARY_TABLESPACE  

INITIAL_RSRC_CONSUMER_GROUP   EXTERNAL_NAME

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

SYS                           OPEN

DEFAULT

SYSTEM                        TEMP

SYS_GROUP

 

SYSTEM                        OPEN

DEFAULT

TOOLS                         TEMP 

SYS_GROUP

 

WAPMAIL                       OPEN

DEFAULT

WAPMAIL                       TEMP

DEFAULT_CONSUMER_GROUP

 

ROLETEST                      OPEN

DEFAULT

WAPMAIL                       TEMP

DEFAULT_CONSUMER_GROUP

 

WAPMAIL_156                   OPEN

DEFAULT

WAPMAIL                       TEMP

DEFAULT_CONSUMER_GROUP

 

TEMP_IVR                      OPEN

DEFAULT

XIAO                          TEMP

DEFAULT_CONSUMER_GROUP

 

WAPMAIL_RW                    OPEN

DEFAULT

USERS                         TEMP

DEFAULT_CONSUMER_GROUP

 

NEWSTA                        OPEN

DEFAULT

NEWSTA                        TEMP

DEFAULT_CONSUMER_GROUP

 

BOSS_EXCA                     OPEN

DEFAULT

BOSS_WAPMAIL                  TEMP

DEFAULT_CONSUMER_GROUP

 

PERFSTAT                      OPEN

DEFAULT

PERFSTAT                      TEMP

DEFAULT_CONSUMER_GROUP

 

WAPMAIL_RO                    OPEN

DEFAULT

USERS                         TEMP                          

DEFAULT_CONSUMER_GROUP

 

DUTYVISITDBA                  OPEN

DEFAULT

PERFSTAT                      TEMP

DEFAULT_CONSUMER_GROUP

 

SURVEY                        OPEN

DEFAULT

UNIJAWAP                      TEMP

DEFAULT_CONSUMER_GROUP

 

XIAO                          OPEN

DEFAULT

XIAO                          XIAO_TMP

DEFAULT_CONSUMER_GROUP

 

OUTLN                         LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

OSE$HTTP$ADMIN                LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

ORDSYS                        LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

MDSYS                         LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

CTXSYS                        LOCKED

DEFAULT                       03-APR-06

DRSYS                         TEMP

DEFAULT_CONSUMER_GROUP

 

ORDPLUGINS                    LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

AURORA$ORB$UNAUTHENTICATED    LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

AURORA$JIS$UTILITY$           LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

DBSNMP                        LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

TRACESVR                      LOCKED

DEFAULT                       03-APR-06

SYSTEM                        TEMP

DEFAULT_CONSUMER_GROUP

 

2)     检查异常用户

Sql>select username,account_status,profile,

to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') as t_lock_date,

to_char(created,'yyyy-mm-dd hh24:mi:ss') as t_created

from dba_users

where profile != 'DEFAULT' or (sysdate - lock_date) < 11

or (sysdate - created) < 11

order by account_status desc,profile;

 

    no rows selected

 

11.检查表扩张情况（包括临时表空间）

1)     取各表空间占用的字节数

Sql>select tablespace_name, sum(bytes) from dba_data_files

group by tablespace_name

union

select tablespace_name, sum(bytes) from dba_temp_files

group by tablespace_name;

 

TABLESPACE_NAME       SUM(BYTES)

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

BOSS_WAPMAIL          2147483648

DRSYS                 20971520

NEWSTA                209715200

PERFSTAT              314572800

RBS01                 2311372800

SYSTEM                524288000

TEMP                  524288000

TOOLS                 10485760

UNIJAWAP              209715200

USERS                 20971520

WAPMAIL               1048576000

XIAO                  2.5740E+10

XIAO_INDEX            4294967296

XIAO_TMP              5679087616

 

2)     取各表空间空闲的字节数

Sql>select tablespace_name, sum(bytes) from dba_free_space

group by tablespace_name

    union

    select tablespace_name, sum(bytes_free) from V$TEMP_SPACE_HEADER

group by tablespace_name;

 

TABLESPACE_NAME        SUM(BYTES)

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

BOSS_WAPMAIL           2058993664

DRSYS                  16637952

NEWSTA                 209387520

PERFSTAT               244482048

RBS01                  2281259008

SYSTEM                 235929600

TEMP                   427819008

TOOLS                  10018816

UNIJAWAP               206045184

USERS                  18866176

WAPMAIL                841940992

XIAO                   5964693504

XIAO_INDEX             2214526976

XIAO_TMP               2151677952

 

3)     将表空间占用的字节数和空闲字节数分别放入表

dutyvisitdba.STAMP_TABLESPACE_SIZE

dutyvisitdba.STAMP_TABLESPACE_FREE_SPACE

 

4)     取表空间最早和最新的使用时间

Sql> select to_char(min(timestamp),'yyyy-mm-dd hh24:mi:ss') as min_timestamp,

    to_char(max(timestamp),'yyyy-mm-dd hh24:mi:ss') as max_timestamp

    from dutyvisitdba.STAMP_TABLESPACE_SIZE;

 

MIN_TIMESTAMP                MAX_TIMESTAMP

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

2006-11-04 08:40:08       2006-12-06 08:40:08

 

5)     取表空间最近两天的使用时间

Sql> select to_char(min(timestamp),'yyyy-mm-dd hh24:mi:ss') as min_timestamp,

    to_char(max(timestamp),'yyyy-mm-dd hh24:mi:ss') as max_timestamp

    from dutyvisitdba.STAMP_TABLESPACE_SIZE

where timestamp >= sysdate-2

    and  timestamp <= sysdate;

 

MIN_TIMESTAMP               MAX_TIMESTAMP

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

2006-12-04 16:40:08      2006-12-06 08:40:08

 

6)     取2006-12-04表空间的使用情况

Sql>select a.tablespace_name,a.tablespace_type,

a.bytes/1024/1024 as old_total_size,

    b.bytes_free/1024/1024 as old_free_size

    from dutyvisitdba.STAMP_TABLESPACE_SIZE a,

dutyvisitdba.STAMP_TABLESPACE_FREE_SPACE b

    where a.tablespace_name = b.tablespace_name

    and a.tablespace_type = b.tablespace_type

    and a.timestamp = to_date('2006-12-04 16:40:08','yyyy-mm-dd hh24:mi:ss')

    and a.timestamp = b.timestamp;

 

TABLESPACE_NAME   TABLESPACE_TYPE   OLD_TOTAL_SIZE   OLD_FREE_SIZE

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

BOSS_WAPMAIL      DATA              2048              1963.60938

DRSYS             DATA              20                15.8671875

NEWSTA            DATA              200               199.6875

PERFSTAT          DATA              300               233.15625

RBS01             DATA              2204.29688        2175.57813

SYSTEM            DATA              500               225

TEMP              TEMP              500               408

TOOLS             DATA              10                9.5546875

UNIJAWAP          DATA              200               196.5

USERS             DATA              20                17.9921875

WAPMAIL           DATA              1000              802.9375

XIAO              DATA              24548             6012.25

XIAO_INDEX        DATA              4096              2111.9375

XIAO_TMP          TEMP              5416              2052

 

7)     取2006-12-06表空间的使用情况

Sql>select a.tablespace_name,a.tablespace_type,

a.bytes/1024/1024 as new_total_size,

    b.bytes_free/1024/1024 as new_free_size

    from dutyvisitdba.STAMP_TABLESPACE_SIZE a,

dutyvisitdba.STAMP_TABLESPACE_FREE_SPACE b

    where a.tablespace_name = b.tablespace_name

    and a.tablespace_type = b.tablespace_type

    and a.timestamp = to_date('2006-12-06 08:40:08','yyyy-mm-dd hh24:mi:ss')

    and a.timestamp = b.timestamp;

 

TABLESPACE_NAME   TABLESPACE_TYPE   NEW_TOTAL_SIZE   NEW_FREE_SIZE

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

BOSS_WAPMAIL       DATA              2048              1963.60938

DRSYS              DATA              20                15.8671875

NEWSTA             DATA              200               199.6875

PERFSTAT           DATA              300               233.15625

RBS01              DATA              2204.29688        2175.57813

SYSTEM             DATA              500               225

TEMP               TEMP              500               408

TOOLS              DATA              10                9.5546875

UNIJAWAP           DATA              200               196.5

USERS              DATA              20                17.9921875

WAPMAIL            DATA              1000              802.9375

XIAO               DATA              24548             6004.125

XIAO_INDEX         DATA              4096              2111.9375

XIAO_TMP           TEMP              5416              2052

 

8)     求最近两天表空间的增长率

Sql>select e.tablespace_name,e.tablespace_type,

to_char(round(e.total_size,1)) as total_size,

to_char(round((e.total_size - e.free_size),1)) as used_size,

to_char(round(e.free_size,1)) as free_size,

to_char(round((e.total_size - e.free_size)/

decode(e.total_size,0,-1,e.total_size),1)*100)||'%' as space_used_rate,

to_char(round(((e.total_size - e.free_size) –

          (decode(b.total_size,null,0,b.total_size) –

decode(b.free_size,null,0,b.free_size))),1)) as increase

from (select a.tablespace_name,a.tablespace_type,

a.bytes/1024/1024 as total_size,b.bytes_free/1024/1024 as free_size

  from dutyvisitdba.STAMP_TABLESPACE_SIZE a,

dutyvisitdba.STAMP_TABLESPACE_FREE_SPACE b

  where a.tablespace_name = b.tablespace_name

 and a.tablespace_type = b.tablespace_type

  and a.timestamp =

to_date('2006-12-06 08:40:08','yyyy-mm-dd hh24:mi:ss')

  and a.timestamp = b.timestamp) e,

(select a.tablespace_name,a.bytes/1024/1024 as total_size,

b.bytes_free/1024/1024 as free_size

  from dutyvisitdba.STAMP_TABLESPACE_SIZE a,

dutyvisitdba.STAMP_TABLESPACE_FREE_SPACE b

  where a.tablespace_name = b.tablespace_name

and a.tablespace_type = b.tablespace_type

  and a.timestamp =

to_date('2006-12-04 16:40:08','yyyy-mm-dd hh24:mi:ss')

and a.timestamp = b.timestamp) b      

where e.tablespace_name = b.tablespace_name(+);

 

TABLESPACE_NAME   TABLESPACE_TYPE   TOTAL_SIZE   USED_SIZE

FREE_SIZE         SPACE_USED_RATE   INCREASE

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

BOSS_WAPMAIL      DATA               2048         84.4

1963.6            0%                 0

 

DRSYS             DATA               20           4.1

15.9              20%                0

 

NEWSTA            DATA               200          .3

199.7             0%                 0

 

PERFSTAT          DATA               300          66.8

233.2             20%                0

 

RBS01             DATA               2204.3       28.7

2175.6            0%                 0

 

SYSTEM            DATA               500          275

225               60%                0

 

TEMP              TEMP               500          92

408               20%                0

 

TOOLS             DATA               10           .4

9.6               0%                 0

 

UNIJAWAP          DATA               200          3.5

196.5             0%                 0

 

USERS             DATA               20           2

18                10%                0

 

WAPMAIL           DATA               1000         197.1

802.9             20%                0

 

XIAO              DATA               24548        18543.9

6004.1            80%                8.1

 

XIAO_INDEX        DATA               4096         1984.1

2111.9            50%                0

 

XIAO_TMP          TEMP               5416         3364

2052              60%                0

 

小结：

TOTAL_SIZE = NEW_TOTAL_SIZE 

USED_SIZE = NEW_TOTAL_SIZE - NEW_FREE_SIZE

FREE_SIZE = NEW_FREE_SIZE

SPACE_USED_RATE = (NEW_TOTAL_SIZE - NEW_FREE_SIZE)/

NEW_TOTAL_SIZE*100% 

INCREASE = NEW_TOTAL_SIZE - OLD_TOTAL_SIZE

 

二． DayRealTimeAlarmReport.sh维护内容

 

1.     检查session数

Sql>select count(*) as cnt from v$session;

 

 CNT

----------

 22

 

2.     查看alert日志

1)     确定alert日志目录

Sql> select name, value from v$parameter where name like '%dump%';

 

NAME                           VALUE

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

shadow_core_dump               partial

background_core_dump           partial

background_dump_dest           /u01/app/oracle/admin/wapmail/bdump

user_dump_dest                 /u01/app/oracle/admin/wapmail/udump

max_dump_file_size             UNLIMITED

core_dump_dest                 /u01/app/oracle/admin/wapmail/cdump

 

2)     查看/u01/app/oracle/admin/wapmail/bdump/ alert_wapmail.log日志

 

三． DBA_MAIN_delhisdata.sh维护内容

1.     删除历史数据（删除一个月前的记录）

1)     sql>delete from dutyvisitdba.STAMP_TABLESPACE_FREE_SPACE

where TIMESTAMP < sysdate - 31;

 

2)     sql> delete from dutyvisitdba.STAMP_TABLESPACE_SIZE

where TIMESTAMP < sysdate - 31;

 

 

四． DBA_MAIN_logmanage.sh维护内容

1.     备份日志文件

1)        复制文件

>cd /u01/app/oracle/admin/wapmail/bdump

>cp alert_wapmail.log alert_wapmail.log.20061207160000

 

压缩文件 >/bin/gzip alert_wapmail.log.20061207160000

生成压缩文件 alert_wapmail.log.20061207160000.gz

 

2)        >cd /u01/app/oracle/product/8.1.7/network/log

>cp listener.log listener.log.20061207160000

 

>/bin/gzip listener.log.20061207160000

生成压缩文件 listener.log.20061207160000.gz

 

3)        >cd /u01/app/oracle/product/8.1.7/network/log

>cp sqlnet.log sqlnet.log.20061207160000

 

>/bin/gzip sqlnet.log.20061207160000

生成压缩文件 sqlnet.log.20061207160000.gz

 

2.     备份审计文件

1)     >cd /u01/app/oracle/admin/wapmail/bdump

检查 wapmail.bdump.20061207160000目录是否存在

不存在，则创建目录  >Mkdir wapmail.bdump.20061207160000

 

将15天以前的trc文件移至目录/wapmail.bdump.20061207160000

>mv -f `find . -mtime +15` /wapmail.bdump.20061207160000

 

打tar包 >tar -cf wapmail.bdump.20061207160000.tar

./wapmail.bdump.20061207160000

 

   删除目录 >rm -r

/u01/app/oracle/admin/wapmail/bdump/wapmail.bdump.20061207160000

压缩文件 >/bin/gzip wapmail.bdump.20061207160000.tar

生成压缩文件文件 wapmail.bdump.20061207160000.tar.gz

 

2)     >cd /u01/app/oracle/admin/wapmail/udump

检查 wapmail.udump.20061207160000目录是否存在

不存在，则创建目录  >Mkdir wapmail.udump.20061207160000

 

>mv -f `find . -mtime +15` /wapmail.udump.20061207160000

 

打tar包 >tar -cf wapmail.udump.20061207160000.tar

./wapmail.udump.20061207160000

 

   删除目录 >rm -r

/u01/app/oracle/admin/wapmail/udump /wapmail.udump.20061207160000

 

压缩文件 >/bin/gzip wapmail.udump.20061207160000.tar

生成压缩文件文件 wapmail.udump.20061207160000.tar.gz

 

3)     >cd /u01/app/oracle/admin/wapmail/cdump

检查 wapmail.bdump.20061207160000目录是否存在

不存在，则创建目录  >Mkdir wapmail.cdump.20061207160000

 

>mv -f `find . -mtime +15` /wapmail.cdump.20061207160000

 

打tar包 >tar -cf wapmail.cdump.20061207160000.tar

./wapmail.cdump.20061207160000

 

   删除目录 >rm -r

/u01/app/oracle/admin/wapmail/bdump/wapmail.cdump.20061207160000

 

压缩文件 >/bin/gzip wapmail.cdump.20061207160000.tar

生成压缩文件文件 wapmail.cdump.20061207160000.tar.gz

 

 

五． db_env_bk.sh维护内容

1.     备份数据库参数文件

1)     切换至备份目录

>cd /DBA/backup/env

 

2)     检查目录 /DBA/backup/env/db_env_bk_20061207160000是否存在

不存在，则创建目录 >mkdir db_env_bk_20061207160000

 

3)     拷贝文件

>cp /u01/app/oracle/admin/wapmail/pfile/initwapmail.ora

/DBA/backup/env/db_env_bk_20061207160000/.

 

    Oracle8i没有spfile，故备份initwapmail.ora（等价）

    9i以上版本要备份spfile，以短信数据库为例

>cp /app/oracle/product/9.2.0/dbs/spfileorawic.ora

/DBA/backup/env/db_env_bk_20061207160000/.

 

>cp /u01/app/oracle/product/8.1.7/network/admin/listener.ora

/DBA/backup/env/db_env_bk_20061207160000/.

 

>cp /u01/app/oracle/product/8.1.7/network/admin/tnsnames.ora

/DBA/backup/env/db_env_bk_20061207160000/.

 

>cp /u01/app/oracle/product/8.1.7/network/admin/sqlnet.ora

/DBA/backup/env/db_env_bk_20061207160000/.

 

>cp /export/oracle/.profile

/DBA/backup/env/db_env_bk_20061207160000/.

 

2.     备份控制文件

1) 把控制文件备份到跟踪文件（*.trc）

Sql> alter database backup controlfile to trace

 

2) 确定用户dump文件的路径

    Sql> select value from v$parameter where name='user_dump_dest';

 

    VALUE

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

/u01/app/oracle/admin/wapmail/udump

 

4)     确定会话对应的所有进程号（由登录用户启动的进程号）

    Sql> select paddr from v$session where audsid=userenv('SESSIONID');

 

以sys用户登录（启动的是系统进程）

    PADDR

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

00000003A59A4130

00000003A59A4560

00000003A59A4990

00000003A59A4DC0

00000003A59A51F0

00000003A59A5620

00000003A59A5A50

00000003A59A5E80

00000003A59A62B0

00000003A59A66E0

00000003A59A7BD0

 

    以wapmail用户登录（一般只对应一个server process）

PADDR

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

00000003A59A90C0

 

5)     取会话进程号对应的操作系统进程号

    Sql>select a.spid from v$process a, v$session b

where a.addr = b.paddr

and b. audsid= userenv('SESSIONID');

或

Sql>select spid from v$process where addr =/in

(select paddr from v$session where audsid=userenv('SESSIONID'));

 

Spid与进程间的关系

SPID          $ps -ef | grep ora_

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

26661    oracle 26661     1  0   Dec 01 ?        0:02 ora_pmon_wapmail

26663    oracle 26663     1  0   Dec 01 ?        2:46 ora_dbw0_wapmail

26665    oracle 26665     1  0   Dec 01 ?        2:05 ora_lgwr_wapmail

26667    oracle 26667     1  0   Dec 01 ?        2:20 ora_ckpt_wapmail

26669    oracle 26669     1  0   Dec 01 ?        0:06 ora_smon_wapmail

26671    oracle 26671     1  0   Dec 01 ?        0:00 ora_reco_wapmail

26673    oracle 26673     1  0   Dec 01 ?        0:42 ora_snp0_wapmail

26675    oracle 26675     1  0   Dec 01 ?        0:44 ora_snp1_wapmail

26677    oracle 26677     1  0   Dec 01 ?        0:23 ora_snp2_wapmail

26679    oracle 26679     1  0   Dec 01 ?        0:22 ora_snp3_wapmail

6540

 

6)     获取数据库实例名

Sql>select value from v$parameter where name='instance_name';

 

VALUE

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

wapmail

 

7)     确定备份路径及文件名

    /u01/app/oracle/admin/wapmail/udump/wapmail_ora_26661.trc

    /u01/app/oracle/admin/wapmail/udump/wapmail_ora_26663.trc

    …

 

8)     确定备份路径及文件名

>cp /u01/app/oracle/admin/wapmail/udump/wapmail_ora_26661.trc

/DBA/backup/env/db_env_bk_20061207160000/.

   …

 

3.     打压缩包

>cd /DBA/backup/env

>tar -cf db_env_bk_20061207160000.tar  ./db_env_bk_20061207160000

>/bin/gzip db_env_bk_20061207160000.tar

>rm –r db_env_bk_20061207160000

六． db_user_structure_bk.sh维护内容

1.     从/DBA/commconf/db_user_bk.lst文件中获取用户列表

CESVR

ROLETEST

ORDSYS

ORDPLUGINS

MDSYS

CTXSYS

WAPMAIL

XIAO

WAPMAIL_156

SURVEY

WAPMAIL_RW

WAPMAIL_RO

NEWSTA

BOSS_INTE

BOSS_EXCA

 

2.     切换至备份目录

>cd /DBA/backup/db_structure

 

3.     检查目录db_user_structure_bk_20061207160000是否存在，如果不存在，则创建该目录

 

4.     依次备份用户文件

>/u01/app/oracle/product/8.1.7/bin/exp system/Ou03LuDH@wapmail

 file=/DBA/backup/db_structure/db_user_structure_bk_20061207160000/CESVR.dat

log=/DBA/backup/db_structure/db_user_structure_bk_20061207160000/CESVR.log

owner=CESVR rows=n

 

5.     打包压缩

>cd /DBA/backup/db_structure

>tar -cf  db_user_structure_bk_20061207160000.tar

 ./ db_user_structure_bk_20061207160000

>/bin/gzip db_user_structure_bk_20061207160000.tar

> rm –r db_user_structure_bk_20061207160000

 

七． exp_ulitity_bigboarv1.sh维护内容

1.       从/DBA/commconf/db_user_data_bk.lst文件中获取用户列表

 

Username        password         备份的table列表

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

CESVR

ROLETEST

ORDSYS

ORDPLUGINS

MDSYS

CTXSYS

WAPMAIL

XIAO            ChwSKaJ1        /DBA/commconf/tables/tables_databk_XIAO.lst

WAPMAIL_156

SURVEY

WAPMAIL_RW

WAPMAIL_RO

NEWSTA

 

2.       判断目录/disk1/oracle/backup/exp_backup/exp_200612071600，如果不存在，则创建该目录

 

3.       依次备份用户文件

1) 简单备份

>/u01/app/oracle/product/8.1.7/bin/exp

system/ Ou03LuDH@wapmail

file=/disk1/oracle/backup/exp_backup/exp_200612071600/CESVR_20061207160002.dat

log=/disk1/oracle/backup/exp_backup/exp_200612071600/CESVR_20061207160002.log

owner=CESVR

 

2) 指定表备份（存在备份table列表，如xiao用户）

>/u01/app/oracle/product/8.1.7/bin/exp

    XIAO/ChwSKaJ1@wapmail

file=/disk1/oracle/backup/exp_backup/exp_200612071600/XIAO_20061207160002.dat

log=/disk1/oracle/backup/exp_backup/exp_200612071600/XIAO_20061207160002.log

parfile=/DBA/commconf/tables/tables_databk_XIAO.lst

 

4)     打包压缩

>/usr/bin/gzip

 /disk1/oracle/backup/exp_backup/exp_200612071600/CESVR_20061207160002.dat

 

4.       删除10天前的数据

>/usr/bin/find  /disk1/oracle/backup/exp_backup -mtime +10 -exec rm -rf {} \;

 

八．