查询被锁住的会话
select a.inst_id,a.sid,a.serial#,a.sql_id,a.blocking_instance,a.blocking_session,a.event from GV$SESSION a where a.username is not null and a.status='ACTIVE' and a.blocking_session is not null;
查询一个表的大小
COL OWNER FORMAT A10
COL SEGMENT_NAME FORMAT A20
SELECT * FROM (SELECT D.OWNER,D.SEGMENT_NAME,(SUM(D.BYTES)/1024/1024) SIZE_M
FROM DBA_SEGMENTS D
GROUP BY D.OWNER,D.SEGMENT_NAME
ORDER BY SIZE_M DESC)
WHERE ROWNUM<=5;
查询表里有多少数据
SELECT D.OWNER,D.TABLE_NAME,D.NUM_ROWS
FROM DBA_TABLES D
WHERE D.TABLE_NAME = 'IDL_UB1$'
AND D.OWNER='SYS';
查看数据库字符集
SELECT USERENV('LANGUAGE') 数据库字符集 FROM DUAL;
SELECT * FROM NLS_DATABASE_PARAMETERS; ------数据库服务器字符集
SELECT * FROM NLS_INSTANCE_PARAMETERS; ------客户端字符集
SELECT * FROM NLS_SESSION_PARAMETERS; ------会话字符集
SELECT * FROM V$NLS_PARAMETER;
SELECT * FROM SYS.PROPS$;
---------------------------
查看创建表的sql语句
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM DUAL;
查看创建索引的sql语句
SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM DUAL;
查看创建主键的sql语句
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;
查看创建外键的sql语句
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_PK_DEPT') FROM DUAL;
查看创建视图(VIEW)的sql语句
SELECT DBMS_METADATA.GET_DDL('VIEW','MY_TABLES','SCOTT') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('VIEW',U.OBJECTNAME) FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW'; SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');
查看创建存储过程(PROCEDURE)的sql语句
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='PROCEDURE';
查看创建触发器(TRIGGER)的sql语句
SELECT DBMS_METADATA.GET_DDL('TRIGGER',U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='TRIGGER';
查看创建函数(FUNCTION)的sql语句
SELECT DBMS_METADATA.GET_DDL('FUNCTION',U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='FUNCTION';
查看创建包(PACKAGE)的sql语句
SELECT DBMS_METADATA.GET_DDL('PACKAGE',U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='PACKAGE';
查看创建序列(SEQUENCE)的sql语句
SELECT DBMS_METADATA.GET_DDL('SEQUENCE',U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='SEQUENCE';
查看创建同义词(SYNONYM)的sql语句
SELECT DBMS_METADATA.GET_DDL('SYNONYM',U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='SYNONYM';
查看创建表空间(TABLESPACE)的sql语句
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',U.TABLESPACE_NAME) FROM USER_TABLESPACES U;
查看创建角色(ROLE)的sql语句
SELECT DBMS_METADATA.GET_DDL('ROLE',U.ROLE_NAME) FROM DBA_ROLES U;
查看创建用户(USER)的sql语句
SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL;
得到一个用户下的所有表、索引、存储过程、函数的ddl语句
SET PAGESIZE 0 SET TRIMSPOOL ON SET LINESIZE 10000 SET LONG 9000 SET FEEDBACK OFF SET FEED OFF SET ECHO OFF SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.OBJECT_NAME) FROM USER_OBJECTS U WHERE U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION');
从dmp文件中获取信息
获取基本信息:
strings czy2.dmp | head -10
获取dmp文件中的表信息:
strings czy2.dmp | grep "CREATE TABLE" | awk '{print $3}' | sed 's/"//g'
解析dmp文件生成parfile文件:
strings czy2.dmp | grep "CREATE TABLE" | awk '{print $3}' | sed 's/"//g' | awk '{if (FNR==1) print "tables="$1 ; else print ","$1 }'
查看dmp字符集方法:
1:imp导入时看
2:cat /tmp/czy2.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 1-2,7-8
然后再数据库中可以查到十六进制代表的字符集:
SELECT NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX')) FROM DUAL;
常用的字符集和对应的数字
SELECT NLS_CHARSET_NAME(TO_NUMBER('0001','XXXX')) US7ASCII, NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX')) ZHS16GBK, NLS_CHARSET_NAME(TO_NUMBER('0369','XXXX')) AL32UTF8, TO_CHAR(NLS_CHARSET_ID('US7ASCII'),'XXXX') US7ASCII, TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'),'XXXX') ZHS16GBK, TO_CHAR(NLS_CHARSET_ID('AL32UTF8'),'XXXX') AL32UTF8 FROM DUAL;
AWR使用
select snap_interval from dba_hist_wr_control; ------snap_interval不能无穷大 SELECT D.INSTANCE_NUMBER,(SYSDATE - D.END_INTERVAL_TIME) INTERVAL FROM DBA_HIST_SNAPSHOT D WHERE D.SNAP_ID = (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT); -INTERVAL列必须大于0 若小于0则 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
1.产生整个数据库的AWR报告,运行脚本awrrpt.sql
@?/rdbms/admin/awrrpt.sql
2.产生某个实例的AWR报告,运行脚本awrrpti.sql
@?/rdbms/admin/awrrpti.sql
3.产生针对不同时段性能的一个对比报告,运行脚本awrddrpt.sql
@?/rdbms/admin/awrddrpt.sql
4.产生某条sql语句的AWR报告,运行脚本awrsqrpt.sql
@?/rdbms/admin/awrsqrpt.sql
另外一种生成AWR报告方法
首先查询数据库快照
SELECT S.SNAP_ID, TO_CHAR(S.BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_INTERVAL_TIME, TO_CHAR(S.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') END_INTERVAL_TIME, S.DBID,S.INSTANCE_NUMBER FROM DBA_HIST_SNAPSHOT S WHERE S.INSTANCE_NUMBER=1 ORDER BY S.SNAP_ID DESC;
要生成127到128之家的快照,可以执行
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1947823303,1,127,128))
获取ADDM报告
1:采用addmrpt.sql脚本。
@?/rdbms/admin/addmrpt.sql