oracle创建表空间、查询表空间、创建用户、赋用户基本权限、创建表

1 表空间

1.1 创建表空间

create tablespace tablespace_name logging datafile 'D:\app\tablespace\tablespace_name.dbf'
size 1024m autoextend on next 20M extent management local;

名称	描述
datafile	后‘’符号中对应的是要修改表空间的路径，例如：‘D:\app\tablespace\tablespace_name.dbf’
size	后的值为表空间的大小
tablespace_name	值为表空间的名称
autoextend	后的值为是否要自增长表空间 off为否 on为是
next	后的值为每次增长的大小

1.1.1 修改表空间自增长

如果在创建的时候已经设置了表空间自增，则不需求二次修改了

alter database datafile 'D:\app\tablespace\tablespace_name.dbf' AUTOEXTEND ON NEXT 200M;

名称	描述
datafile	后‘’符号中对应的是要修改表空间的路径，例如：‘D:\app\tablespace\tablespace_name.dbf’
autoextend	后的值为是否要自增长表空间 off为否 on为是
next	后的值为每次增长的大小

1.1.2 查询表空间剩余大小

select a.tablespace_name,
a.bytes/(1024*1024*1024) total,
b.bytes/(1024*1024*1024) used,
c.bytes/(1024*1024*1024) free,
(b.bytes*100)/a.bytes "%used",
(c.bytes*100)/a.bytes "%free"
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c
where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

名称	描述
total	表空间总大小
used	表空间使用大小
free	表空间剩余大小
%used	表空间使用百分比
%free	表空间剩余百分比

2 用户

2.1 创建用户

create user user_name identified by user_password default tablespace tablespace_name;

名称	描述
user_name	值为用户的名称
user_password	值为用户的密码
tablespace_name	默认表空间名称

2.1.1 配置用户的默认表空间

如果在创建的时候已经设置了默认表空间，则不需求二次修改了

alter user user_name default tablespace tablespace_name;

名称	描述
user_name	值为用户名称
tablespace_name	值为表空间的名称

注：默认表空间一个用户只能有一个

2.2 配置用户权限

grant connect,resource,dba to user_name;
grant connect to user_name with admin option;

名称	描述
connect	为登录、连接权限
dba	为创建数据结构权限
resource	创建实体权限

3 查询

3.1 基础查询

1. 查询所有的表空间

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

2. 查询所有用户

SELECT * FROM DBA_USERS;

3. 查询能管理的所有用户

SELECT * FROM ALL_USERS;

4. 查询当前用户信息

SELECT * FROM USER_USERS;

5. 查找表结构

select * from user_tab_columns where Table_Name='tableName';  --当前用户下的表的信息

select * from all_tab_columns where Table_Name='tableName';   --所有表的信息

select * from dba_tab_columns where Table_Name='tableName';   --dba下的表的信息

形成批量修改表字段类型的sql

SELECT
	column_name,
	'ALTER table tableName modify ' || column_name || ' NVARCHAR2(' || char_length || ')' 
FROM
	all_tab_columns 
WHERE
	Table_Name = 'tableName' 
	AND data_type = 'NVARCHAR2';

4 导出导出数据

4.1 执行环境

exp/imp可以在客户端执行也可以在服务器端执行，在客户端执行需要先安装有oracle的客户端,如果是linux系统，就是以oracle用户登录，在控制台下执行。建议在服务器端执行exp，备份速度快。

4.2 如果运行的时候提示没有exp命令则进入到oracle目录

cd /u01/app/oracle/product/11.2.0/dbhome_1/bin

4.3 配置临时环境变量

export ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1/"
export ORACLE_SID=glassfish

4.4 执行导出

4.4.1 切换到oracle用户

su oracle

4.4.2 导出文件

4.4.2.1 默认导出

导出的文件是带有数据的，如果表中没有数据则不会导出（oracle 11g的机制如果没有数据则不分配segment）此操作在系统层级下执行即可

exp userName/pwd@orcl file="/home/backup/tablespaceName.dmp"

名称	描述
userName	用户名称
pwd	密码
orcl	实例名
file	导出的目录，可以新建确保导出的目录权限够用
tables	需要导出的表，如果要导出全部的表则可以不写 例如 tables=tb_test,tb_user
STATISTICS	统计对象，如果exp导出的时候出现EXP-00091: Exporting questionable statistics.错误可以把此参数设置为no 例如 statistics=no

4.4.2.2 导出带有空数据的表

1. 批量处理空表，此方法可以处理所有的空表支持导出

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

执行上述查询，并且将查询的结果复制在执行

例如：

alter table CRM_FORM_DESC_CUSTOMER allocate extent;
alter table CRM_FORM_DESC_CUSTOMER_CORP allocate extent;
alter table CRM_FORM_DESC_DATA allocate extent;
alter table CRM_FORM_DESC_SERVICE allocate extent;

2. 再次执行4.4.2.1 默认导出 的操作即可

3. 设置deferred_segment_creation参数，此参数只对新增的表影响，但是以前是空的表依旧不能导出。如果后续的表没有特殊要求可以不使用此方法

alter system set deferred_segment_creation=false; 

4.5 执行导入

4.5.1 切换oracle用户

su oracle

4.5.2 导入文件

imp userName/pwd@orcl file="/home/backup/tablespaceName.dmp"

名称	描述
userName	用户名称
pwd	密码
orcl	实例名
file	导出的目录，可以新建确保导出的目录权限够用
full	y-导入全部的数据库忽视对应的用户 n-不忽视用户会提示错误，默认为n 例如 full=y

5 创建序列

create sequence SQ_Name
minvalue 1  --最小值
maxvalue 99999999 --最大的值
start with 1   --开始的数值
increment by 1  --每次增长的数值
NOCYCLE -- 一直累加，不循环
nocache; --不建缓冲区