Oracle常用命令
1、创建表空间
1 create tablespace rofly_data 2 datafile 'F:\oracle\db\rofly_data.dbf' 3 size 500m autoextend on; 4 5 create temporary tablespace rofly_temp 6 tempfile 'F:\oracle\db\rofly_temp.dbf' 7 size 300m autoextend on;
2、创建角色
1 CREATE USER "OAROFLY" PROFILE "DEFAULT" 2 IDENTIFIED BY "OAPASSWORD" DEFAULT TABLESPACE 3 "ROFLY_DATA" 4 TEMPORARY TABLESPACE "ROFLY_TEMP" 5 ACCOUNT UNLOCK;
3、赋予权限
1 GRANT "CONNECT" TO "OAROFLY"; 2 GRANT "DBA" TO "OAROFLY";
4、imp导入
1 imp JTOA/OAPASSWORD@orcl ignore=y FILE=E:\test.dmp full=y;
5、exp导出整个库
1 exp OAROFLY/OAPASSWORD@orcl FILE=E:\rofly_oa.dmp full=y;
6、exp导出指定用户数据
1 exp OAXY/OAPASSWORD@ORCL FILE=d:\oracledb\xy_oa_dmp;
7、oracle创建序列号
1 create sequence JXBrhavior_id_seq start with 1 maxvalue 99999999999999999999 minvalue 1 nocycle cache 20 noorder
9、Oracle删除用户
1 drop user ×× cascade
10、Oracle删除表空间
1 DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
11、Oracle 查询数据分页
1 select * 2 from (select ROWNUM as num,A.* from 3 (select * from F_FC_MESSAGE where 1=1 and fcolumnId='8a3084e854d34dd101556703a97629f3' order by FID desc) A where rownum <= 165) 4 where num >= 151;
12、Oracle将锁定的用户解锁
1 alter user username account unlock;
13、Oracle超级用户登陆
1 sqlplus / as sysdba
14、Oracle修改用户密码
1 alter user user01 identified by user10;
15、Oracle指定实例登陆
1 sqlplus system/kingdee@orcl as sysdba;
16、Oracle从回收站中还原已删除的数据表
1 select * from recyclebin where original_name = 'F_I_INSPECT_OTHER_RULE'; 2 3 create table F_I_INSPECT_OTHER_RULE_BAK AS 4 select * 5 from ioffice."BIN$i90r3SvaSo66IED9xsMYIg==$0";
17、Oracle批量删除、禁用、启用约束
1 --删除所有外键约束(复制列表SQL执行) 2 select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R' 3 --禁用所有外键约束(复制列表SQL执行) 4 select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R' 5 --启用所有外键约束(复制列表SQL执行) 6 select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
18、Oracle添加拼音函数提取中文首字母
1 CREATE OR REPLACE FUNCTION GET_PYJM (P_NAME IN VARCHAR2) 2 RETURN VARCHAR2 3 AS 4 V_COMPARE VARCHAR2 (100); 5 V_RETURN VARCHAR2 (4000); 6 BEGIN 7 DECLARE 8 FUNCTION F_NLSSORT (P_WORD IN VARCHAR2) 9 RETURN VARCHAR2 10 AS 11 BEGIN 12 RETURN NLSSORT (P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M'); 13 END; 14 BEGIN 15 FOR I IN 1 .. LENGTH (P_NAME) 16 LOOP 17 V_COMPARE := F_NLSSORT (SUBSTR (P_NAME, I, 1)); 18 19 IF V_COMPARE >= F_NLSSORT ('吖') 20 AND V_COMPARE <= F_NLSSORT ('驁') 21 THEN 22 V_RETURN := V_RETURN || 'A'; 23 ELSIF V_COMPARE >= F_NLSSORT ('八') 24 AND V_COMPARE <= F_NLSSORT ('簿') 25 THEN 26 V_RETURN := V_RETURN || 'B'; 27 ELSIF V_COMPARE >= F_NLSSORT ('嚓') 28 AND V_COMPARE <= F_NLSSORT ('錯') 29 THEN 30 V_RETURN := V_RETURN || 'C'; 31 ELSIF V_COMPARE >= F_NLSSORT ('咑') 32 AND V_COMPARE <= F_NLSSORT ('鵽') 33 THEN 34 V_RETURN := V_RETURN || 'D'; 35 ELSIF V_COMPARE >= F_NLSSORT ('妸') 36 AND V_COMPARE <= F_NLSSORT ('樲') 37 THEN 38 V_RETURN := V_RETURN || 'E'; 39 ELSIF V_COMPARE >= F_NLSSORT ('发') 40 AND V_COMPARE <= F_NLSSORT ('猤') 41 THEN 42 V_RETURN := V_RETURN || 'F'; 43 ELSIF V_COMPARE >= F_NLSSORT ('旮') 44 AND V_COMPARE <= F_NLSSORT ('腂') 45 THEN 46 V_RETURN := V_RETURN || 'G'; 47 ELSIF V_COMPARE >= F_NLSSORT ('妎') 48 AND V_COMPARE <= F_NLSSORT ('夻') 49 THEN 50 V_RETURN := V_RETURN || 'H'; 51 ELSIF V_COMPARE >= F_NLSSORT ('丌') 52 AND V_COMPARE <= F_NLSSORT ('攈') 53 THEN 54 V_RETURN := V_RETURN || 'J'; 55 ELSIF V_COMPARE >= F_NLSSORT ('咔') 56 AND V_COMPARE <= F_NLSSORT ('穒') 57 THEN 58 V_RETURN := V_RETURN || 'K'; 59 ELSIF V_COMPARE >= F_NLSSORT ('垃') 60 AND V_COMPARE <= F_NLSSORT ('擽') 61 THEN 62 V_RETURN := V_RETURN || 'L'; 63 ELSIF V_COMPARE >= F_NLSSORT ('嘸') 64 AND V_COMPARE <= F_NLSSORT ('椧') 65 THEN 66 V_RETURN := V_RETURN || 'M'; 67 ELSIF V_COMPARE >= F_NLSSORT ('拏') 68 AND V_COMPARE <= F_NLSSORT ('瘧') 69 THEN 70 V_RETURN := V_RETURN || 'N'; 71 ELSIF V_COMPARE >= F_NLSSORT ('筽') 72 AND V_COMPARE <= F_NLSSORT ('漚') 73 THEN 74 V_RETURN := V_RETURN || 'O'; 75 ELSIF V_COMPARE >= F_NLSSORT ('妑') 76 AND V_COMPARE <= F_NLSSORT ('曝') 77 THEN 78 V_RETURN := V_RETURN || 'P'; 79 ELSIF V_COMPARE >= F_NLSSORT ('七') 80 AND V_COMPARE <= F_NLSSORT ('裠') 81 THEN 82 V_RETURN := V_RETURN || 'Q'; 83 ELSIF V_COMPARE >= F_NLSSORT ('亽') 84 AND V_COMPARE <= F_NLSSORT ('鶸') 85 THEN 86 V_RETURN := V_RETURN || 'R'; 87 ELSIF V_COMPARE >= F_NLSSORT ('仨') 88 AND V_COMPARE <= F_NLSSORT ('蜶') 89 THEN 90 V_RETURN := V_RETURN || 'S'; 91 ELSIF V_COMPARE >= F_NLSSORT ('侤') 92 AND V_COMPARE <= F_NLSSORT ('籜') 93 THEN 94 V_RETURN := V_RETURN || 'T'; 95 ELSIF V_COMPARE >= F_NLSSORT ('屲') 96 AND V_COMPARE <= F_NLSSORT ('鶩') 97 THEN 98 V_RETURN := V_RETURN || 'W'; 99 ELSIF V_COMPARE >= F_NLSSORT ('夕') 100 AND V_COMPARE <= F_NLSSORT ('鑂') 101 THEN 102 V_RETURN := V_RETURN || 'X'; 103 ELSIF V_COMPARE >= F_NLSSORT ('丫') 104 AND V_COMPARE <= F_NLSSORT ('韻') 105 THEN 106 V_RETURN := V_RETURN || 'Y'; 107 ELSIF V_COMPARE >= F_NLSSORT ('帀') 108 AND V_COMPARE <= F_NLSSORT ('咗') 109 THEN 110 V_RETURN := V_RETURN || 'Z'; 111 END IF; 112 END LOOP; 113 114 RETURN V_RETURN; 115 END; 116 END;
