常使用的Orcale命令

最新推荐文章于 2025-03-28 14:05:55 发布

原创最新推荐文章于 2025-03-28 14:05:55 发布 · 87 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #SQL #OS

本文介绍了Oracle数据库的启动与关闭操作、查询表空间文件及使用情况的方法，并提供了调整表空间大小的具体步骤，还展示了如何查询并解除锁定的会话。

su - oracle

sqlplus / as sysdba

shutdown immediate;
// 启动数据库
startup nomount;
alter database mount;
alter database open;

// 查询表空间文件
select file_name,tablespace_name,bytes/(1024*1024) from dba_data_files
where tablespace_name = 'TBS_DATA_SIG_USER_RPT1';

// 查询每一个表空间的大小和使用
select fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name;

// 重置表空间
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt1_01.dbf' resize 500m;
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt2_01.dbf' resize 500m;
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt3_01.dbf' resize 500m;
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt5_01.dbf' resize 500m;

9 查锁
select l.session_id sid,l.locked_mode lockmode,l.oracle_username db_user,l.os_user_name os_user,s.machine,s.schemaname,o.object_name tablename,q.sql_text
from v$locked_object l, v$session s, v$sql q, all_objects o
where l.session_id = s.sid
and s.type = 'USER' and s.sql_address = q.address and l.object_id = o.object_id
10 杀锁
alter system kill session 'sid,SERIAL#'

su - oracle

// 启动监听程序
lsnrctl start