Oracle数据库的常见操作记录

来源：互联网发布：java ldap 编辑：程序博客网时间：2024/05/22 06:09

1.查看所有序列

select * from all_sequences;

2.创建序列

create sequence HST_SEQ_MESSAGE_CONSULTING--序列名
minvalue 1 --最小从1开始
NOMAXvalue --没有最大的上限
start with 1 --从1开始
increment by 1 --每次增加1
nocache;

3.创建索引

create unique index "HSTSOUTH"."HST_SEQ_MESSAGE_CONSULTING" on "HSTSOUTH"."HST_MESSAGE_CONSULTING"("hmcuuid");

4.删表

drop table HST_MESSAGE_CONSULTING(表名);

5.模糊查询

select * from HST_NEWS where TITLE like '%心(查询的关键字)%' and DEPTMENTID = 62;

6.提交

commit

7.删除序列

DROP SEQUENCE HST_SEQ_MESSAGE_ADVISORY;

8.删除表数据

delete from HST_MESSAGE_CONSULTING;

9.过滤掉重复

--可以使用group by,也可以使用distinct

10.创建两张带有关联字段的表sql

create table HST_MESSAGE_ADVISORY
(
   HMAUUID              NUMBER               not null,
   HWUUUID              NUMBER,
   HCONTENT             VARCHAR2(500),
   HFTIME               DATE,
   AUTH                 NUMBER,
   STATUS               NUMBER,
   constraint PK_HST_MESSAGE_ADVISORY primary key (HMAUUID)
);

/*==============================================================*/
/* Table: HST_MESSAGE_CONSULTING                                */
/*==============================================================*/
create table HST_MESSAGE_CONSULTING
(
   HMCUUID              NUMBER               not null,
   HMAUUID              NUMBER,
   HWUUUID              NUMBER,
   STARTTIME            DATE,
   TIMEEND              DATE,
   SPEED                INTEGER,
   STATUS               INTEGER,
   HWCUUID              NUMBER,
   constraint PK_HST_MESSAGE_CONSULTING primary key (HMCUUID)
);

alter table HST_MESSAGE_CONSULTING
   add constraint FK_HST_MESS_REFERENCE_HST_MESS foreign key (HMAUUID)
      references HST_MESSAGE_ADVISORY (HMAUUID);

11.向oracle中导入数据

drop user hstsouth cascade;
create user hstsouth identified by 123456;

grant dba to hstsouth;
grant sysdba to hstsouth;
create directory dir_dp as 'F:\oracle';
Grant read,write on directory dir_dp to hstsouth;
Impdp hstsouth/123456 DIRECTORY=dir_dp DUMPFILE=EXPDP_20161031_ALL.DMP

--1.create tablespace hst datafile 'F:\oracle\hst.dbf' size 100m autoextend on next 32m maxsize 20048m extent management local;
--2.create user hstsouth identified by 123456 default tablespace hst temporary tablespace temp;

12.oracle服务启动监听改为手动

13、oracle异常处理

使用oracle总结
如果oracle连接不上某个数据库的时候，并且报THE NETWORK ADADPTERE错误的时候，用oracle新建数据库连接老是说 THE NETWORK ADAPTER COULD NOT ESTABLISH THE CONNECTION!
答：监听没有启动，命令行用下面的命令启动监听:
lsnrctl start

netca命令是用来进行监听程序配置

0 0