Oracle表空间等操作
oracle表空间、用户、导入导出相关
1. 导入导出相关：... 2
1.1. oracle 11g"不能使用exp导出空表"的解决方法: 2
1.2. Oracle用imp导入dmp 提示遇到 ORACLE 错误 12560 TNS: 协议适配器错误 解决方法... 2
2. 用户相关... 4
2.1. 使用plsqldev新增用户并赋予dba权限... 4
3. 表空间相关：... 6
3.1. 创建表空间... 6
3.2. exp-imp实现oracle不同表空间的迁移（偷梁换柱）... 7
3.3. 修改表空间数据文件大小为不限制... 8
3.4. 给表空间增加数据文件：... 9
3.5. 知道表空间名，显示该表空间包括的所有表... 9
3.6. 知道表名，查看该表属于哪个表空间... 9
3.7. 查看表空间的名称及大小... 10
3.8. 查看表空间物理文件的名称及大小... 10
3.9. 查看表空间的使用情况... 10
3.10. 查看表空间的使用情况（另一种实现方法）... 11
3.11. 查看数据库的版本... 12
3.12. 查询某张表的大小... 12
4. 带日志drop表：... 13
4.1. drop表用法... 13
1. 导入导出相关：
1.1. oracle 11g"不能使用exp导出空表"的解决方法:
|
在plsql中执行： Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null 上述代码可产生批量的修改表extent的SQL语句(有多少张空表就产生多少条)，我们只需要将其生成的所有sql代码全部执行，就可以给每一张已经存在的表来分配segment，然后在执行exp就OK了。 |
1.2. Oracle用imp导入dmp 提示遇到 ORACLE 错误 12560 TNS: 协议适配器错误 解决方法
|
用imp命令导入dmp文件时提示以下错误：
IMP-00058: 遇到 ORACLE 错误 12560 ； ORA-12560: TNS: 协议适配器错误 ； IMP-00000: 未成功终止导入 ；
解决方法： ---查看以下服务有没有开启 (1)监听服务没有起起来。windows平台个一如下操作：开始---程序---管理工具---服务，打开服务面板， 启动oraclehome92TNSlistener服务。 (2)database instance没有起起来。windows平台如下操作：开始---程序---管理工具---服务，打开服务面板，启动oracleserviceXXXX，XXXX就是你的database SID.
---imp导入语句（mpy就是通过这个方法搞定的） 由原先 imp 用户名/密码 ...写法 改为 imp 用户名/密码@oracle_SID(oracleserviceXXXX,XXXX就是SID) |
2. 用户相关
2.1. 使用plsqldev新增用户并赋予dba权限
3. 表空间相关：
3.1. 创建表空间
创建表空间TSP_YDEMR，oracle数据库用户emr，密码emrpwd，并赋予dba权限：
|
示例： /*第1步：创建数据表空间 */ create tablespace TSP_EMR logging datafile 'F:\app\Administrator\oradata\orcl\TSP_EMR.dbf' size 50m autoextend on maxsize unlimited extent management local;
/*第2步：创建用户并指定表空间 */ create user newemr identified by newemr default tablespace TSP_EMR;
/*第3步：给用户授予权限 */ grant connect,resource,dba to newemr; |
3.2. exp-imp实现oracle不同表空间的迁移（偷梁换柱）
参考http://www.2cto.com/database/201308/235743.html
|
假设： 我们的用户是emr，这个用户的默认表空间是USERS表空间中。并且我们在这个用户下建立有很多的业务表。 现在我们有一个新的用户newemr，他的默认表空间是TSP_EMR，我们希望把emr用户下的所有业务表，都迁移到newemr中。并且存放在新的表空间中。
步骤如下（偷梁换柱）： --1、修改表空间名称(账号：system/Zxcvbnm123 SYSDBA 登录) alter tablespace TSP_EMR rename to TSP_EMR_BAK; alter tablespace USERS rename to TSP_EMR; 执行上面两个语句后，在plsql中可以看到，emr用户下面的任意一个业务表的表空间，已经是TSP_EMR了。
--2、导出用户emr下的业务表，到磁盘文件中 exp emr/Zxcvbnm123@orcl
--3、将表空间名称修改回去(账号：system/Zxcvbnm SYSDBA 登录) alter tablespace TSP_EMR rename to USERS; alter tablespace TSP_EMR_BAK rename to TSP_EMR;
-4、从磁盘文件把数据文件导入到newemr中 imp newemr/newemr@orcl
|
3.3. 修改表空间数据文件大小为不限制
修改表空间数据文件大小为不限制的语句为：
alter datab具体句法文档里一查便知
alert datafile ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\
TSP_YDEMR.DBF’ autoextend on maxsize
unlimited;
3.4. 给表空间增加数据文件：
alter tablespace xxx add datafile ' F:\APP\ADMINISTRATOR\ORADATA\ORCL\
TSP_YDEMR.DBF ' autoextend on maxsize xxx m
具体句法文档里一查便知
3.5. 知道表空间名，显示该表空间包括的所有表
|
知道表空间名，显示该表空间包括的所有表： Select * from all_tables where tablespace_name=’表空间名’; |
3.6. 知道表名，查看该表属于哪个表空间
|
知道表名，查看该表属于哪个表空间： Select tablespace_name, table_name from user_tables where table_name=’table001’ |
3.7. 查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
3.8. 查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
3.9. 查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes 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;
3.10. 查看表空间的使用情况（另一种实现方法）
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
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
3.11. 查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
3.12. 查询某张表的大小
select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='大写的表名
'; --备注，仅表数据的大小，不含索引、分区、LOB类型
4. 带日志drop表：
4.1. drop表用法
|
一. drop table 语法
二. 选项说明 1. schema 可选项，方案，这里可以理解为用户名， 缺省为当前用户下的表。 比如，要删除scott用户下的emp表， drop table scott.emp
2.purge 可选项，如果开启了回收站(oracle 10g以后，回收站默认是开启的)功能， 不带purge选项，表示删除的表放进回收站，空间不回收 。使用flashbask可以闪回该表。 带purge选项，则表示将表删除，释放空间。
3.cascade constraints 可选项,如果有其它表关联到要删除的表的主键列或唯一键列（要删除的表是主表(父表)，从表(子表)有外键关联到这张表），那么，直接用drop table会报错，这时候如果想要强制删除这张表，就需要加上cascade constraints选项
三. 实验 1.cascade constraints选项实验 SQL> drop table t; drop table t * ERROR at line 1: ORA-00942: table or view does not exist
SQL> drop table t1;
Table dropped.
SQL> create table t(id number,name varchar2(20));
Table created.
SQL> create table t1(id number,sal number);
Table created.
SQL> SQL> alter table t add constraint t_pk primary key(id);
Table altered. --在T表上添加主键
SQL> alter table t1 add constraint t_fk foreign key(id) references t(id);
Table altered. --在t1表上添加外键，关联到t表的主键列
SQL> insert into t values (1,'Smith');
1 row created.
SQL> insert into t values (2,'John');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> insert into t1 values(1,3000);
1 row created.
SQL> insert into t1 values(2,4000);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t1 values(3,200); insert into t1 values(3,200) * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.T_FK) violated - parent key not found
SQL> SQL> drop table t; drop table t * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys -- 删除不成功
SQL> drop table t cascade constraints;
Table dropped. --加上cascade constrants项,删除成功
SQL> select * from t1;
ID SAL ---------- ---------- 1 3000 2 4000
SQL> select CONSTRAINT_NAME,TABLE_NAME from dba_constraints where owner='SOTT'and TABLE_NAME='T1';
no rows selected
SQL> 来自CODE的代码片 snippet_file_0.txt
2.purge选项实验 --清空回收站 SQL> Purge recyclebin;
Recyclebin purged.
--建测试表 SQL> create table t(id int,name varchar2(10));
Table created.
--删除表不，加purge选项 SQL> drop table t;
Table dropped.
--删除的表已经放进回收站,只不过改了个表名 SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- T BIN$Nl2YJRjLSyyJ45+XGN7pwA==$0 TABLE 2014-04-04:00:42:10
--闪回t表 SQL> flashback table t to before drop;
Flashback complete.
--t表已经回来了 SQL> select * from t;
no rows selected
--删除t表,加purge选项 SQL> drop table t purge;
Table dropped.
--没有放进回收站,回收站是空的 SQL> show recyclebin; SQL> |
