LinuxEye - Linux系统教程

LinuxEye - Linux系统教程

当前位置: 主页 > 数据库 >

Oracle DBA日常工作常用SQL整理

时间:2013-06-16 20:24来源:wubiaoblog.com/archives/938 编辑:admin 点击:
1.用SQL计算出你所用数据库的总容量,给出SQL语句和结果。 SQL select sum(bytes)/1024/1024||M size from (select bytes from dba_data_files union all select bytes from dba_temp_files);size-----------------------------------
1.用SQL计算出你所用数据库的总容量,给出SQL语句和结果。
SQL> select sum(bytes)/1024/1024||'M' "size" from (select bytes from dba_data_files union all select bytes from dba_temp_files);

size
-----------------------------------------
4284.5M

2.用SQL计算出某个用户下所有对象的大小,给出SQL语句和结果。
SQL> select sum(bytes)/1024/1024||'M' "size" from dba_segments where owner='SCOTT';

size
-----------------------------------------
79.875M

3.用SQL计算某个表空间的大小及所包含对象的大小,给出SQL语句和结果。
SQL> select df.tablespace_name,sum(df.bytes)/1024/1024||'M' tablespace_size,sum(nvl(ds.bytes,0))/1024/1024||'M' object_size from dba_data_files df,(select tablespace_name, sum(bytes) bytes from dba_segments group by tablespace_name) ds where df.tablespace_name=ds.tablespace_name(+) group by df.tablespace_name;

TABLESPACE_NAME      TABLESPACE_SIZE      OBJECT_SIZ
-------------------- -------------------- ----------
TS_SALE_OTHERS       10M                  0M
TS_SALE_WEST         60M                  0M
TS_SALE_DATE_8       40M                  0M
TS_SALE_HASH4        60M                  0M
TS_SALE_HASH1        60M                  0M
SYSAUX               630M                 570.375M
UNDOTBS1             275M                 24.9375M
TS_SALE_DATE_4       40M                  0M
TS_SALE_DATE_9       40M                  0M
TS_SALE_DATE_11      40M                  0M
TS_SALE_DATE_10      40M                  0M
USERS                657.5M               97.3125M
CTXSYS               50M                  5.625M
TS_SALE_DATE_5       40M                  0M
TS_SALE_DATE_12      40M                  0M
TS_SALE_DATE_2       40M                  0M
TS_SALE_CENTRAL      60M                  0M
SYSTEM               1020M                943.1875M
TS_SALE_HASH7        60M                  0M
TS_SALE_DATE_0       40M                  32.875M
READONLY             5M                   .0625M
TS_SALE_HASH2        60M                  0M
TS_SALE_HASH3        60M                  0M
TS_SALE_SOUTH        60M                  0M
TS_SALE_DATE_7       40M                  0M
TS_2010              10M                  .0625M
TS_2011              10M                  .0625M
TS_SALE_HASH6        60M                  0M
TS_2012              10M                  0M
TS_SALE_HASH5        60M                  0M
TS_SALE_HASH8        60M                  0M
TS_SALE_EAST         70M                  0M
TS_SALE_NORTH        60M                  0M
TS_SALE_DATE_1       40M                  0M
TS_SALE_DATE_6       40M                  0M
TS_SALE_DATE_3       40M                  0M

36 rows selected.

4.在告警日志中找到一条错误信息,并贴出来(如果没有,自己造出一条错误信息)。
[oracle@biao ~]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Sun Mar 10 23:07:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/oracle"
adrci> set homepath diag/rdbms/mydb/mydb
adrci> show alert -tail 1
2013-03-10 23:04:20.521000 +08:00
ORACLE Instance mydb - Archival Error
ORA-16038: log 4 sequence# 267 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 4 thread 1: '/oracle/oradata/mydb/redo04.log'
Errors in file /oracle/diag/rdbms/mydb/mydb/trace/mydb_arc0_5422.trc:
ORA-16038: log 4 sequence# 267 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 4 thread 1: '/oracle/oradata/mydb/redo04.log'
2013-03-10 23:05:03.225000 +08:00
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_2: '/oracle_/archive2/1_267_791515158.dbf' (error 19502) (mydb)
Errors in file /oracle/diag/rdbms/mydb/mydb/trace/mydb_arc1_5424.trc:
ORA-19502: write error on file "/oracle_/archive2/1_267_791515158.dbf", block number 71680 (block size=512)
ORA-27072: File I/O error
Linux Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 71680
Additional information: 229376
ORA-19502: write error on file "/oracle_/archive2/1_267_791515158.dbf", block number 71680 (block size=512)
2013-03-10 23:06:02.544000 +08:00
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance mydb - Archival Error
ORA-16014: log 4 sequence# 267 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/oracle/oradata/mydb/redo04.log'
Errors in file /oracle/diag/rdbms/mydb/mydb/trace/mydb_arc3_5428.trc:
ORA-16014: log 4 sequence# 267 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/oracle/oradata/mydb/redo04.log'

转载请保留固定链接: https://linuxeye.com/database/1765.html

------分隔线----------------------------
标签:OracleDBA
栏目列表
推荐内容