--创建表空间
create tablespace tablespace_name2
datafile
'D:\tablespace_name2.DBF' size 100M autoextend on next 30M maxsize unlimited
logging extent management local segment space management auto
--创建用户
create user test identified by tiger default tablespace tablespace_name
--为用户分配权限
grant dba to test -- 管理员权限
grant resource to tablespace_name -- 开发者权限(建表||删除表||增删改查数据)
grant connect to tablespace_name -- 浏览者权限(查看)
--删除表空间
drop tablespace tablespace_name;
--删除表空间及数据库文件
drop tablespace tablespace_name2 including contents and datafiles;
--删除用户
drop user test cascade;
select * from dba_data_files t where t.file_name = 'D:\tablespace_name'
--删除表空间
drop tablespace tablespace_name including contents and datafiles cascade constraints;
--EXP,IMP导出 导入
exp XXXXX/orcl@test full=n file=d:\BF.dmp --导出 带数据
exp XXXXX/orcl@test full=n file=d:\BF.dmp rows=n --导出空表
imp XXXXX/orcl@test full=y file=d:\BF.dmp --导入、
--数据泵导入导出
create or replace directory tempdumpdir as 'e:\backup'; --创建数据泵目录
impdp scott/tiger@orcl DIRECTORY=MYIMPFILE DUMPFILE=test.DMP LOGFILE=impscott.log --导入
expdp CDDCJ_META/tiger@orcl directory=tempdumpdir dumpfile=test.DMP logfile=expCDDCJ_META.log version=10.2.0.1.0--指定导出
--Oracle查看用户下的表信息
select table_name,tablespace_name,temporary from
user_tables where tablespace_name = ''
--ORACLE查询字段信息
select column_name
from user_tab_columns where table_name= '' and column_name not like '%'
--ORACLE主键触发器
BEGIN SELECT XL_CQS_CQJSB.NEXTVAL INTO :new.MBBSM FROM dual; END;
--ORACLE解决误删 数据库登陆不进去
SQL> conn sys/orcl as sysdba;
SQL>alter database open resetlogs
SQL>alter database datafile 11 offline drop
SQL>shutdown immediate
SQL>startup
--ORACLE解决跨用户创建视图权限不足
GRANT CREATE ANY TABLE TO USER1;
GRANT SELECT ANY TABLE TO USER1;
GRANT COMMENT ANY TABLE TO USER1;
GRANT LOCK ANY TABLE TO USER1;
GRANT SELECT ANY DICTIONARY TO USER1;
--查所有用户名
select * from dba_users order by username
--查某用户下的表名
select table_name from dba_tables
--ORACLE查询字段信息
select *
from DBA_tab_columns where OWNER = '用户名' and table_name = ‘表名'
--查询某用户下视图信息
select * from DBA_views where OWNER = '用户名''
--查询某用户下函数信息
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='FUNCTION' and OWNER = '用户名''
--查询否用户下存储过程名
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' and OWNER = '用户名''
--查找主键名称
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name =
--根据表名查询此表全部字段和类型ORACLE版
select COlUMN_NAME as columnName,DATA_TYPE as columnType from DBA_tab_columns where OWNER = '{0}' and table_name = 'tableName'
--查找第一个列的名称
select column_name from user_tab_columns where table_name= 'table_name' and column_ID = '1'
--设置表的某列非空
ALTER TABLE "test01"."AFTEST" MODIFY ( "TEST" NUMBER NOT NULL) ;
--设置表的某列为主键
ALTER TABLE "test01"."AFTEST" ADD PRIMARY KEY ("TEST");
--建立序列
CREATE SEQUENCE "test01"."XL_AFTEST"
MINVALUE 1 MAXVALUE 99999999999 INCREMENT
BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
--建立ORACLE主键自增触发器
CREATE OR REPLACE TRIGGER "test01"."ZJ_AFTEST"
BEFORE INSERT ON "test01"."AFTEST"
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
BEGIN SELECT XL_AFTEST.NEXTVAL INTO :new.TEST FROM dual; END;
--改变已有表空间大小
alter database datafile e:\app\oracle\data\test.dbf' resize 1000M;
--设置已有表空间文件自增大小
alter database datafile 'e:\app\oracle\data\test.dbf' autoextend on next 10m
--查看sql对应的会话
select sql_id from v$sql where sql_text like '%***%'
select * from v$session where sql_id='6vmyv89tt9w9m'
select * from dba_objects where object_name like upper('')
select * from v$lock where id1=52343
查看等待事件的会话
select * from gv$session where event in ('db file scattered read','db file sequential read','PX Deq: Execution Msg','read by other session')
批量分析表
select 'analyze table '|| owner||'.'||table_name||' compute statistics' from dba_tables where owner in ('ZSYW','RESURVEY')
1、查询系统中长时间执行的SQL操作,顺序排列:
select sid, message from v$session_longops order by start_time;
2、查询当前正在执行的SQL完成比率:
select sid, opname, target_desc, sofar, totalwork, trunc(sofar/totalwork*100,2) || '%' as perwork
from v$session_longops where sofar != totalwork;
3、查看前五个最占用CPU的Oracle会话进程
#!/bin/bash
ps -e -o pcpu -o pid -o user -o
args | grep oraclemktdb | sort -k 1| tail -5r
spid=`ps -e -o pcpu -o pid -o user -o
args | grep oraclemktdb | sort -k 1| tail -5r | awk '{print $2}'`
for i in $spid
do
sqlplus -S /nolog << EOF
conn / as sysdba
set feedback off
set linesize 200
set pagesize 70
column spid format 99999
column sid format 99999
column module format a20
column username format a8
column sql_text format a60
select distinct c.spid,b.sid,b.username,
a.module,a.hash_value,sql_text
from v$sql a,v$session b,v$process c
where a.hash_value=b.sql_hash_value and
a.address=b.sql_address and b.paddr=c.addr and c.spid =$i;
exit
EOF
done
----
Select a.Username, a.SID, a.SERIAL# ,b.Sql_Text,c.spid
From V$Session a, V$Sqlarea b,v$process c
Where a.Username 'HYJ'
And a.Username Is Not Null
And (a.Sql_Address = b.Address Or a.Prev_Sql_Addr = b.Address)
and a.PADDR=c.ADDR
and c.SPID='860464'
4、V$SESSION,LAST_ET_CALL 可以看到当前SQL已经执行的时间
5、数据库中有长时间不退出的会话存在,查询语句如下:
SELECT B.WAIT_CLASS, B.SECONDS_IN_WAIT/3600 WAIT_HOUR, A.LOCKED_MODE,
C.OBJECT_NAME, D.STATE, D.EVENT , B.SID
FROM V$LOCKED_OBJECT A, V$SESSION B, ALL_OBJECTS C, V$SESSION_WAIT D
WHERE A.SESSION_ID = B.SID AND A.OBJECT_ID = C.OBJECT_ID AND D.SID = B.SID
数据库管理员可以执行下述语句来查看SQL语句的解析情况:
SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse_time_cpu','parse_time_elapsed','parse_count_ hard');
这里:
①parse_time_cpu:是系统服务时间。
②parse_time_elapsed:是响应时间。
而用户等待时间为:
waite_time = parse_time_elapsed – parse_time_cpu
(2)
数据库管理员还可以通过下述语句,查看低效率的SQL语句:
SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA;
优化这些低效率的SQL语句也有助于提高CPU的利用率
create tablespace tablespace_name2
datafile
'D:\tablespace_name2.DBF' size 100M autoextend on next 30M maxsize unlimited
logging extent management local segment space management auto
--创建用户
create user test identified by tiger default tablespace tablespace_name
--为用户分配权限
grant dba to test -- 管理员权限
grant resource to tablespace_name -- 开发者权限(建表||删除表||增删改查数据)
grant connect to tablespace_name -- 浏览者权限(查看)
--删除表空间
drop tablespace tablespace_name;
--删除表空间及数据库文件
drop tablespace tablespace_name2 including contents and datafiles;
--删除用户
drop user test cascade;
select * from dba_data_files t where t.file_name = 'D:\tablespace_name'
--删除表空间
drop tablespace tablespace_name including contents and datafiles cascade constraints;
--EXP,IMP导出 导入
exp XXXXX/orcl@test full=n file=d:\BF.dmp --导出 带数据
exp XXXXX/orcl@test full=n file=d:\BF.dmp rows=n --导出空表
imp XXXXX/orcl@test full=y file=d:\BF.dmp --导入、
--数据泵导入导出
create or replace directory tempdumpdir as 'e:\backup'; --创建数据泵目录
impdp scott/tiger@orcl DIRECTORY=MYIMPFILE DUMPFILE=test.DMP LOGFILE=impscott.log --导入
expdp CDDCJ_META/tiger@orcl directory=tempdumpdir dumpfile=test.DMP logfile=expCDDCJ_META.log version=10.2.0.1.0--指定导出
--Oracle查看用户下的表信息
select table_name,tablespace_name,temporary from
user_tables where tablespace_name = ''
--ORACLE查询字段信息
select column_name
from user_tab_columns where table_name= '' and column_name not like '%'
--ORACLE主键触发器
BEGIN SELECT XL_CQS_CQJSB.NEXTVAL INTO :new.MBBSM FROM dual; END;
--ORACLE解决误删 数据库登陆不进去
SQL> conn sys/orcl as sysdba;
SQL>alter database open resetlogs
SQL>alter database datafile 11 offline drop
SQL>shutdown immediate
SQL>startup
--ORACLE解决跨用户创建视图权限不足
GRANT CREATE ANY TABLE TO USER1;
GRANT SELECT ANY TABLE TO USER1;
GRANT COMMENT ANY TABLE TO USER1;
GRANT LOCK ANY TABLE TO USER1;
GRANT SELECT ANY DICTIONARY TO USER1;
--查所有用户名
select * from dba_users order by username
--查某用户下的表名
select table_name from dba_tables
--ORACLE查询字段信息
select *
from DBA_tab_columns where OWNER = '用户名' and table_name = ‘表名'
--查询某用户下视图信息
select * from DBA_views where OWNER = '用户名''
--查询某用户下函数信息
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='FUNCTION' and OWNER = '用户名''
--查询否用户下存储过程名
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' and OWNER = '用户名''
--查找主键名称
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name =
au.constraint_name and au.constraint_type = 'P' and au.table_name = '***'
--根据表名查询此表全部字段和类型ORACLE版
select COlUMN_NAME as columnName,DATA_TYPE as columnType from DBA_tab_columns where OWNER = '{0}' and table_name = 'tableName'
--查找第一个列的名称
select column_name from user_tab_columns where table_name= 'table_name' and column_ID = '1'
--设置表的某列非空
ALTER TABLE "test01"."AFTEST" MODIFY ( "TEST" NUMBER NOT NULL) ;
--设置表的某列为主键
ALTER TABLE "test01"."AFTEST" ADD PRIMARY KEY ("TEST");
--建立序列
CREATE SEQUENCE "test01"."XL_AFTEST"
MINVALUE 1 MAXVALUE 99999999999 INCREMENT
BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
--建立ORACLE主键自增触发器
CREATE OR REPLACE TRIGGER "test01"."ZJ_AFTEST"
BEFORE INSERT ON "test01"."AFTEST"
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
BEGIN SELECT XL_AFTEST.NEXTVAL INTO :new.TEST FROM dual; END;
--改变已有表空间大小
alter database datafile e:\app\oracle\data\test.dbf' resize 1000M;
--设置已有表空间文件自增大小
alter database datafile 'e:\app\oracle\data\test.dbf' autoextend on next 10m
--查看sql对应的会话
select sql_id from v$sql where sql_text like '%***%'
select * from v$session where sql_id='6vmyv89tt9w9m'
select * from dba_objects where object_name like upper('')
select * from v$lock where id1=52343
查看等待事件的会话
select * from gv$session where event in ('db file scattered read','db file sequential read','PX Deq: Execution Msg','read by other session')
批量分析表
select 'analyze table '|| owner||'.'||table_name||' compute statistics' from dba_tables where owner in ('ZSYW','RESURVEY')
1、查询系统中长时间执行的SQL操作,顺序排列:
select sid, message from v$session_longops order by start_time;
2、查询当前正在执行的SQL完成比率:
select sid, opname, target_desc, sofar, totalwork, trunc(sofar/totalwork*100,2) || '%' as perwork
from v$session_longops where sofar != totalwork;
3、查看前五个最占用CPU的Oracle会话进程
#!/bin/bash
ps -e -o pcpu -o pid -o user -o
args | grep oraclemktdb | sort -k 1| tail -5r
spid=`ps -e -o pcpu -o pid -o user -o
args | grep oraclemktdb | sort -k 1| tail -5r | awk '{print $2}'`
for i in $spid
do
sqlplus -S /nolog << EOF
conn / as sysdba
set feedback off
set linesize 200
set pagesize 70
column spid format 99999
column sid format 99999
column module format a20
column username format a8
column sql_text format a60
select distinct c.spid,b.sid,b.username,
a.module,a.hash_value,sql_text
from v$sql a,v$session b,v$process c
where a.hash_value=b.sql_hash_value and
a.address=b.sql_address and b.paddr=c.addr and c.spid =$i;
exit
EOF
done
----
Select a.Username, a.SID, a.SERIAL# ,b.Sql_Text,c.spid
From V$Session a, V$Sqlarea b,v$process c
Where a.Username 'HYJ'
And a.Username Is Not Null
And (a.Sql_Address = b.Address Or a.Prev_Sql_Addr = b.Address)
and a.PADDR=c.ADDR
and c.SPID='860464'
4、V$SESSION,LAST_ET_CALL 可以看到当前SQL已经执行的时间
5、数据库中有长时间不退出的会话存在,查询语句如下:
SELECT B.WAIT_CLASS, B.SECONDS_IN_WAIT/3600 WAIT_HOUR, A.LOCKED_MODE,
C.OBJECT_NAME, D.STATE, D.EVENT , B.SID
FROM V$LOCKED_OBJECT A, V$SESSION B, ALL_OBJECTS C, V$SESSION_WAIT D
WHERE A.SESSION_ID = B.SID AND A.OBJECT_ID = C.OBJECT_ID AND D.SID = B.SID
数据库管理员可以执行下述语句来查看SQL语句的解析情况:
SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse_time_cpu','parse_time_elapsed','parse_count_ hard');
这里:
①parse_time_cpu:是系统服务时间。
②parse_time_elapsed:是响应时间。
而用户等待时间为:
waite_time = parse_time_elapsed – parse_time_cpu
(2)
数据库管理员还可以通过下述语句,查看低效率的SQL语句:
SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA;
优化这些低效率的SQL语句也有助于提高CPU的利用率