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文件存放路径)