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 基础查询
- 查询所有的表空间
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
- 查询所有用户
SELECT * FROM DBA_USERS;
- 查询能管理的所有用户
SELECT * FROM ALL_USERS;
- 查询当前用户信息
SELECT * FROM USER_USERS;
- 查找表结构
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 导出带有空数据的表
- 批量处理空表,此方法可以处理所有的空表支持导出
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;
-
再次执行
4.4.2.1 默认导出
的操作即可 -
设置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; --不建缓冲区