1、创建新表流程
1.1 建表语句
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的话，则跳过
CREATE SEQUENCE seq_user MINVALUE 1000000000100001 MAXVALUE 9999999999999999 START WITH 1000000000100001 INCREMENT BY 1 NOCACHE;
1.3 授权
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、修改表结构
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;
4）暂停Job
begin
sys.dbms_job.broken(job => 103,
broken => true);
commit;
end;
/
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
-----------------------
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;
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from v$sql)
where exec_rank <=5;
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;
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;
7. Oracle锁表查询
最近遇到这样一个问题，操作一个表，发现被人锁定了，于是查询谁锁定的表，发现多人锁定，安全起见，不能全部kill，于是用一下语句判定，谁锁定，谁等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name，
s.LOCKWAIT,s.status,s.machine,s.terminal,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id AND lower(object_name)='tb_offer'
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC；
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以上的语句可以查询到谁锁了表，而谁在等待。
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
找出谁锁定的记录，kill掉就行了。
alter system kill session '133,3506'; (其中133是SID，3506是SERIAL#)
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;