Oracle常用命令

1、创建表空间

1 create tablespace rofly_data
2 datafile 'F:oracledb
ofly_data.dbf'
3 size 500m autoextend on;
4 
5 create temporary tablespace rofly_temp
6 tempfile 'F:oracledb
ofly_temp.dbf'
7 size 300m autoextend on;

View Code

2、创建角色

1 CREATE USER "OAROFLY" PROFILE "DEFAULT" 
2 IDENTIFIED BY "OAPASSWORD" DEFAULT TABLESPACE 
3 "ROFLY_DATA" 
4 TEMPORARY TABLESPACE "ROFLY_TEMP" 
5 ACCOUNT UNLOCK;

View Code

3、赋予权限

1 GRANT "CONNECT" TO "OAROFLY";
2 GRANT "DBA" TO "OAROFLY";

View Code

4、imp导入

1 imp JTOA/OAPASSWORD@orcl ignore=y FILE=E:	est.dmp full=y;

View Code

5、exp导出整个库

1 exp OAROFLY/OAPASSWORD@orcl FILE=E:
ofly_oa.dmp full=y;

View Code

6、exp导出指定用户数据

1 exp OAXY/OAPASSWORD@ORCL FILE=d:oracledbxy_oa_dmp;

View Code

7、oracle创建序列号

1 create sequence JXBrhavior_id_seq start with 1 maxvalue 99999999999999999999 minvalue 1 nocycle cache 20 noorder

View Code

9、Oracle删除用户

1 drop user ×× cascade

View Code

10、Oracle删除表空间

1 DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

View Code

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;

View Code

12、Oracle将锁定的用户解锁

1 alter user username account unlock;

View Code

13、Oracle超级用户登陆

1 sqlplus / as sysdba

View Code

14、Oracle修改用户密码

1 alter user user01 identified by user10;

View Code

15、Oracle指定实例登陆

1 sqlplus system/kingdee@orcl as sysdba;

View Code

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";

View Code

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'

View Code

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;

View Code