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 |