Oracle-数据库对象(objects)

1. TABLE

1.1 创建表语法

storage部分通常不用指定，为默认值。

create table table_name(column type,column2 type)
tablespace 'tablespace_name'
storage
(a
initial                 --指定初始区的大小
next                    --指定下一个区的大小
minextents         --指定最少要分配多少个区
maxextents        --指定区的最大数量
)

1.2 只创建表结构

create table table_name as  select * from table_name where 1=0; 

1.3 常用对表的操作

# 不经过回收站,删除表
drop table schema.table_name purge;

# 新增列
alter table table_name add (column type);

# 修改表的类型，默认值
alter table table_name modify (column type);
alter table table_name modify (column default '');

# 改变列的数值类型  这一列必须为空
desc table_name  --原先  name number 
alter table table_name modify (name varchar2(100);

# 重命名列
alter  table table_name rename column column_name to column_name_new;

# 删除列
alter table table_name drop  (column_name1,column_name2);

# 添加表注释，字段注释
comment  on table table_name is '';
comment on column tablename.column_name is '';

alter session set current_schema=scott; --切换模式
comment on table test01 is '测试表'; --表注释
comment on column test01.id is 'pk_id';  --表字段注释

# 查看注释：
SELECT * FROM DBA_COL_COMMENTS WHERE TABLE_NAME='xx';
SELECT * FROM DBA_TAB_COMMENTSWHERE TABLE_NAME='xx';

1.4 获取建表语句

declare
get_ddl clob;
begin
        c_ddl := dbms_metadata.get_ddl('TABLE','Table_name','owner');
        dbms_output.put_line(get_ddl);
end;
/
# 去掉storage部分:
begin
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
end;
/

set long 9999;
select dbms_metadata.get_ddl('TABLE','BAT_BKJOB_PARA_HIS','GZYY') from dual;

对比Mysql：

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

2. INDEX

2.1 基础语法

create index idx_name_01 on Schema.Tablename1(cloumn_name) 
tablespace tbs_name     --指定表空间
parallel 8              --并行，取决于服务器的繁忙，CPU个数
online                  --在线
nologging ;

alter index idx_name_01 noparallel;

# 删除索引:
drop index idx_name_01;  

# 索引重建
alter index IDX_name rebuild parallel 8 nologging;
alter index IDX_name noparallel;

存在大字段的表迁移

# 移动LOB字段
alter table [table_name] move tablespace [new tablespace];


# 移动(重建)索引
alter table [table_name] move lob ([lobsegment_name]) store as (tablespace [new tablespace]);

alter index [index_name] rebuild tablespace [new tablespace];

3. SEQUENCE

语法:

CREATE SEQUENCE seq_name
       [INCREMENT BY n]
       [START WITH n]
       [{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
       [{CYCLE|NOCYCLE}]
      [{CACHE n|NOCACHE}];

increment by  --自增步长
start with    --从这个初始值开始计算
maxvalue   --最大值
minvalue    --最小值
cycle       --循环,当达到最大值时,不是从start with设置的值开始循环,而是从1开始循环
cache     --设置缓存cache个序列
nocache     --如果系统down掉了或者其它情况将会导致序列不连续,防止跳号

示例:

create sequence emp_seq
 start with     1000
 increment by   1
 nocache
 nocycle;

新建序列需要初始化:

SQL> create sequence seq_newsid
  2        start with 1 
  3        increment by 1
  4        maxvalue 10 ;

Sequence created.

SQL> select seq_newsid.currval from sys.dual;  
select seq_newsid.currval from sys.dual
       *
ERROR at line 1:
ORA-08002: sequence SEQ_NEWSID.CURRVAL is not yet defined in this session


SQL> select seq_newsid.nextval from sys.dual;

   NEXTVAL
----------
         1

SQL> select seq_newsid.currval from sys.dual;  

   CURRVAL
----------
         1

# 修改最大值
SQL> alter sequence seq_newsid maxvalue  20;

Sequence altered.

# 查看序列信息：
select   sequence_owner,sequence_name,min_value,max_value,last_number
  from  dba_sequences
 where  sequence_name='SEQ_NEWSID';

4. TRIGGER

需求: 监控登录数据库的用户名，登录时间，主机名等信息。

创建DDL记录表

CREATE TABLE dbwh.ddl_log
(
optime        TIMESTAMP PRIMARY KEY,
instance_name VARCHAR2(40),
ip            VARCHAR2(20),
host          VARCHAR2(255),
os_user       VARCHAR2(255),
isdba         VARCHAR(40),
db_user       VARCHAR2(40),
sid           NUMBER,
module        VARCHAR2(255),
terminal      VARCHAR2(255),
dblink        VARCHAR2(255),
network       VARCHAR2(255),
bg_job_id     VARCHAR2(255),
fg_job_id     VARCHAR2(255),
operation     VARCHAR2(255),
object_type   VARCHAR2(255),
object_name   VARCHAR2(255),
sql_stmt      CLOB
)
partition by range (optime) interval(numtoyminterval(1,'month'))
(partition p1 values less than (to_date('2019-01-01','yyyy-mm-dd')))

创建触发器：

CREATE OR REPLACE TRIGGER sys.trg_logon
   AFTER LOGON
   ON DATABASE
BEGIN
    IF sys_context('USERENV','DATABASE_ROLE') not like '%STANDBY%'
    THEN
    begin
    INSERT INTO dbwh.logon_log
        (optime,
         instance_name,
         ip,
         host,
         os_user,
         isdba,
         db_user,
         sid,
         module,
         terminal,
         dblink,
         network,
         bg_job_id,
         fg_job_id)
    VALUES
        (systimestamp,
         SYS_CONTEXT('USERENV','INSTANCE_NAME'),
         SYS_CONTEXT('USERENV','IP_ADDRESS'),
         SYS_CONTEXT('USERENV','HOST'),
         SYS_CONTEXT('USERENV','OS_USER'),
         SYS_CONTEXT('USERENV','ISDBA'),
         SYS_CONTEXT('USERENV','SESSION_USER'),
         SYS_CONTEXT('USERENV','SESSIONID'),
         SYS_CONTEXT('USERENV','MODULE'),
         SYS_CONTEXT('USERENV','TERMINAL'),
         SYS_CONTEXT('USERENV','DBLINK_INFO'),
         SYS_CONTEXT('USERENV','NETWORK_PROTOCOL'),
         SYS_CONTEXT('USERENV','BG_JOB_ID'),
         SYS_CONTEXT('USERENV','FG_JOB_ID'));
    COMMIT;
    exception when others
    then
        dbms_output.put_line('Logon trigger error !');
    end;
    END IF;
END;