Oracle 学习笔记

语句

创建表

CREATE TABLE schema_name.table_name
(
  column_1 data_type column_constraint,
  column_2 data_type column_constraint, ..
  .
  table_constraint
);

给表添加列

ALTER TABLE table_name
  ADD (
    column_name_1 data_type constraint,
    column_name_2 data_type constraint, ...
);

增

INSERT INTO table_name (column_1, column_2, column_3, ...column_n)
VALUES (value_1, value_2, value_3, ..., value_n);

删

DELETE
FROM table_name
WHERE
  condition;

改

UPDATE
  table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3, ...
  WHERE
  condition;

查

SELECT column_1,
       column_2, ...
  FROM
  table_name;

工作中实用脚本

更新test库中所有表的abc字段，把所有名称包含abc的字段为xxx的改成yyy

SELECT 'UPDATE ' || TABLE_NAME || ' SET ' || COLUMN_NAME || ' = ''xxx'' WHERE ' || COLUMN_NAME || ' = ''yyy'';'
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%abc%'
  AND OWNER = 'test'

数据表被死锁，输入以下sql即可查找（以test数据库为例）

SELECT /*+ rule */ s.username,
                   s.SID,
                   s.SERIAL#,
                     decode(l.type, 'TM', 'TABLE LOCK',
                          'TX', 'ROW LOCK',
                          NULL) LOCK_LEVEL, o.owner,
                   o.object_name,
                   o.object_type,
                   s.sid,
                   s.serial#,
  s.terminal, s.machine,
                   s.program,
                   s.osuser
FROM v$session s,
     v$lock l,
     dba_objects o
WHERE l.sid = s.sid
  and username = 'test'
  AND l.id1 = o.object_id(+)
  AND s.username is NOT NULL;