自己总结点oracle的东西

一、desc 表名 查看表的结构

二、新建用户

create user 用户名 identified by 密码；

给用户授权（新建的用户连登陆的权限也没有，普通用户一般授connect,resouce）

grant connect,resouce to 用户名；

给用户授查询一张表的权限

grant select on 表名 to 用户名；

给用户授予这张表的所有权限

grant all on 表名 to 用户名;

授予的权限可传递（A把自己的表ss的查询权限授予了B，B也可以把这个权限授予C，如果A收回赋予B的权限，C也将丧失这个权限），下面是授予B权限可传递

grant select on ss（表名） to B（用户名） with grant option;

三、创建规则，用户登陆尝试3次，如果三次失败锁定2天

create profile lock_account（规则名称，随意） limit failed_login_attempts 3 password_lock_time 2;

给用户授予这个规则

alter user 用户名 profile lock_account;

解开被所用户

alter user 用户名 account unlock;

规则：每隔十天修改一次登陆密码，宽限期两天,密码十天后可以重新使用

create profile 规则名称 limit password_life_time 10 password_grace_time 2 password_reuse_time 10;

删除规则，如果加这cascade,就是级联，与此相关的全部删除

drop profile 规则名称 [cascade];

四、oracle的基本数据类型

char 定长 最大2000字符 也就是char(2000)

varchar2() 变长 最大4000个字符

clob 字符型大对象，最大4G

number 10^-38~10^38

date 包括年月日时分秒

timestramp 精度比价高，一般用于银行的业务

blob 二进制数据，用于图片/声音（处于安全性可考虑，一般只存路径）

比较：

1、char相对于varchar2效率高，因为定长，检索时全匹配

2、如果value=ZHS16GBK，那么一个汉字占用2个字节，如果value=AL32UTF8，那么一个汉字占用3个字节

select * from v$nls_parameters t where t.PARAMETER='NLS_CHARACTERSET';

五、添加一个字段

alter table 表名 add(字段名 number(2));

删除一个字段（一般别这么干）

alter table 表明 drop column 字段名；

修改表名（一般别用）

rename oldtablename to newtablename;

修改字段的类型

alter table tablename modify(column varchar2(20));

oracle的默认插入日期是日-月-年，“01-5月-2010”，即2010年5月1日，必须带着月，可通过下面语句修改格式

alter session set nls_date_format="yyyy-mm-dd";

也可插入时使用to_date("2010-05-05","YYYY-MM-DD");

查询为空的字段，只能采用这个方式

select * from tablename where id is null;

方法nvl(value,0)函数，如果value为null就置为0

六、设置保存点

savepoint 保存点名称

取消部分事务

rollback to 保存点名称

取消全部事务

rollback

设置只读事务

set transction read only

七、导入导出要在oracle的bin下进行

导出数据

exp userid=用户名/密码@数据库实例 tables=(表名...) file=d:\test.dmp;

导出表结构

exp userid=用户名/密码@数据库实例 tables=(表名...) file=d:\test.dmp rows=n;

使用直接导出

exp userid=用户名/密码@数据库实例 tables=(表名...) file=d:\test.dmp direct=y;

导出数据库（dba权限或者是exp_full_database权限）

exp userid=用户名/密码@数据库实例 full=y inctype=complete(增量备份) file=d:\test.dmp;

导出自己的方案（一个用户对应一个方案schema,方案的schema是数据库对象的集合）

exp userid=用户名/密码@数据库实例 owner=方案名 file=d:\test.dmp;

导出其他方案的表（dba权限）

exp userid=用户名/密码@数据库实例 tables=(方案名.表名) file=d:\test.dmp;

导出其他的方案（需要dba权限）

exp userid=用户名/密码@数据库实例 owner=(方案名) file=d:\test.dmp;

导入自己的表

imp userid=用户名/密码@数据库实例 tables=(表名...) file=d:\test.dmp;

导入数据

imp userid=用户名/密码@数据库实例 tables=(表名) file=d:\test.dmp ignore=y;

八、数据字典：数据库一些系统信息

动态性能视图：实例启动后的系统信息

当前用户所拥有的表

select table_name from user_tables;

当前用户可以访问的所有表

select table_name from all_talbes;

所有方案拥有的所有的表

select table_name from dba_tables;

显示所有数据库用户的详细信息

dba_users

用户所具有系统权限

dba_sys_privs

用户所具有对象权限

select distinceet privilege from dba_tab_privs

用户所具有的角色

dba_role_privs

oracle所有的角色

dba_role

查询oracle所有的系统权限

select * from system_privilege_map order by name;

查询数据库表空间

select tablespace_name from dba_tablespaces;

显示当前用户可以访问的所有数据字典视图

select * from dict where comments like '%grant%';

显示当前数据库的全称

select * from global_name;

九、

创建一个data01的表空间，最大不能超过500M，块128k

create tablespace data01 datafile 'd:\test\data01.dbf' size 20m uniform size 128k;

往表空间中建表

create table tablename tablespace data01;

使表空间脱机

alter tablespace 表空间名 offline

使表空间联机

alter tablespace 表空间名 online

只读表空间

alter tablespace 表空间 read only；

写表空间

alter tablespace 表空间 read write；

显示表空间的所有表

select * from all_tables where tablespace ='表空间名‘;

查询表所属表空间

select tablespace_name,table_name from user_tables where table_name='表名'；

删除表空间,contents所有的数据库对象，datafiles数据库文件

drop tablespace '表空间’ including contents and datafiles;

增加表空间的三种方法（第三种还凑合）

1、增加数据文件

alter tablespace 表空间名 add datafile 'd:\test.dbf' size 20m;

2、增加数据文件的大小

alter tablespace 表空间名 'd:\test.dbf' resize 20m;

3、设置文件的自动增长

alter tablespace 表空间名 'd:\test.dbf' auto extend on next 10m maxsize 500m;

移动数据文件test.dbf

1、确定文件所在的表空间

select tablespace_name form dba_data_files where file_name='d:\test.dbf';

2、使表空间脱机

alter tablename 表空间名 offline;

3、使用命令移动数据文件到指定的目标位置

host move d:\test.dbf c:\test.dbf;

4、逻辑性数据文件移动

alter tablespace test rename datafile 'd:\test.dbf' to 'c:\test.dbf';

5、使表空间联机

alter tablespace test online;

十、not null 不为空

unique 唯一

primary key 主键

foreign key 外键

check 检查

删除一个表的约束名称

alter table 表明 drop constraint 约束名称

当前用户的约束信息

user_constrains

列级定义

id number(10) primary key

表级定义

id number(10)

constraint pk_name primary key(id)

十一，分页sql核心语句

select * from (select table.* ,rownum rn from (select * from tablename) table where rownum < 10) where rn>6;