• 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:appAdministratororadataorclTSP_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:APPADMINISTRATORORADATAORCL TSP_YDEMR.DBF’  autoextend on maxsize unlimited;

    3.4. 给表空间增加数据文件:

    alter tablespace xxx add datafile ' F:APPADMINISTRATORORADATAORCL 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>

  • 相关阅读:
    PHP开发APP接口(三)
    PHP开发APP接口(二)
    PHP开发APP接口(一)
    解密PHP模糊查询技术
    流程的问题
    德邦项目《表》
    微信公众号开发1
    在world2013中插入GB_2312
    HTML5笔记(一)
    蓝色文字显示
  • 原文地址:https://www.cnblogs.com/maweiwei/p/7367814.html
Copyright © 2020-2023  润新知