oracle 常用命令

--创建表空间
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的利用率