oracle 常用性能查询SQL分享
select count(1) from dictionary;
select * from dba_data_files;
select count(1) from dba_objects t where t.owner='BESTTONE';
select * from dba_tablespaces t where t.tablespace_name='BESTTONE';
select count(1) from dba_tables t where t.owner='BESTTONE';
select t.table_name,t.comments from dictionary t where t.table_name like 'V$%';
select * from dba_constraints;
select * from dba_cons_columns;
select * from dba_tablespaces t where t.tablespace_name='SIM002';
CREATE TABLESPACE SS DATAFILE 'D:\ORACLE\ORADATA\BSTO\SS.DBF' SIZE 1M;
SELECT T.tablespace_name, T.status from DBA_TABLESPACES T;
SELECT T.TABLESPACE_NAME,T.FILE_NAME from DBA_DATA_FILES T;
ALTER TABLESPACE SIM002 OFFLINE;
ALTER TABLESPACE SIM002 ONLINE;
SELECT T.os_username,
T.username,
T.terminal,
DECODE(T.returncode,
'0',
'Connected',
'1005',
'failedNull',
'1017',
'failed'),
to_char(t.timestamp, 'dd-mon-yy hh24:mi:ss'),
to_char(t.logoff_time, 'dd-mon-yy hh24:mi:ss')
from DBA_AUDIT_SESSION T;--尝试登陆审计
select t.action,t.name from audit_actions t;
select * from dba_audit_object;
select * from v$sesstat;
select a.sid,a.value "total cpu time" from v$sesstat a ,v$statname b
where a.STATISTIC#=b.STATISTIC# and b.NAME='cpu used by this session'
order by a.sid;--会话级服务时间值
select sum(time_waited) &quo
相关文档:
||------- 数据库管理 -------||
//显示初始化参数
show parameter;
//数据库表的逻辑备份与恢复
//导出
1.导出表
exp userid=scott/tiger@ORCL tables=(emp) file=d:\e1.dmp
2.导出其他方案的表,首先要确认自己是sysdba身份
exp userid=system@919959ORCL tables=(scott.emp) file=d:\e1.d ......
||------- pl/sql 基础 -------||
pl/procedural language 过程语言
//创建表
SQL> create table mytest(
2 name varchar2(30),
3 pwd varchar2(30));
//创建过程
create procedure sp_pro1 is
create or replace procedure sp_pro1 is --如果存在即 ......
SELECT *
from Ds_WasteBook
WHERE (Ds_WasteBook_Desc IN
(SELECT Ds_WasteBook_Desc
from Ds_WasteBook
GROUP BY Ds_WasteBook_Desc
&nb ......
/*
功能:利用函数创建流水号如:
fx201005260001,
fx201005260002,
fx201005270001
作者:陈永建
创建时间:2010-05-26
*/
use master
go
i ......
用sql*plus或第三方可以运行sql语句的程序登录数据库:
增加一个列:
ALTER TABLE 表名 ADD(列名 数据类型);
如:
ALTER TABLE emp ADD(weight NUMBER(38,0));
修改一个列的数据类型(一般限于修改长度,修改为一个不同类型时有诸多限制):
ALTER TABLE 表名 MODIFY(列名 数据类型);
如:
ALTER TABLE emp MODIFY(wei ......