数据库常用命令
时间：2022-03-14 04:22
进程查看
select count (*) from v$process ;
命令窗口：show parameter process;
修改数据库允许的最大连接数：
alter system set processes = 300 scope = spfile;
权限查看
select * from dba_role_privs where grantee = ‘SYS‘;
会话查看
select * from v$session
show parameter sessions
alter system kill session ‘4276,1329‘;
RAC环境下的查询和kill session
select * from gv$session where username = ‘CRM_BASE‘
alter system kill session ‘4276,1329,@2‘ ;
表名查看
select * from tab
select * from all_tables where owner=‘CRM_SO‘ --用户
select * from dba_table --DBA
select table_name from ( select * from user_tables ) order by table_name asc;
用户查看
select * from dba_users
查看表空间使用物理文件名，大小，已使用空间，利用率
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_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
扩展表空间文件大小
alter database datafile ‘/home/oracle/11g/dbs/ntonsite/data_cs.dbf‘ Resize 1000m;
设置表空间为自动扩展
增加表空间文件
alter tablespace DATA_BASE Add datafile ‘+DG_TCRM_DATA/crm/data_base3.dbf‘ size 10m reuse autoextend on next 100m maxsize 10g;
删除表空间文件
alter tablespace DATA_BASE Drop datafile ‘+DG_TCRM_DATA/crm/data_so3.dbf‘;
查看表空间的创建语句
SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE‘, TS.tablespace_name)
FROM DBA_TABLESPACES TS;
使用Toad 工具直接看表，用户，表空间等创建语句
锁表处理
SELECT A.OBJECT_ID,
B.OBJECT_NAME,
A.SESSION_ID,
A.ORACLE_USERNAME,
A.OS_USER_NAME,
A.PROCESS,
A.LOCKED_MODE,
c.MACHINE,
c.MODULE,
c.ACTION,
d.SQL_TEXT
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B,v$session C,v$sql D
WHERE A.OBJECT_ID = B.OBJECT_ID and a.session_id = c.SID and c.SQL_ADDRESS = d.ADDRESS;
SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME
FROM V$LOCKED_OBJECT T1, V$SESSION T2
WHERE T1.SESSION_ID = T2.SID
ORDER BY T2.LOGON_TIME;
ALTER SYSTEM KILL SESSION ‘3172, 25456‘;
select p.*
from v$session s,v$process p
where s.paddr=p.addr and s.sid=3172
