oracle创建用户,赋予权限及常用操作

oracle常见操作,如登录,建表空间,导出,自动备份,存储过程等。

调整oracle会话级参数,使用并行提高效率
1. tablespace
create tablespace tse datafile ‘d:\app\oradata\tse.dbf’ size 20m autoextend on;
drop tablespace tse including contents and datafiles;
create tablespace tse datafile ‘d:\app\oradata\tse.dbf’ reuse autoextend on;
2. imp,exp,impdp expdp
exp TSE/TSE@FY file=’d:\exportdb.dmp’ owner=TSE
imp TSE/TSE@FY file=’exportdb.dmp’ fromuser=TSE touser=TSE log=’d:/l.log’ ignore=Y
使用ignore可只导入数据而忽略建表
指定表的数据:
imp TSE/TSE@FY file=’exportdb.dmp’ fromuser=TSE touser=TSE log=’d:/l.log’ ignore=Y tables=(tb1,tb2,tb3)
当exp无法导出没有数据的表时:
1)、先查询一下当前用户下的所有空表 
select table_name from user_tables where NUM_ROWS=0;
2)
、用以下这句查找空表 
select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where num_rows=0
3)、
把查询结果导出,执行导出的语句 
IMPDP system/manager@ORCL DIRECTORY=CSMISWS DUMPFILE=’NNCMSNN.DMP’  EMAP_SCHEMA=source_schema:target_schema REMAP_TABLESPACE=source_tablespace:target_tablespace
3. functions
wm_concat:
    select wm_concat(c) from tb;
    result: c1,c2,c3
计算时间段的后1/3时
select to_char(to_date(to_char(c.sgdate,’yyyy-MM-dd’)||’ ‘||substr(shijian,instr(shijian,’-‘)+1)||’:00′,’yyyy-MM-dd HH24:mi:ss’)-
(to_date(to_char(c.sgdate,’yyyy-MM-dd’)||’ ‘||substr(shijian,instr(shijian,’-‘)+1)||’:00′,’yyyy-MM-dd HH24:mi:ss’)-
to_date(to_char(c.sgdate,’yyyy-MM-dd’)||’ ‘||substr(shijian,1,instr(shijian,’-‘)-1)||’:00′,’yyyy-MM-dd HH24:mi:ss’))/3,’yyyy-MM-dd HH24:mi:ss’) st,
to_char(to_date(to_char(c.sgdate,’yyyy-MM-dd’)||’ ‘||substr(shijian,instr(shijian,’-‘)+1)||’:00′,’yyyy-MM-dd HH24:mi:ss’),’yyyy-MM-dd HH24:mi:ss’) ed,j.stationid
from wxu_t_sgjh j,wxu_t_sgcomplete c where j.id=c.jhid and c.id=’${url_id}’
1. login:

1) start – run – cmd
   sqlplus /nolog
  conn sys/password as sysdba;
2)  start – run – cmd
sqlplus sys/password as sysdba;
2. create tablespace:
sql>create tablespace mytasp;
3. create user:
1) sql>create user newuser identified by password;
2) sql>create user newuser identified by password default tablespace mytasp;
4. grant :
sql>grant resource to newuser;
sql>grant connect to newuser;
sql>grant create any table,  create any view,  create session to newuser;
sql>grant select any table to newuser;
sql>grant select any dictionary to newuser;
sql>grant unlimited tablespace to newuser;
5. revoke:
sql>revoke resource from newuser;
6. create role:
sql>create role newrole;
7. grant to role:
sql>grant resource to newrole;
8.  add newuser to newrole:
sql>grant newrole to newuser;
9. drop user:
1)sql>drop user newuser;
2)sql>drop user newuser cascade;
10. import data(run in cmd or bat):
imp account/password file=”D:\data\d.dmp” fromuser=fuser  touser=tuser log=”D:\data\logfile.log”
11. export data (can write a bat file and set it running automatically):
set rq=%date:~0,10%
 exp account/password@db file=”d:\data\export%rq%.dmp” log=”d:\data\log”
12. some functions
 instr(str,’a’): equals java’s indexOf
 substr(str,1,length(str)-4): substr and length of a string
concat(str,’%’): concatenate 2 strings
13. 存储过程输出及调试
使用dbms_output.put_line(param);输出。
 调试存储过程:
var ym refcursor;
exec pro_wxu_year(2013,’2a389cdf-999c-417b-946c-b45feb60367a’,:ym);
set serverout on打开pl/sql的输出显示。对于 Oracle Sql developer,DBMS Output窗口,点左上边的点图标,这时会显示’Set serveroutput on’。如下图
Snap1
14. 删除、创建表空间
drop tablespace nnmis including contents and datafiles;
create tablespace nnmis datafile ‘d:\nnmis.dbf’ size 50m autoextend on;
create tablespace nnmis datafile ‘d:\nnmis.dbf’ reuse autoextend on;
=====
create tablespace and create user

/*第1步:创建临时表空间 */
create temporary tablespace csmis_temp
tempfile 'D:\app\60257\oradata\sycsmis\csmis_temp.dbf'
size 50m
autoextend on
next 50m maxsize 10480m
extent management local;
临时表空间加上uniform size 20m;size不要太小。

/*第2步:创建数据表空间 */
create tablespace csmis
logging
datafile 'D:\app\60257\oradata\sycsmis\csmis.dbf'
size 50m
autoextend on
next 50m maxsize 10480m
extent management local;  

/*第2步:创建表空间用户 */
create user sycsmis identified by sycsmis
default tablespace csmis
temporary tablespace csmis_temp;  

/*第4步:给用户授予权限 */
grant connect,resource,dba to sycsmis;
oracle数据库导入脚本
imp sycsmis/sycsmis@sycsmis file=F:\sycsmis20120915.dmp fromuser=sycsmis touser=sycsmis
导出
exp username/passwd@orcl file=c:\a.dmp log=c:\a.log
15. 存储过程示例(an example of procedure):

业务描述:铁路电务段下辖车间,车间下辖工区。由工区的维修年表可生成车间的年表,车间年表可生成段年表;工区的年表可生成月表。 /*********************
根据年表生成月表(工区)
**********************/
create or replace
PROCEDURE PRO_WXU_YTOM
( paramid IN VARCHAR2
, paramonth IN NUMBER
, parazw IN VARCHAR2
, cur_m OUT SYS_REFCURSOR
, cur_c OUT SYS_REFCURSOR
) AS
pyear NUMBER;
porgid VARCHAR2(50);
pcount NUMBER;
pmonmid VARCHAR2(50);
TYPE DaCur is REF CURSOR;
dcur DaCur;
pcounter NUMBER;
pitem VARCHAR2(50);
psql VARCHAR2(1000);
pflowstate NUMBER;
BEGIN
dbms_output.put_line(paramid);
select year,orgid into pyear, porgid from wxu_t_year_m where id= paramid;
select count(*) into pcounter FROM wxu_t_month_m where year= pyear and month= paramonth and orgid= porgid;
dbms_output.put_line(pcounter);
–insert wxu_month_m if not exist,return if not fit conditions.
if pcounter=0 then
dbms_output.put_line(porgid);
insert into wxu_t_month_m(id, year, month,orgid, orglevel_v, flow_state, flow_nodestarttime, flow_orgid, flow_positionid)
values(sys_guid(), pyear, paramonth, porgid,1,1, sysdate, porgid, parazw);
–dbms_output.put_line(‘after insert’);
else
select flow_state into pflowstate FROM wxu_t_month_m where year= pyear and month= paramonth and orgid= porgid;
if pflowstate<>1 then
return;
end if;
end if;
select id into pmonmid FROM wxu_t_month_m where year= pyear and month= paramonth and orgid= porgid;
pcounter:=0;
dbms_output.put_line(pmonmid); –select wxu_year_c of given month
psql:=’select item_d,M’|| paramonth|| ‘ from wxu_t_year_c where mid=”’|| paramid||””;
dbms_output.put_line(psql);
open dcur for psql;
loop
fetch dcur into pitem,pcount;
EXIT WHEN dcur %NOTFOUND;
dbms_output.put_line(pitem);
select count(*) into pcounter from wxu_t_month_c where mid= pmonmid and item_d= pitem;
if pcounter=0 then
insert into wxu_t_month_c(cid,mid,item_d, count)
values(sys_guid(), pmonmid, pitem, pcount);
else
update wxu_t_month_c set count= pcount where mid= pmonmid and item_d= pitem;
end if;
end loop;
close dcur;
dbms_output.put_line(‘generate complete’);
commit; –return records
open cur_m for
select * from wxu_t_month_m where year= pyear and month= paramonth and orgid= porgid;
open cur_c for
select * from wxu_t_month_c where mid in (
select id from wxu_t_month_m where year= pyear and month= paramonth and orgid= porgid
);
END PRO_WXU_YTOM;

16. wm_concat函数(将查询结果连接成一个字符串):

例如:select col from tb; 结果有三条记录,有时需要将这3个连接成一条c1,c2,c3。可使用该函数。

17.不同结果集合并

例如select name from tb1;查询结果 n1,n2; select title from tb2;查询结果t1,t2; 要得到n1,n2,t1,t2四条记录: select name from tb1 union all select title from tb2; select did,DCODE,DTITLE,DTYPE,DAUTHOR,DKEYWORDS,DEDITION,DLANG,DPRESSTIME,DPRESSFIRM,DFORMAT from tb_document union all
select DID,DCODE,DNAME,DTYPE,DAUTHOR,DKEYWORDS,DEDITION,DLANG,DPUBLISHTIME,DPUBLISHFIRM,DPATTERN from tb_docmedia;
select sum(cc) from (select count(*) cc from tb_document union all select count(*) cc from tb_docmedia );

18. 64位oracle sql developer 启动报Unable to find a java Virtual Machine

安装一个32位jdk,打开oracle安装目录中C:\app\用户名\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin目录,sqldeveloper.conf,手动修改

19. 清空回收站 PURGE recyclebin;

20. 不同用户不同表空间导入数据

实现从表空间tbs1用户ur1的一张表table1的数据导入到表空间tbs2用户ur2的表table2。确认用户ur2具有对table1的访问权(可临时赋予dba权限,使用完成后revoke) insert into ur2.table2 (col1,col2,col3) select lum1,lum2,lum3 from ur1.table1; 如果不具有访问权限的其它情况,则可以考虑将table1的数据导出到sql或excel,编辑后导入table2,或者使用imp/exp命令实现。 示例: —————————————
–an example
–synchronise user from csmis to cms
–若出现cms用户无法访问csmis表,
执行GRANT SELECT ANY TABLE TO xncms WITH ADMIN OPTION;
—————————————
create or replace
PROCEDURE “PROC_SYNC_USER” as
pmaxjcuid NUMBER(10,0);
pmaxjouid NUMBER(10,0);
TYPE DaCur is REF CURSOR;
pcur DaCur;
puname VARCHAR2(50 BYTE);
ppwd VARCHAR2(50 BYTE);
BEGIN
dbms_output.put_line(‘begin synchronise user from csmis to cms.’);
delete from jc_user_ext;
delete from jc_user_site where USER_ID!=’1′;
delete from JC_USER_ROLE where USER_ID!=’1′;
delete from JC_CHANNEL_USER where USER_ID!=’1′;
delete from jo_user where username!=’admin’;
delete from jc_user where username!=’admin’;
pmaxjcuid:=2;
pmaxjouid:=2;
open pcur for select name,pwd from NNMIS2.SYS_T_PERSON where name!=’admin’;
loop
fetch pcur into puname, ppwd;
EXIT WHEN pcur %NOTFOUND;
insert into xncms.jo_user(user_id,
username,email,password,REGISTER_TIME,REGISTER_IP,LAST_LOGIN_TIME)
values(pmaxjouid, puname,’a@a.com’, ppwd,sysdate,’127.0.0.1′,sysdate);
insert into xncms.jc_user(user_id,
GROUP_ID,username,email,password,REGISTER_TIME,REGISTER_IP,LAST_LOGIN_TIME)
values(pmaxjcuid,1, puname,’a@a.com’, ppwd,sysdate,’127.0.0.1′,sysdate);
pmaxjouid:= pmaxjouid+1;
pmaxjcuid:= pmaxjcuid+1;
end loop;
dbms_output.put_line(‘end synchronise user from csmis to cms.’);
END PROC_SYNC_USER;

21. 数据库自动备份脚本

@echo off
set rq=%date:~0,10%
exp csmis/csmis@spmmis file=e:/backup/spmmis%rq%.dmp log=e:/backup/spmmis.log
exit

22. 数据库用户密码过期

select username,profile from dba_users;
select * from dba_profiles s where s.profile=’DEFAULT’ and resource_name=’PASSWORD_LIFE_TIME’;
alter profile DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

23. 实体化视图刷新

exec dbms_mview.refresh(‘SYS_MV_ORG_NODEP’) 24.ORACLE卸载 11gr2版本引入了deinstall命令: 使用管理员身份运行C:\Windows\System32\cmd.exe,并执行D:\app\admin\product\11.2.0\dbhome_1\deinstall>deinstall.bat -paramfile D:\app\admin\product\11.2.0\dbhome_1\deinstall\response\deinstall.rsp.tmpl

24. 查询和修改连接数

不同用户的连接数select username,count(username) from v$session where username is not null group by username; 并发连接数Select count(*) from v$session where status=’ACTIVE’; select count(*) from v$session; 系统配置select value from v$parameter where name=’processes’; 修改alter system set processes=1000 scope=spfile;

25. 强制断开用户连接

select sid,serial# from v$session where username=’ERP’; alter system kill session ‘sid,serial’; 例如 alter system kill session ‘222,123’;

26. directory

查看select * from dba_directories; 创建 create directory tempdump as ‘d:\temp’; 授权grant read,write on directory tempdump to CSMIS;

This entry was posted in Computer, Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s