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;
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:\test.dmp full=y;
View Code

5、exp导出整个库

1 exp OAROFLY/OAPASSWORD@orcl FILE=E:\rofly_oa.dmp full=y;
View Code

6、exp导出指定用户数据

1 exp OAXY/OAPASSWORD@ORCL FILE=d:\oracledb\xy_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
posted @ 2019-12-18 20:24  我是段先森  阅读(294)  评论(0编辑  收藏  举报