oracle 表的相关操作

1、创建表

1．1．创建堆表

create table NEWEMP

(

  empno    NUMBER(4),

  ename    VARCHAR2(10),

  job      VARCHAR2(9),

  mgr      NUMBER(4),

  hiredate DATE,

  sal      NUMBER(7,2),

  comm     NUMBER(7,2),

  deptno   NUMBER(2)

)

tablespace USERS  --指定表空间

  pctfree 10  --

  initrans 1  --初始化的事务

  maxtrans 255   --最大事务

  storage  --这是存储参数

  (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited 

  );

 

1.2  复制表结构创建表

 

create table newemp as select * from emp;

上面的创建表的方式不仅创建表，而且还会复制表中已经存在的数据，只创建表不复制数据语句如下：

create table newemp as

select * from emp where <> 1;

【注：通过上面的创建表方式，只会复制表结构，但是表的注释是不会有的】

2、修改表

 

3、drop table

drop table newemp purge;

 

purge 关键字表示的是不仅删除表而且在 recyclebin 删除该表的信息，而不保留在回收站中。

4、和表相关的视图

4.1 tab

Tab is included for compatibility. Oracle recommends that you do not use this view.

这个视图可以查看当前schema下的所有的表

4.2 USER_TABLES

user_tables  describes the relational tables owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TABLES. To gather statistics for this view, use the DBMS_STATS package.

 

SELECT TABLE_NAME,TABLESPACE_NAME

from DBA_TABLES

where TABLE_NAME =upper('ACMG_T_EPCUST_LOAD');

 

【基本上所有的视图都有dba_,user_,all_  这三个层次】

 

• 查询emp表中的数据行数

select NUM_ROWS from user_tables where TABLE_NAME = 'EMP';

 

4.3 USER_TAB_COMMENTS

USER_TAB_COMMENTS displays comments on the tables and views owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TAB_COMMENTS.

 

4.4 USER_COL_COMMENTS

USER_COL_COMMENTS displays comments on the columns of the tables and views owned by the current user. Its columns (except for OWNER) are the same as those in ALL_COL_COMMENTS.

 

4.5 USER_TAB_COLS

USER_TAB_COLS describes the columns of the tables, views, and clusters owned by the current user. This view differs from "USER_TAB_COLUMNS" in that hidden columns are not filtered out. Its columns (except for OWNER) are the same as those in "ALL_TAB_COLS". To gather statistics for this view, use the DBMS_STATS package.

 

 

5、对表的操作

5.1 修改表不生成日志

alter table emp nologging;

查看修改后是否产生日志

select a.TABLE_NAME, a.LOGGING

  from user_tables a

 where a.table_name = upper('emp');

让表不产生日志还有一种方法就是在表创建的时候就指定

create table emp_bak (

 empno    NUMBER(4) not null,

  ename    VARCHAR2(10),

  job      VARCHAR2(9),

  mgr      NUMBER(4),

  hiredate DATE,

  sal      NUMBER(7,2),

  comm     NUMBER(7,2),

  deptno   NUMBER(2)

)nologging;

查看是否记录日志

select a.table_name, a.LOGGING

  from user_tables a

 where a.table_name = upper('emp_bak');

 

5.2 为表添加一个字段

alter table newemp add  (nick varchar2(60));

同样的道理，我们可以直接在后面添加多列。

 

ALTER TABLE countries

   ADD (duty_pct     NUMBER(2,2)  CHECK (duty_pct < 10.5),

        visa_needed  VARCHAR2(3));

 

alter table newemp add (a number(22,2),b varchar2(20));

 

5.3 修改表中的触发器不可用

修改表中的出发器可用

alter table newemp enable all triggers;

所有的触发器不可用

alter table newemp disable all triggers;

5.4修改表中的某列字段名（重新命名一个字段）

alter table newemp rename column nick to nickname;

一定不要少了 column 关键字

 

5.5 修改字段的数据类型

alter table newemp modify nickname number(22,2);

-- 将 varchar(60) 改为  number(22,2)

 

【注意：修改字段的数据类型的时候，必须要该表中的数据为空】

 

5.6  为表中的字段添加非空约束

 

alter table newemp modify empno not null;

alter table newemp modify empno null;

 

5.7 删除表中的列

ALTER TABLE hr.admin_emp DROP COLUMN sal;

alter table newemp drop (nickname) cascade constraints;

后面的意思是删除该列上对应的约束。

 

删除单列的时候关键字 column 必须加上。

也可以删除多列：

ALTER TABLE t1 DROP (pk, fk, c1);

alter table newemp drop (a,b);

 

5.8设置列不可用

alter table newemp set unused (b,c);

 

设置一列不可用以后，通过 desc 来看表结构的时候，就看不到该列了。

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to

list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

 

SQL> select * from user_unused_col_tabs;

TABLE_NAME                          COUNT

------------------------------ ----------

NEWEMP                                  2

可以看出有两列 被设置为 unused 状态了

 

 

将列设置为不可用了以后，可以将那些不可用的列删除

alter table newemp drop unused columns  ;

 

5.9将表移动到另外的表空间

ALTER TABLE hr.admin_emp MOVE

      STORAGE ( INITIAL 20K

                NEXT 40K

                MINEXTENTS 2

                MAXEXTENTS 20

                PCTINCREASE 0 )

  TABLESPACE hr_tbs;

5.10将表设置为只读状态

 

ALTER TABLE newemp  READ ONLY;

-- 将表修改为只读模式后，该表是不能够修改的

然后将表修改为 read write 模式

ALTER TABLE newemp READ WRITE;

5.11删除表中的数据

 

truncate table newemp ; -- 快速删除表中的数据，

delete from newemp;  -- 使用 delete 语句删除表中的数据可以 rollback 返回，但是 truncate 删除快，并且数据不能够恢复

 

5.12删除表

DROP TABLE hr.int_admin_emp; -- 只是删除表

DROP TABLE hr.admin_emp CASCADE CONSTRAINTS; --删除表和约束

DROP TABLE hr.admin_emp PURGE; -- 删除表并且从回收站(user_recyclebin)清空(recyclebin)

 

5.13表分析

1、You analyze a schema object (table, index, or cluster) to:

    Collect and manage statistics for it

    Verify the validity of its storage format

    Identify migrated and chained rows of a table or cluster

analyze table emp compute statistics;

 

 

2、DBMS_STATS 来收集

 

With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.

 

GATHER_INDEX_STATS

GATHER_TABLE_STATS

GATHER_SCHEMA_STATS

GATHER_DATABASE_STATS

 

exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp',estimate_percent => 10) ;

 

exec dbms_stats.gather_table_stats('scott','emp2')

 

 

ANALYZE TABLE newemp COMPUTE STATISTICS;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('HR','COUNTRIES');

 

SELECT LAST_ANALYZED, BLEVEL,LEAF_BLOCKS, DISTINCT_KEYS

   FROM DBA_INDEXES WHERE INDEX_NAME= 'PK_ADMIN_DOCINDEX';

 

SELECT table_name, LAST_ANALYZED, NUM_ROWS, BLOCKS, EMPTY_BLOCKS

   FROM DBA_TABLES WHERE IOT_TYPE='IOT_OVERFLOW'

          and IOT_NAME= 'ADMIN_DOCINDEX';

 

5.14修改表字段的默认值

ALTER TABLE APP_CUST_INFO MODIFY guzhu DEFAULT 0;

 

5.15表压缩

CREATE TABLE promotions_var2

    ( promo_id         NUMBER(6)

    , promo_name       VARCHAR2(20)

    , promo_category   VARCHAR2(15)

    , promo_cost       NUMBER(10,2)

    , promo_begin_date DATE

    , promo_end_date   DATE

    , CONSTRAINT promo_id_u UNIQUE (promo_id)) compress basic;

 

 COMPRESS [BASIC]，COMPRESS FOR OLTP，COMPRESS FOR QUERY [LOW|HIGH] ，COMPRESS FOR ARCHIVE [LOW|HIGH]

注意一点

create table emp3 as ( select * from emp)  compress  basic; 这样的处理方式是错误的

 

查看一个表是不是压缩，是怎么压缩的：

SELECT table_name, compression, compress_for FROM user_tables;

声明：本文档仅是自己学习总结，其中有些知识点可能存在错误，若是学友偶然搜到参考，望斟酌后再使用，以免给您带来困扰，若是发现错误也希望您指出更正，在此提前感谢！！  总结过程中要是有些地方借鉴了各路大神成果，您觉得侵犯了您的知识产权，对您有所冒犯，烦请通知鄙人，鄙人将会尽快修正！  邮箱地址：390835164@qq.com