1、创建新表流程

 

1.1 建表语句（table.sql或sns_table.sql)

CREATE TABLE tb_image
(
    image_id      NUMBER(16) NOT NULL,
    suffix          VARCHAR2(4) NOT NULL,
    scenario      CHAR(1) DEFAULT 0 NOT NULL,
    creater        NUMBER(16) NOT NULL,
    create_time  DATE NOT NULL,
    CONSTRAINT pk_image
        PRIMARY KEY (image_id)   USING INDEX TABLESPACE CATIDX,
    CONSTRAINT fk_image_creater
        FOREIGN KEY (creater) REFERENCES tb_user(user_id)
) TABLESPACE CAT;

1) 如果字段名为关键字，则加双引号，例如："option"
2) 如果创建Unique，在外键声明下加上如下语句：CONSTRAINT UC_tb_visitor UNIQUE (mail) USING INDEX TABLESPACE ADMIDX
3) PK，FK，Unique命名请保持统一

1.2 创建sequence（sequence.sql或sns_sequence.sql） - 假如新表不需要sequence的话，则跳过

CREATE SEQUENCE seq_user MINVALUE 1000000000100001 MAXVALUE 9999999999999999 START WITH 1000000000100001 INCREMENT BY 1 NOCACHE;

1.3 授权（privilege.sql和sns_privilege.sql）

1）给table创建同义词
CREATE PUBLIC SYNONYM tb_acc_account FOR tb_acc_account;
2）给sequence创建同义词
CREATE PUBLIC SYNONYM seq_acc_journal FOR seq_acc_journal;
3）给table授权（根据相应的需求授予SELECT, INSERT, DELETE, UPDATE权限）
GRANT SELECT, INSERT, DELETE, UPDATE ON tb_activation_link TO om_app_role;
4）给sequence授权
GRANT SELECT ON seq_acc_journal TO om_app_role;

2、修改表结构

- 需要同步更新table.sql或sns_table.sql

2.1 重命名表字段

ALTER TABLE tb_user RENAME COLUMN test_field TO test_field2;

2.2 增加新的字段

ALTER TABLE tb_user ADD test_field NUMBER(16) DEFAULT 0 NOT NULL;

2.3 删除字段

ALTER TABLE tb_user DROP COLUMN test_field;

2.4 更改字段

ALTER TABLE tb_user MODIFY test_field VARCHAR2(16) DEFAULT '0' NULL;

2.5 特殊情况

1) 例如修改字段类型VARCHAR2(2000)到CLOB,不能直接使用MODIFY，这时按以下流程操作：
ALTER TABLE tb_user ADD test_field2 CLOB DEFAULT 'NA' NOT NULL;
UPDATE tb_user SET test_field2=test_field;
COMMIT;
ALTER TABLE tb_user DROP COLUMN test_field;
ALTER TABLE tb_user RENAME COLUMN test_field2 TO test_field;

2.6 增加主键，外键，UNIQUE等

1）ALTER TABLE tb_acc_journal ADD CONSTRAINT PK_tb_user PRIMARY KEY (user_id) USING INDEX  TABLESPACE DEALIDX；
2）ALTER TABLE tb_acc_journal ADD CONSTRAINT FK_tb_acc_journal_account  FOREIGN KEY (account_id) REFERENCES tb_acc_account (acc_account_id)；
3）ALTER TABLE tb_item_category ADD CONSTRAINT UK_item_category_level_name UNIQUE(parent_cat_id,cat_name) USING INDEX TABLESPACE ADMIDX;

2.7 删除主键，外键，UNIQUE

1）ALTER TABLE tb_acc_journal DROP CONSTRAINT PK_tb_user；
2）ALTER TABLE tb_acc_journal DROP CONSTRAINT FK_tb_acc_journal_account；
3）ALTER TABLE tb_item_category DROP CONSTRAINT UK_item_category_level_name;

3、删除表

DROP TABLE tb_user CASCADE CONSTRAINTS;

4、Procedure和Job

1） 创建
CREATE OR REPLACE PROCEDURE bt_deal_promotion_visitor AS
BEGIN
update tb_visitor tv set tv.receive_mail_setting=bitor(tv.receive_mail_setting,1) where tv.mail in (select t.mail as email from tb_visitor t where t.status=1 and t.subscribe_sts!=9 and bitand(t.receive_mail_setting,1)=0
minus
select distinct substr(REGEXP_SUBSTR(t.referrer,'mail=[^&]+'),6) from tb_http_access_log t where t.url = '/unsubscribe/save' and t.referrer like '%/unsubscribe%' and (t.query_string is null or t.query_string like '%mailType=1%') and NOT REGEXP_LIKE(user_agent, '(bot|scan|slurp|spider|craw)', 'i') and (ip <> '211.144.118.170' and ip <> '202.74.178.140' and ip <> '202.74.178.141'));
COMMIT;
END bt_deal_promotion_visitor;
/

var job NUMBER
begin
  dbms_job.submit(:job,
                  'bt_deal_promotion_visitor;',
                  next_day(trunc(sysdate)+22/24,'SATURDAY'),
                  'trunc(sysdate+30)');
  commit;
end;
/

2）查看
SQL> column log_user format a20
SQL> column WHAT format a50
SQL> column INTERVAL format a40
SQL> select job,log_user,what,next_date,next_sec,interval from user_jobs;
       JOB             LOG_USER                       WHAT                           NEXT_DATE NEXT_SEC                         INTERVAL
---------- ------------------------------ ------------------------------ --------- -------------------------------- --------------------
103           OM_SUPERVISOR             bt_deal_promotion_visitor;     10/JUL/10 22:00:00                      trunc(sysdate+30)

3）删除
begin   
dbms_job.remove(103);
commit;
end;   
/   

drop procedure bt_deal_promotion_visitor;

5. Table Space

1） 改变表的表空间
alter table tablename move tablespace tabelspacename;

2）查看表空间的物理位置及具体参数
select * from dba_data_files where TABLESPACE_NAME='DEAL';

3）查看所有表空间的参数
select * from dba_tablespaces;

4) 查看表空间的空闲空间大小
select  a.tablespace_name,b.file_name,sum(a.bytes) freespace,count(*) extcnt,b.bytes,max(a.bytes) largest,min(a.bytes) smallest
from dba_free_space a,dba_data_files b where a.tablespace_name=b.tablespace_name and a.file_id=b.file_id group by a.tablespace_name,b.file_name,b.bytes;

5) ORACLE中表、索引的表空间的批量更改方法
a. 查询当前用户下的所有表
select 'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_all_tables;
b. 查询当前用户下的所有索引
select 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes;
c. 在当前用户下将查询结果批处理执行即可！

6. 通过v$sqlarea,v$sql查询最占用资源的查询

-----------------------
v$sqlarea,v$sql
-----------------------

从V$SQLAREA中查询最占用资源的查询

select b.username username,a.disk_reads reads,
    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
    a.sql_text Statement
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
 and a.disk_reads > 100000
order by a.disk_reads desc;

用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。

 

V$SQL是内存共享SQL区域中已经解析的SQL语句。

列出使用频率最高的5个查询：

select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=5;

消耗磁盘读取最多的sql top5：
select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
     (order by disk_reads desc) disk_reads_rank
   from v$sql)
where disk_reads_rank <=5;

找出需要大量缓冲读取（逻辑读）操作的查询：

select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;