Oracle运维常用SQL一览

目录

1.表空间满的问题 

2.oracle启动报错 

3.查看用户的表空间 

4.Oracle的连接数sessions和进程数processes参数修改

5.锁表、存储对象被锁的查询

---

1.表空间满的问题 

--查看表空间的占用量
SELECT AA.Tablespace_Name,
       Sumb AS Totle_MB,
       Suma AS Free_MB,
       (Sumb - Suma) AS Used_MB,
       ROUND((Sumb - Suma) / Sumb * 100, 2) AS Used_Percent
  FROM (SELECT DFS.Tablespace_Name,
               ROUND((SUM(DFS.Bytes) / 1024 / 1024), 2) AS Suma
          FROM DBA_FREE_SPACE DFS
         GROUP BY DFS.Tablespace_Name) AA,
       (SELECT DDF.Tablespace_Name,
               ROUND((SUM(DDF.Bytes) / 1024 / 1024), 2) AS Sumb
          FROM DBA_DATA_FILES DDF
         GROUP BY DDF.Tablespace_Name) BB
 WHERE AA.Tablespace_Name = BB.Tablespace_Name
 ORDER BY Used_Percent DESC;

查询数据文件dbf的位置

 select file_name,Tablespace_Name  FROM DBA_DATA_FILES DDF
 group by file_name,Tablespace_Name

 客户的业务无法正常使用，最终定位是表空间满的问题。

使用上面的查询语句，发现报错的XXA表空间没在里面，一直很疑惑。但在XXA表空间下新建表会报错：

ORA-01658: 无法为表空间 XXA中的段创建 INITIAL 区

虽然未查询到表空间使用率情况，查询dbf文件位置是可以看到该表空间dbf文件的，直接强制扩展表空间的存储：采用的是方法4

Oracle增加表空间大小的四种方法

Meathod1：给表空间增加数据文件

ALTER TABLESPACE app_data ADD DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF' SIZE 50M;

Meathod2：新增数据文件，并且允许数据文件自动增长

ALTER TABLESPACE app_data ADD DATAFILE  'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP04.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

Meathod3：允许已存在的数据文件自动增长

--允许表空间数据文件自增长
ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF' AUTOEXTEND ON

--表空间每次增长5M
ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF' AUTOEXTEND ON NEXT 5M;

--表空间每次增长5M，最大空间为100M
ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

Meathod4：手工改变已存在数据文件的大小

ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATAEDWTESTAPP02.DBF' RESIZE 100M;

查看临时表空间：

select c.tablespace_name,
       d.bytes_used "表空间使用大小(BYTE)",
       c.bytes / (1024 * 1024 * 1024) "表空间大小(GB)",
       (c.bytes - d.bytes_used) / (1024 * 1024 * 1024) "表空间剩余大小(GB)",
       d.bytes_used / (1024 * 1024 * 1024) "表空间使用大小(GB)",
       to_char(d.bytes_used * 100 / c.bytes, '99.99999') || '%' "使用率"
  from (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         group by tablespace_name) c,
       (select tablespace_name, sum(bytes_cached) bytes_used
          from v$temp_extent_pool
         group by tablespace_name) d
 where c.tablespace_name = d.tablespace_name;

2.oracle启动报错 

使用人员说后台报错dblink无法使用，但是我用plsql都无法登录数据库。

通过服务器登录，发现监听关闭了；

开启监听后，进入数据库发现，数据库也是关闭状态。

startup数据库过程报错，查看日志

background_dump_dest   = /orabak/oracle10/admin/crm/bdump

sculkget: failed to lock /oracle/OraHome_1/dbs/lkCRM exclusive
sculkget: lock held by PID: 12587 

查看这个进程是crm实例的一个进程，kill掉后再次重启数据库恢复正常。

3.查看用户的表空间 

--查看用户表空间
select USERNAME,default_tablespace from dba_users;

 查看用户对应的表空间和表情况：

select owner,tablespace_name,SEGMENT_NAME ,segment_type,sum(BYTES/1024/1024) "sizes(MB)" 
from dba_segments where  owner='WJZ' 
group by owner,segment_name,segment_type, tablespace_name 
order by "sizes(MB)"  desc;

查看用户下所有lob字段占用空间大小：

SELECT B.TABLE_NAME,
   a.tablespace_name,
   B.COLUMN_NAME,
   A.SEGMENT_NAME,
   a.SEGMENT_TYPE,
    ROUND(SUM(A.BYTES / 1024 / 1024 ), 2) MB
FROM DBA_SEGMENTS A
JOIN DBA_LOBS B
ON A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
WHERE A.owner='WJZ'
GROUP BY B.TABLE_NAME,B.TABLESPACE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE,a.tablespace_name
ORDER BY MB DESC ;

4.Oracle的连接数sessions和进程数processes参数修改

查看设置的参数值

show parameter processes;
show parameter sessions;

查询数据库当前进程的连接数

select count(*) from v$process;

 查看数据库当前会话的连接数

select count(*) from v$session;

修改参数值

alter system set processes=500 scope=spfile;
alter system set sessions=555  scope=spfile;

ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下：
sessions=(1.1*process+5) 

另外，若是修改静态参数（不能直接生效的参数），必须指定scope=spfile，否则若指定memory或者both会报如下错误：

ORA-02095: specified initialization parameter cannot be modified

因为静态参数不能直接通过修改内存而生效，只能通过修改spfile然后重启数据库生效。

所以，需要注意的是修改processes和sessions值后必须重启oracle服务器才能生效

重启数据库

shutdown immediate;
startup;

5.锁表、存储对象被锁的查询

select * from dba_ddl_locks where name='PKG_TEMP';

查看是否有排它锁存在，如果有编译存过就会卡住。