--查看用户默认的表空间.sql：
selectusername,default_tablespacefromdba_users;
--查看各个表空间占用磁盘情况.sql：
select
b.file_id文件ID号,
b.tablespace_name表空间名,
b.bytes/1024/1024||'M'字节数,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M'已使用,
sum(nvl(a.bytes,0))/1024/1024||'M'剩余空间,
100-sum(nvl(a.bytes,0))/(b.bytes)*100占用百分比
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_id,b.bytes
orderbyb.file_id
--以上2者关联，就是查看用户默认表空间使用情况的sql语句：
SELECT*
FROM
(selectusername,default_tablespacefromdba_users)ut,
(select
--b.file_id文件ID号,
b.tablespace_name表空间名,
b.bytes/1024/1024||'M'字节数,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M'已使用,
sum(nvl(a.bytes,0))/1024/1024||'M'剩余空间,
100-sum(nvl(a.bytes,0))/(b.bytes)*100占用百分比
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_id,b.bytes
orderbyb.file_id)tsu
WHEREut.default_tablespace=tsu.表空间名
ORDERBYut.username
--创建表空间
Sql>createtablespaceicbcdbdatafile'D:\oracle\product\10.2.0\oradata\orcl\icbcdb.dbf'size500mautoextendonnext10mmaxsizeunlimited;
Sql>alterdatabasedatafile'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf'autoextendon;
1)DATAFILE:表空间数据文件存放路径
2)SIZE:起初设置为200M
3)UNIFORM:指定区尺寸为128k,如不指定，区尺寸默认为64k
4)空间名称histdb与数据文件名称histdb.dbf不要求相同,可随意命名.
5)AUTOEXTENDON/OFF表示启动/停止自动扩展表空间
6)alterdatabasedatafile'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf'resize500m;//手动修改数据文件大小为500M
--删除表空间
DROPTABLESPACEaudit_icbcdbINCLUDINGCONTENTSANDDATAFILES;
createtablespaceicbcdbdatafile'D:\oracle\product\10.2.0\oradata\orcl\icbcdb.dbf'size1000mautoextendonnext10mmaxsizeunlimited;
CREATEUSERicbc_auditIDENTIFIEDBYicbc_audit
PROFILEDEFAULT
DEFAULTTABLESPACEicbcdb
ACCOUNTUNLOCK;
grantconnect,resource,dbatoicbc_audit;
GRANTDELETEANYTABLETOicbc_audit;
GRANTDROPANYTABLETOicbc_audit;
GRANTINSERTANYTABLETOicbc_audit;
GRANTSELECTANYTABLETOicbc_audit;
GRANTUNLIMITEDTABLESPACETOicbc_audit;
GRANTUPDATEANYTABLETOicbc_audit;
①用户及用户下面所有表的删除
dropusericbc_test02cascade;
②创建用户
createuserfantestidentifiedbyfantest;
③分配表领域
dropuserfantestcascade;
createuserfantestidentifiedbyfantest;
grantconnect,resource,dbatofantest;
GRANTDELETEANYTABLETOfantest;
GRANTDROPANYTABLETOfantest;
GRANTINSERTANYTABLETOfantest;
GRANTSELECTANYTABLETOfantest;
GRANTUNLIMITEDTABLESPACETOfantest;
GRANTUPDATEANYTABLETOfantest;
executedbms_flashback.disable;
grantexecuteondbms_flashbacktofantest;
grantplustracetofantest;
impsystem/dlds@syhxzgfile=d:\hxzg20080421.dmpfull=yignore=y;
－－修改System口令
conn/assysdba;
alterusersystemidentifiedbydlds;
alteruser"SYSTEM"ACCOUNTUNLOCK;
137.202
Server137.202
/********************************************/
createtablet_dep
(
depnovarchar2(8)notnull,
depnamevarchar2(24)
)
/
altertablet_depaddconstraintpk_depnoprimarykey(depno);
createtablet_emp
(
empnovarchar2(8)notnull,
empnamevarchar2(24)notnull,
depnovarchar2(8)notnull,
salarynumber(6),
demovarchar2(200)
)
/
altertablet_empaddconstraintpk_empnoprimarykey(empno);
altertablet_empaddconstraintfk_depnoforeignkey(depno)referencest_dep;
insertintot_depvalues('10','人事部');
insertintot_depvalues('20','软件开发部');
insertintot_depvalues('30','金融事业部');
insertintot_depvalues('40','电信事业部');
insertintot_depvalues('50','社保事业部');
insertintot_empvalues('100001','aa','10',1000,'');
insertintot_empvalues('100002','aa1','10',1500,'');
insertintot_empvalues('100003','aa2','10',1800,'');
insertintot_empvalues('100004','aa3','10',2000,'');
insertintot_empvalues('200001','bb','20',1000,'');
insertintot_empvalues('200002','bb1','20',1500,'');
insertintot_empvalues('200003','bb2','20',1800,'');
insertintot_empvalues('200004','bb3','20',2000,'');
insertintot_empvalues('200005','bb4','20',4000,'');
insertintot_empvalues('300001','cc','30',1000,'');
insertintot_empvalues('300002','cc1','30',6500,'');
insertintot_empvalues('300003','cc2','30',1800,'');
insertintot_empvalues('300004','cc3','30',2000,'');
insertintot_empvalues('300005','cc4','30',4000,'');
insertintot_empvalues('300006','cc5','30',4002,'');
commit;
/*******************************************************/
createorreplaceproceduretest_proc(countsinnumber)is
icountnumber;
CURSORc_cur(p_depnovarchar2)
is
selectdepno,empno,empname,salaryfromt_emp
wheredepno=p_depno
orderbysalarydesc;
v_depnot_emp.depno%type;
begin
forv_depnoin(selectdistinctdepnofromt_emporderbydepno)loop
icount:=0;
--openc_cur(v_depno.depno);
foriinc_cur(v_depno.depno)loop
if(icount dbms_output.put_line(i.depno||''||i.empno||''||i.empname||''||i.salary);
endif;
icount:=icount+1;
endloop;
endloop;
endtest_proc;
/***********************************/
CREATEORREPLACEVIEWV_DEP_STATAS
SELECTb.depno,decode(avg(salary),null,0,avg(salary))asavg_sal,decode(max(salary),null,0,max(salary))asmax_sal,
decode(min(salary),null,0,min(salary))asmin_sal,count(empno)asdep_psncnt
FROMt_empa,t_depb
wherea.depno(+)=b.depno
groupbyb.depno
/*******************************************/
createsequenceSEQ_ID
minvalue1
startwith1
incrementby1;
/**********************************************/
createtablet_log
(
idnumber(8)notnull,
slogvarchar2(256)
)
altertablet_log
addmod_datedate;
/*************************************************/
createorreplacetriggerWrite_Logs
Beforeinsertorupdateordeleteont_emp
FOREACHROW
declare
log_desct_log.slog%type;
tempnot_emp.empno%type;
begin
tempno:=:new.empno;
ifinsertingthen
tempno:=:new.empno;
log_desc:='用户:'||user||'插入'||tempno;
elsifupdatingthen
tempno:=:new.empno;
log_desc:='用户:'||user||'更新'||tempno;
elsifdeletingthen
tempno:=:old.empno;
log_desc:='用户:'||user||'删除'||tempno;
endif;
insertintot_logvalues(SEQ_ID.nextval,log_desc);
end;
deletefromt_empwhereempno='300009';
insertintot_empvalues('300009','cc9','30',5000,'');
deletefromt_empwhereempno='300009';
commit;
selectto_date(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'),'yyyy-mm-ddhh24:mi:ss')fromdual;
createtablet_tmp
(
anumber(2),
bnumber(2),
cnumber(2),
dnumber(2),
enumber(2)
)
/
insertintot_tmpvalues(2,1,4,5,3);
insertintot_tmpvalues(4,2,3,1,5);
commit;
/*************检查锁***************/
SELECT
p.spid
,s.username
,s.sid
,s.serial#
,s.status
,s.osuser
,s.machine
,DECODE(l.lmode,0,'lockwaiter','lockholder')resp
,s.program
,SUBSTR(s.module,INSTR(s.module,'/',-1)+1,20)module
,s.sql_hash_value
,s.sql_address
,l.id1
,l.TYPE
,s.logon_time
,s.paddr
,l.lmode
,l.request
,l.ctime
,s.row_wait_obj#
,s.row_wait_file#
,s.row_wait_block#
FROMv$sessions
,v$processp
,v$lockl
WHEREl.id1IN(SELECTc.id1
FROMv$lockc
WHEREc.lmode=0)
ANDl.sid=s.sid
ANDs.paddr=p.addr
ORDERBYl.id1
,resp
,ctime
,s.logon_time;
/****************查找锁产生的SQL***************/
selects.usernameusername,a.sidsid,a.owner||'.'||a.objectobject,s.lockwait,t.sql_textSQL
fromv$sqltextt,v$sessions,v$accessa
wheret.address=s.sql_address
andt.hash_value=s.sql_hash_value
ands.sid=a.sid
anda.owner!='SYS'
andupper(substr(a.object,1,2))!='V
