1、DOS命令下用超管进入
sqlplus /nolog;
conn / as sysdba;
2、修改INIT.ORA文件参数
alter system set undo_tablespace=undotbs2 scope=spfile;
alter system set undo_management=auto scope=spfile;
3、启用/关闭 归档日志(archive log)
- 开启归档日志
shutdown immediate;
startup mount;
alter database archivelog; -- 开启归档日志
alter database open; -- 开启数据库
archive log list; --查看归档日志状态
- 关闭归档日志
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;
4、修改最大连接数
- 查询数据库最大连接数
select value from v$parameter where name = 'processes'; --数据库允许的最大连接数
- 修改最大连接数
alter system set processes = 1000 scope = spfile;
shutdown immediate;
startup
5、查询oracle版本
select * from v$version;
6、比较命令decode
select decode(sign(变量1 - 变量2), -1, 变量1, 变量2) from dual; --取较小值
7、查看表大小
select segment_name,
tablespace_name,
bytes B,
bytes / 1024 KB,
bytes / 1024 / 1024 MB
from user_segments
where segment_type = 'TABLE';
8、查看死锁与解锁
查询死锁:
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;
解锁:
alter system kill session 'sid,serial#'; --结合上面查询死锁结果使用
9、查询数据库正在使用用户信息
select a."MACHINE", a."PROGRAM", a."PREV_EXEC_START", a.terminal
from v$session a
where a.username = 'HRPDEV';
?、查看表空间名称及大小
select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
-
查看表空间物理名称及大小
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;
-
查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
from dba_rollback_segs r, v$rollstat v
where r.segment_id = v.usn(+)
order by segment_name;
-
查看控制文件
select name from v$controlfile;
-
查看日志文件
select member from v$logfile;
-
查看表空间的使用情况
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
select a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
from sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name;
11、查看数据库对象
select owner, object_type, status, count(*) count#
from all_objects
group by owner, object_type, status;
12、查看数据库用户以及对应的表空间
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;
- 删除用户以及其所有对象
DROP USER YXT CASCADE;
- 删除表空间,同时删除数据文件
DROP TABLESPACE YXT INCLUDING CONTENTS AND DATAFILES; -- YXT为用户名
备注:
- 删除表空间之前要确认该表空间没有被其他用户使用之后再做删除;
- including contents 删除表空间中的内容,如果删除表空间之前表空间中有内容,而未加此参数,则表空间删不掉;
- including datafiles 删除表空间中的数据文件;
- cascade constraints 同时删除tablespace中表的外键参照;
- 删除DBLINK
DROP PUBLIC DATABASE LINK HRP_ZK_LINK; -- HRP_ZK_LINK为DBLINK名称
13、查看执行计划
explain plan for SQL;
select * from table(dbms_xplan.display);
例如: