oracle 常用sql
--1,随机抽取一条数据
select columnName from (select * from tableName order by dbms_random.value ) where rownum = 1;
--2,选取一个表中的重复数据
select columnName from tableName group by columnName having count(*) > 1;
--3,添加一列
alter table tableName add columnName varchar(200);
--4,删除一列
alter table tablename drop (columnName );
--5,修改一列的字段长度
ALTER TABLE tablename modify columnName varchar2 (30);
--6,删除表的主键
alter table tablename drop CONSTRAINT pk_name;
--7,给表增加主键
alter table tablename add constraint pk_name primary key(columnName1, columnName2,……);
--8,如2012-4-27 9:20:22只截取后面的时分秒
select to_char(sysdate,'hh24:mi:ss') from dual;
--9,查看当前表被谁占用
SELECT /*+ rule */ S.USERNAME, 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 L.ID1 = O.OBJECT_ID(+) AND S.USERNAME IS NOT NULL
--10,修改注释
comment on column tableName.columnName is '这是修改的注释';
--11,绝对值小于1的小数显示小数点前面的0
select DECODE(TRUNC(:decimal_number), 0, REPLACE(TO_CHAR(:decimal_number),'.','0.', ) TO_CHAR(:decimal_number) ) from dual;