Oracle的基本操作

oracle创建过程：

数据库 >  数据库实例 > 表空间 > 用户 > 表

 

数据库

数据库是数据的集合，也即物理数据、内存、操作系统进程的组合体。

查询当前数据库名：

select name from v$database;

 

数据库实例

用来访问和使用数据库的一块进程，它只存在于内存中。

数据库实例名(instance_name)用于对外部连接。在操作系统中要取得与数据库的联系，必须使用数据库实例名。

查询当前数据库实例名：

select instance_name from v$instance;

jdbc:oracle:thin:@localhost:1521:orcl（orcl就为数据库实例名）

一个数据库可以有多个实例，在作数据库服务集群的时候可以用到。

 

表空间

Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。

一个表空间只能属于一个数据库。

创建

创建表空间：

create tablespace tablename  --表名

logging   --日志记录的缺省模式

datafile ‘D:\GPP.dbf’   --数据文件的路径名

size 200m  --文件的大小

autoextend on --设置表空间自动扩展

next 100m   --每次扩展100m

maxsize unlimited  --表示数据文件的最大大小，unlimited 表示无限的表空间

extent management local;  --设置表空间的去管理为本地管理，为的是减少分配extent的时候产生的内部递归sql，提高数据库分配空间的效率.

删除

drop tablespace tablespace_name including contents and datafiles cascade constraint;

//删除表空间

查看

• 查看表空间的大小及名称

SELECT a.tablespace_name "表空间名",

total "表空间大小",

free "表空间剩余大小",

(total - free) "表空间使用大小",

total / (1024 * 1024 * 1024) "表空间大小(G)",

free / (1024 * 1024 * 1024) "表空间剩余大小(G)",

(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",

round((total - free) / total, 4) * 100 "使用率 %"

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

ORDER BY a.tablespace_name

• 查看表空间是否具有自动扩展的能力

SELECT T.TABLESPACE_NAME,D.FILE_NAME,

D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS

FROM DBA_TABLESPACES T,DBA_DATA_FILES D

WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME

ORDER BY TABLESPACE_NAME,FILE_NAME;

 

查看所有表空间数据文件信息

select * from dba_data_files

更新

• 给表空间添加数据文件以达到增加大小

alter tablespace tablename  //需要增加的表空间

add datafile ‘C:\app\ktianc\oradata\orcl\test2.dbf’  //新添加数据文件

size 100m;  //增加的大小

• 给表空间添加数据文件，并且允许数据文件自动增长

alter tablespace tablename

add datafile ‘C:\app\ktianc\oradata\orcl\test2.dbf’

size 200m

autoextend on

next 50m

maxsize unlinited;

• 允许已存在的数据文件自动增长

alter database datafile ‘C:\app\ktianc\oradata\orcl\test2.dbf’

autoextend on

next 100m

maxsize unlimited;

• 改变已存在数据文件的大小

alter database datafile ‘C:\app\ktianc\oradata\orcl\test2.dbf’

resize 100m

 

临时表空间

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象。

当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。

创建

创建临时表空间：

create temporary tablespace temptablename //临时表名

tempfile 'C:\ORADATA\ORCL\TEMP01.DBF'   //临时数据文件的路径名

size 10M;   //文件的大小

更新

• 添加临时表空间大小：

alter tablespace  TEMP_TEST  //临时表空间名称

add tempfile 'C:\app\ktianc\oradata\orcl\TEMP_TEST_2.DBF' //新加临时表空间数据文件

size 100m

autoextend on

next 20m

maxsize unlimited;

• 改变临时表空间数据文件大小

alter database tempfile ‘C:\APP\KTIANC\ORADATA\ORCL\TEMP_TEST.DBF’

resize 200m

删除

删除临时表空间数据文件

alter tablespace TEMP_TEST

drop tempfile ‘C:\app\ktianc\oradata\orcl\TEMP_TEST.DBF’;

查询

• 查询所有临时表空间数据文件信息

select file_name,tablespace_name,

bytes/1024/1024 MB,autoextensible,maxbytes/1024/1024 MAX_MB from dba_temp_files

• 查询所有临时表空间大小

SELECT d.tablespace_name "Name",

TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",

TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",

TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,

 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",

TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"

FROM sys.dba_tablespaces d,

(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,

(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management like 'LOCAL'

AND d.contents like 'TEMPORARY'

用户

Oracle数据库建好后，要想在数据库里建表，必须先为数据库建立用户，并为用户指定表空间。

创建

create user username  --用户名

identified by passwd --密码

default tablespace  tablename  --指定表空间

temporary tablespace temptablename;  --指定临时表空间

 

grant connect,resource to username;  --授权连接与资源权限

grant dba to username;   --授权最高权限，数据库管理权限

删除

drop user username cascade;  //删除用户,同时删除用户下的所有数据对象。

查询

select  * from dba_users;  //查看数据库用户

 

常用查询

user_tables : 当前用户的表

all_tables : 所有用户的表

dba_tables ： 包括系统表在内的所有表

 

user_users : 描述当前用户

all_users : 当前用户可见的所有用户

dba_users : 所有用户

 

default_tablespace : 表空间名称

temporary_tablespae : 临时表空间名称

 

dba_data_files ： 查询表空间信息

dba_temp_files : 查询临时表空间信息

 

导入

imp user/passwd@orcl file=’*.dmp’

导出

exp user/passwd@orcl file=’*.dmp’ log='*.log'

 

导出空表

• 查看当前用户下的空表

select table_name from user_tables where num_rows='0';

• 生成处理语句

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