oracle管理常用

1.v$session:当前会话视图

#查看oracle当前连接数

（select count (*) from v$session）;

2.v$parameter:oracle参数视图<==>show parameter [%parameter_name%]

#查看oralce数据库允许的最大连接数

（select value from v$parameter where name = 'processes'）等价于

（show parameter processes）

3.select * from user_tables t where t.TABLESPACE_NAME='PECB';

--查看每个表所占空间大小

select OWNER, t.segment_name, t.segment_type, sum (t.bytes / 1024 / 1024 ) mmm

 from dba_segments t

 where t.owner = 'ZFCG'

 and t.segment_type= 'TABLE'

 group by OWNER, t.segment_name, t.segment_type

 order by mmm desc;

3.查询执行较慢的sql

select a.buffer_gets,a.EXECUTIONS,a.elapsed_time,a.sql_fulltext,a.* from v$sql a

order by 1 desc

2.创建表空间用户分配权限

drop user app_1 cascade;

drop user dzzwpt cascade;

DROP TABLESPACE app_1 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE app_1temp INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE hnczzj INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE hnczzjtemp INCLUDING CONTENTS AND DATAFILES;

--创建数据表空间

CREATE TABLESPACE hnczzj

LOGGING

DATAFILE '/home/oracle/oradata/orcl/hnczzj.dbf'

SIZE 512M

AUTOEXTEND ON

NEXT 64M MAXSIZE 10240M

EXTENT MANAGEMENT LOCAL;

--创建临时表空间

CREATE TEMPORARY TABLESPACE hnczzjtemp

TEMPFILE '/home/oracle/oradata/orcl/hnczzjtemp.dbf'

SIZE 32M

AUTOEXTEND ON

NEXT 32M MAXSIZE 512M

EXTENT MANAGEMENT LOCAL;

--创建用户并指定表空间

CREATE USER dzzwpt IDENTIFIED BY dzzwpt

DEFAULT TABLESPACE hnczzj

TEMPORARY TABLESPACE hnczzjtemp;

--给用户授予权限

GRANT DBA TO dzzwpt;

GRANT CONNECT TO dzzwpt;

GRANT RESOURCE TO dzzwpt;

GRANT SELECT ANY TABLE TO dzzwpt;

GRANT CREATE ANY PROCEDURE TO dzzwpt;

--查看表空间使用情况

select b.file_name 物理文件名,

       b.tablespace_name 表空间,

       b.bytes / 1024 / 1024 大小M,

       (b.bytes - sum( nvl(a.bytes, 0))) / 1024 / 1024 已使用M,

       substr((b.bytes - sum (nvl (a.bytes, 0))) / (b.bytes) * 100, 1 , 5 ) 利用率

  from dba_data_files b left join dba_free_space a on a.file_id = b.file_id

 group by b.tablespace_name, b.file_name, b.bytes

 order by b.tablespace_name;

select 'alter database datafile ''' || a.file_name || ''' resize ' ||

       round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;',

       a.filesize,

       c.hwmsize

  from (select file_id, file_name, round(bytes / 1024 / 1024) filesize

          from dba_data_files) a,

       (select file_id, round(max(block_id) * 8 / 1024) HWMsize

          from dba_extents

         group by file_id) c

 where a.file_id = c.file_id

   and a.filesize - c.hwmsize > 100;

3.oracle开启cmd登录调优、查看执行计划

sqlplus / as sysdba

connect

(1).编写login.sql文件, 文件内容如下：

set serveroutput on size 1000000

set trimspool on

set long 5000

set linesize 100

set pagesize 9999

column plan_plus_exp format a80

column global_name new_value gname

set termout off

define gname=idle

column global_name new_value gname

select lower(user) || '@' || substr( global_name, 1,

decode( dot, 0, length(global_name), dot-1) ) global_name

from (select global_name, instr(global_name,'.') dot from global_name );

set sqlprompt '&gname> '

set autotrace traceonly

set timi on

set termout on

(2).配置环境变量SQLPATH（login.sql文件存放路径）