Oracle常用命令示例，献给初学者（oracle常用命令大全）

本人自己总结的Oracle常用命令，初学者必备，时不时翻出来参考下。

--查看数据库编码select userenv ('language') from dual;

--创建表空间create tablespace blogdatafile 'd:\blog.dbf'size 10Mautoextend on next 1M;

--删除表空间，两者选其一

drop tablespace blog including contents and datafiles;

drop tablespace blog including contents cascade constraints;

--创建用户create user blog identified by o123456 default tablespace blog;

--删除用户drop user blog cascade;

--解锁用户alter user scott account unlock;

--修改用户密码,10g版本密码第一位需要是字符alter user sh identified by 123456;

--用户授予DBA权限grant dba to blog;

--授予scott用户创建视图权限grant create view to scott ;

--导出数据exp blog/o123456@orcl file=d:\blog.dmp owner=blog

--导入数据imp blog/o123456@orcl file=d:\blog.dmp fromuser=blog touser=blog

--出现oracle数据库未打开异常时

原因：非常规操作删除了数据库的物理文件导致alter database datafile 'd:\blog.dbf' offline drop;

alter database open;

drop tablespace blog including contents;

--常用表操作语句示例

select * from author;update author set optime = systimestamp where aid = 1;

select a.*, to_char(a.birthday,'yyyy-mm-dd'), to_char(a.optime,'yyyy-mm-dd hh24:mi:ss:ff3') from author a;

insert into author values (3,'tianmao','旅游',40,333.22,to_date('2011-02-01','yyyy-mm-dd'),to_timestamp('2017-04-06 10:28:30','yyyy-mm-dd hh:mi:ss'));

create table author_copy as select * from author;

select * from author_copy ;

drop table author_copy ;

delete from author_copy ;

--分页查询

无排序select * from (select m.*, rownum r from myemp m )where r between 1 and 5;

有排序select * from ( select m.*, rownum r from (select * from myemp order by empno) m)where r between 1 and 5;

--增加约束alter table blog add constraint bid_pk primary key (bid);

alter table blog add constraint bid_uq unique (bname);

--删除约束alter table author drop constraint age_ck;

--创建序列create sequence author_seqminvalue 0maxvalue 99999999999start with 1increment by 1;

--创建视图create or replace view emp_user_baseinfoas select empno, ename, job, hiredate from myemp where deptno = 20 ;

--视图增加如下语句表示只读，不能做DML操作with read only ;

--删除视图drop view emp_user_baseinfo;

先分享这么多，后续持续更新…