ORACLE运维常用命令

--新建表空间
CREATE TABLESPACE bimsdb
DATAFILE '/home/db/oracle/oradata_new/data01.dbf'
SIZE 10M

-- 系统权限查看
SELECT * FROM SYSTEM_PRIVILEGE_MAP ORDER BY NAME;

--创建用户
CREATE USER bims
IDENTIFIED bims
DEFAULT TABLESPACE bimsdb

-- 分配权限
GRANT CONNECT,RESOURCE,DBA TO bims;

-- 数据源配置
D:\JAVA\11.2\instantclient_12_2\tnsnames.ora

-- 密码修改
ALTER USER 用户名 IDENTIFIED BY 密码

--账号解锁
ALTER USER YGQD IDENTIFIED BY oracle ACCOUNT UNLOCK;

-- 账号信息查询
SELECT * FROM dba_users a WHERE a.username='SYSTEM';

-- 查询策略
SELECT * FROM Dba_Profiles s WHERE s.profile = 'DEFAULT' AND s.resource_name='PASSWORD_LIFE_TIME';
-- 删除用户
DROP USER dlcz CASCADE;


-- 关闭180天密码策略
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

--字符集查询
SELECT USERENV('language') FROM DUAL;
SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME = 'NLS_CHARACTERSET'

--锁表查询
SELECT COUNT(*) AS NUMCOUNT
  FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, All_Objects C
 WHERE P.ADDR = A.PADDR
   AND A.PROCESS = B.PROCESS
   AND C.OBJECT_ID = B.OBJECT_ID
   AND C.OBJECT_NAME = ?

--锁表杀进程
SELECT c.OBJECT_NAME,a.*
  FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, All_Objects C
 WHERE P.ADDR = A.PADDR
   AND A.PROCESS = B.PROCESS
   AND C.OBJECT_ID = B.OBJECT_ID
   AND C.OBJECT_NAME = ?

-- kill session进程
ALTER SYSTEM KILL SESSION '50,19194'  -- sid serial

-- 根据sid查询服务器进程号 kill -9 
SELECT SPID, OSUSER, S.PROGRAM
  FROM V$SESSION S, V$PROCESS P
 WHERE S.PADDR = P.ADDR
   AND S.SID = 50


-- session查询
SELECT * FROM V$SESSION T ORDER BY T.MACHINE



-- 数据文件查看
SELECT * FROM v$datafile
-- 表空间对应数据文件查看 
SELECT T2.*, T1.*
  FROM V$TABLESPACE T1
  LEFT JOIN V$DATAFILE T2
    ON T1.TS# = T2.TS#
 WHERE T2.TS# IS NOT NULL;

-- 数据字典
SELECT * FROM User_Tables WHERE table_name = 'USERS';
SELECT * FROM All_Tables WHERE table_name = 'USERS';
-- 表字段
SELECT * FROM all_tab_columns WHERE table_name  = 'USERS';
--表字段说明
SELECT * FROM User_Col_Comments WHERE table_name = 'USERS';

conn hdmp/hdmp@128.128.88:11521/HDMPAPDB
-- 远程登录 
sqlplus dlcz/dlcz@orcl_189

-- 重启监听
lsnrctl START

-- 切换实例
export oracle_sid=hdmpapdb
-- 重启数据库
SHUTDOWN IMMEDIATE;
SHUTDOWN ABORT;
startup;