Oracle 常用语句

创建用户

create user dev identified by iampaawd;
grant connect, resource to dev;

查看表

SELECT * FROM USER_TABLES;
select * from user_tab_comments;
select * from user_col_comments;

查看执行计划

‘%SELECT DISTINCT%’ 为你要找的SQL,'3039j4pnpw0sf’是你找到的SQL_ID

SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT DISTINCT%';
SELECT * FROM v$sql_plan WHERE SQL_ID='3039j4pnpw0sf';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3039j4pnpw0sf',0));
select * from table( dbms_xplan.display_cursor('3039j4pnpw0sf'));

查看序列

select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;

查看最近执行的SQL

SELECT sql_text, last_load_time,SQL_ID FROM v$sql WHERE last_load_time IS NOT NULL and sql_text like 'select%' ORDER BY last_load_time DESC;

查看SQL绑定的参数

select name,datatype_string,VALUE_STRING from v$sql_bind_capture where sql_id='0bgqvw16fpts0';

当前的连接数

select count(*) from v$process;

查看最大连接数

SELECT * FROM V$parameter WHERE NAME = 'processes';

修改最大连接数

ALTER SYSTEM SET processes=1500 SCOPE=spfile;
shutdown immediate;
startup;

服务端字符集

select userenv('language') from dual;
select * from nls_database_parameters where parameter like '%CHARACTERSET%';

客户端字符集

select * from nls_instance_parameters;

修改字符集 和 时区

su oracle
sqlplus /nolog
conn /as SYSDBA
alter database set time_zone='+8:00';
shutdown immediate;
startup MOUNT
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
alter database open;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
select * from v$nls_parameters;
shutdown immediate;
startup

EXIT
EXIT

创建表空间

chmod 700 /home
su oracle
sqlplus /nolog
conn /as SYSDBA
CREATE TABLESPACE TBS_HISUSER DATAFILE '/home/TABLESPACE01' SIZE 100M autoextend on;
EXIT
EXIT

导出表结构

SELECT t1.Table_Name AS "表名称",
t3.comments AS "表说明",
t1.Column_Name AS "字段名称",
t1.Data_Type AS "数据类型",
t1.Data_Length AS "长度",
t1.NullAble AS "是否为空",
t2.Comments AS "字段说明",
t1.Data_Default "默认值"
FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3
on t1.Table_name=t3.Table_name
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
WHERE t4.Object_Type='TABLE'
AND t4.Temporary='Y'
AND t4.Object_Name=t1.Table_Name )
ORDER BY t1.Table_Name, t1.Column_ID;

锁表相关

--查询锁表
SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
 WHERE B.OBJECT_ID = A.OBJECT_ID;
--查询导致锁表的session
SELECT B.USERNAME, B.SID, B.SERIAL#, LOGON_TIME
  FROM V$LOCKED_OBJECT A, V$SESSION B
 WHERE A.SESSION_ID = B.SID
 ORDER BY B.LOGON_TIME;
--KILL SESSION
ALTER SYSTEM KILL SESSION 'sid,serial#';

参考

本文所有SQL，均为网络搜集。