创建用户
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，均为网络搜集。
