Oracle常用命令2

----查看当前数据库
    select name from V$database;
----查看当前用户所有表
    select table_name from DICTIONARY;
    select table_name from user_tables;
----查看所有用户
    select username from dba_user;
---查看及解决死锁办法
    1.用超级用户（如：system）登录
    2. select c.object_name, b.username, b.sid as 主, b.serial# as 次, to_char(b.logon_time, 'YYYY-MM-DD HH24:MI:SS') from v$locked_object a, v$session b, all_objects c where a.session_id = b.sid and a.object_id = c.object_id order by b.logon_time;
    (sqlplus / as sysdba)
    3. alter system kill session '主,次'

-----删除表中重复数据
      delete from test_rowid where  【时间条件，按天操作】 and  rowid not in (select max(rowid) from test_rowid where 【时间条件，按天操作】 group by a,b,c);

-----查看、删除、添加分区表
    SELECT partition_name,num_rows FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='@table_name' order by partition_name;
    select * from @TABLE partition(@partion_name);

    alter table @table_name drop partition @partion_name;

    alter table @table_name SPLIT PARTITION P_MAX AT ('20101027') INTO (PARTITION P_20101026, PARTITION P_MAX)';
----导入文本数据到数据库
     sqlldr ${V_ORA_USER}/${V_ORA_PASSWD}@${V_ORA_SID} control=xxx.ctl data=xxx.txt bad=xxx.bad log=xxx.log;
    xxx.ctl:    load data
            insert/replace into table @tab_name
            fields terminated by '|'
            trailing nullcols(id)

-----创建表、主键、索引及注释
    -- Create table
      CREATE TABLE META_SCP
    (
        ID INTEGER NOT NULL,
        NAME VARCHAR(20) NOT NULL,
    )
    ;
    -- Add comments to the table
        comment ON TABLE META_SCP IS 'SCP信息数据表。该表中同时存储SDP,SMP信息。'
    -- Add comments to the columns
        comment ON COLUMN META_SCP.ID IS '唯一标识。'
            comment ON COLUMN META_SCP.NAME IS '名称'

    -- Create/Recreate primary, unique and foreign key constraints
           ALTER TABLE META_SCP ADD PRIMARY KEY(ID)
    -- Create/Recreate indexes
          CREATE UNIQUE index XAK1META_SCP ON META_SCP
         (
        SCPID
         )
-----添加、修改及删除表字段
    alter table @table_name add @ID number(8)/varchar(20);

    alter table @table_name modify @name varchar(100);
    alter table @table_name drop column @tmp ;

-----创建、修改、删除序列
        create sequence @seq_
        increment by 1     --增长度
        start with 1       --从哪里增加，就是说下一个获取的值从这个值开始
        nomaxvalue         --不设置最大值   对应的：maxvalue  30、
        order              --指定一定往下增加
        nocycle            --不循环，CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环     

        cache  10          --CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能

    修改：序列的某些部分也可以在使用中进行修改，但不能修改SATRT WITH选项。对序列的修改只影响随后产生的序号，已经产生的序号不变。修改序列的语法如下：
    ALTER SEQUENCE @seq_
    INCREMENT BY 10
    MAXVALUE 10000
    CYCLE -- 到10000后从头开始
    NOCACHE ;
    删除：DROP SEQUENCE @seq_;

------日期转毫秒、毫秒转日期
    1.SELECT TO_NUMBER(TO_DATE('2005-03-29 12:30:45', 'YYYY-MM-DD HH24:MI:SS') -  
                 TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000 FROM DUAL;
    2.SELECT TO_CHAR(1112070645000 / (1000 * 60 * 60 * 24) +  
        TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS') AS CDATE FROM DUAL;

------sql分页语句
      select * from ( select t.*,rownum as rowcount from table t) where rowcount between 1 and 20

-------插入及修改操作，一个语句搞定
    merge into TABLE t using dual on (t.id = 1)
        when not matched then insert(ID) values(1)
        when  matched then update set id=2;