oracle学习点滴

1、删除oracle用户（报cannot drop a user that is currently connectted）.

      select username, sid, serial# from V$session.

       alter system kill session 'sid,serial';

       drop user myuser cascasde;

 

2、查看Long型数据

       创建表

      create table t_long( col1 long);

      插入数据

      delete from t_long

      insert into t_long values(str_l_sql);

      查看数据

      select * from t_long

      删除表

      drop  table t_long 

 

 3、(重启)关闭/启动oracle的监听进程

     su - oracle

     lsnrctl stop

     lsnrctl start

 

 4、(重启)关闭/启动oracle的用户实例

     su - oracle

     sqlplus /nolog

     connect /as sysdba

     shutdown immediate/abort

     startup open(默认)/mount(DBA)

 

 5、配置数据库sid地址

    开始->启动->oraHome92->Enterprise Manager console

    右键点击数据库->将数据库添加到树... ...

 

 6、查询oracle SID

     echo $ORACLE_SID

 

 7、查询所有用户

     select * from all_users

 

 8、database link

   (1)创建/删除全局database link

    create public dababase link public_link

    cononect to myuser identified by myuserpasswd

    using 'DB_SID'

    drop public database link public_link.

   (2)创建/删除database link

   create database link myuser_link

   connect to myuser identified by myuserpasswd

   using 'DB_SID'

   drop database link myuser_link

  (3)方式二：创建/删除database link

   create dababase link myuser_link

   connect to myuser identified by myuserpasswd

   using '(DESCRIPTION=

                  (ADDRESS_LIST=

                      (ADDRESS=(PROTOCOL=TCP)

                                           (HOST=10.137.23.5)

                                           (PORT=1521)

                       )

                   )   

                   (CONNECT_DATA =

                                        (SERVICE_NAME=DB_UID)

                   )

               )';

 

 9、批量插入数据

 insert into A(a,b,c) (select a,b,c from B);

 insert into A(select * from A);

 

 10、插入date值

 update t_date d set d.endtime=to_date('2010-08-08 18:31:34','YYYY-MM-DD HH24:MI:SS');