Oracle常用代码

/*查看用户*/
select * from user_users
/*查看用户表*/
select * from user_tables

/*查看表对象的信息*/
select * from user_objects where object_name = upper('news');

/*权限*/
select * from user_tab_privs where table_name = upper('NEWS')

/*查看表列信息*/
select * from all_tab_cols where table_name = upper('news');

 
/*分页设置
显示大于2小于等于8的记录*/

 select *
   from (select rownum rn, id, title, context, adddate
           from (select * from news order by adddate desc))
  where rn >= 1
    and rn <= 10
    


SELECT LimitResult.*
  FROM (SELECT OrderResult.*, rownum AS r
          FROM (SELECT * FROM News ORDER BY adddate DESC) OrderResult
         WHERE rownum <= 10) LimitResult
 WHERE LimitResult.r >=1
 
/*分页设置*/

当前日期 current_date sysdate

select   to_char(current_date,'YYYYMMDD   hh24:mi:ss')   from   dual 
select   to_char(sysdate,'YYYYMMDD   hh24:mi:ss')   from   dual  

显示一个字符用 select 'dd' from dual 


插入日期型数据 to_date('2008-3-2','YYYY-MM-DD')
insert into news values('4','lvyanyang','吕艳阳',to_date('2008-3-2','YYYY-MM-DD'))


/*查看系统拥有哪些用户*/
select * from all_users;

/*查询当前用户下所有对象*/
select * from tab;

/*回滚*/
roll;
rollback;
/*提交*/
commit;


用户授权:

GRANT ALTER ANY INDEX TO "user_id "

GRANT "dba " TO "user_id ";

GRANT "CONNECT " TO "user_id ";

ALTER USER "user_id " DEFAULT ROLE ALL

创建用户:

CREATE USER "user_id " PROFILE "DEFAULT " IDENTIFIED BY " DEFAULT TABLESPACE "USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;

-- Create the user 
create user app
  identified by app
  default tablespace AUTOSPACE
  temporary tablespace temp
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant dba to app with admin option;



CREATE USER "TEST"  PROFILE "DEFAULT" 
    IDENTIFIED BY "111111" DEFAULT TABLESPACE "TOOLS" 
    ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "TEST"
GRANT "CONNECT" TO "TEST";
GRANT "RESOURCE" TO "TEST";




GRANT "CONNECT" TO "TEST";
GRANT "UNLIMITED TABLESPACE" TO "TEST";
GRANT "RESOURCE" TO "TEST";


ALTER USER "TEST" DEFAULT ROLE  ALL


GRANT INSERT 
    ON "APP"."CHELIANGSYXX" TO "TEST"
GRANT SELECT 
    ON "APP"."CHELIANGSYXX" TO "TEST"
    
    
GRANT EXECUTE 
    ON "APP"."PR_QUERY" TO "TEST"
    
REVOKE EXECUTE 
    ON "APP"."PR_QUERY" 
 FROM "TEST"
    
REVOKE SELECT 
    ON "APP"."JXXUEYUANXX" 
    FROM "TEST"
    
    REVOKE CREATE ANY TABLE 
    FROM "TEST"
    REVOKE UNLIMITED TABLESPACE 
    FROM "TEST"


解锁
1、当前session中commit、rollback都可以解锁   
  2、在别的session中(DBA)通过   
  查找用户锁   
  select   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   s.sid=l.sid   
  and   o.object_id=l.id1   
  and   s.username   is   not   null   
  检查出来谁锁了该表   
  查看结果中object_name='你的表名'   
  然后得到sid,serial#   
  alter   system   kill   session   'sid,   serial#'     
  or   
  alter   system   disconnect   session   'sid,   serial#'   immediate;   
 

用户密码设定:

ALTER USER "CMSDB " IDENTIFIED BY "pass_word "

表空间创建:

CREATE TABLESPACE "AUTOSPACE" 
    LOGGING 
    DATAFILE 'D:\ORACLE\ORADATA\XAAI\AUTOSPACE.ORA' SIZE 5M REUSE
    AUTOEXTEND 
    ON NEXT  20480K MAXSIZE  16383M EXTENT MANAGEMENT LOCAL
    以20M的增长量,自动增长
    
删除表空间
drop tablespace AUTOSPACE including contents; 
drop tablespace AUTOSPACE including datafile

/*建一个和a表结构一样的空表*/
create table b as select * from a where 1=2;
create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;



安装之后的初始密码:
sys/change_on_install
system/manager

internal/oracle   
  　　sys/change_on_install   
  　　system/manager   
  　　scott/tiger   
          sysman/oem_temp  

/*察看数据库的大小，和空间使用情况*/

col tablespace format a20

select b.file_id　　文件ID,
       
       　　b.tablespace_name　　表空间,
       
       　　b.file_name　　　　　物理文件名,
       
       　　b.bytes　　　　　　　总字节数,
       
       　　(b.bytes - sum(nvl(a.bytes, 0))) 　　　已使用,
       
       　　sum(nvl(a.bytes, 0)) 　　　　　　　　剩余,
       
       　　sum(nvl(a.bytes, 0)) / (b.bytes) * 100　剩余百分比
       
       　　from dba_free_space a,
       dba_data_files b
       
       　　where a.file_id = b.file_id
       
       　　group by b.tablespace_name,
       b.file_name,
       b.file_id,
       b.bytes
       
       　　order by b.tablespace_name

dba_free_space --表空间剩余空间状况
dba_data_files --数据文件空间占用情况

/*把SQL*Plus当计算器*/
select 100*20 from dual;

/*连接字符串*/
select id | |name from news
select concat(id,name) from news

/*查询当前日期*/
select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual

/*用户间复制数据*/
copy from user1 to user2 create table2 using select * from table1;

/*视图中不能使用order by，但可用group by代替来达到排序目的*/
create view a as select b1,b2 from b group by b1,b2;


/*查出当前用户所有表名。*/
select unique tname from col;







/*修改表属性*/
/* 向一个表格添加字段 */

alter table alist_table add address varchar2(100);

/* 修改字段 属性 字段为空 */

alter table alist_table modify address varchar2(80);

/* 修改字段名字 */

create table alist_table_copy as select ID,NAME,PHONE,EMAIL,

QQ as QQ2, /*qq 改为qq2*/

ADDRESS from alist_table;

drop table alist_table;

rename alist_table_copy to alist_table

/* 修改表名 */

空值处理

有时要求列值不能为空

create table dept (deptno number(2) not null, dname char(14), loc char(13));

在基表中增加一列

alter table dept

add (headcnt number(3));

修改已有列属性

alter table dept

modify dname char(20);
/*
注：只有当某列所有值都为空时，才能减小其列值宽度。

只有当某列所有值都为空时，才能改变其列值类型。

只有当某列所有值都为不空时，才能定义该列为not null。*/

/*修改表属性*/













/*查找未断连接*/

select process,osuser,username,machine,logon_time ,sql_text

from v$session a,v$sqltext b where a.sql_address=b.address;










/*1.以USER_开始的数据字典视图包含当前用户所拥有的信息, 查询当前用户所拥有的表信息:*/

select * from user_tables;

/*2.以ALL_开始的数据字典视图包含ORACLE用户所拥有的信息,*/

查询用户拥有或有权访问的所有表信息:

select * from all_tables;

/*3.以DBA_开始的视图一般只有ORACLE数据库管理员可以访问:*/

select * from dba_tables;

/*4.查询ORACLE用户：*/

conn sys/change_on_install

select * from dba_users;

conn system/manager;

select * from all_users;

/*5.创建数据库用户：*/

CREATE USER user_name IDENTIFIED BY password;

GRANT CONNECT TO user_name;

GRANT RESOURCE TO user_name;

授权的格式: grant (权限) on tablename to username;

/*删除用户(或表):*/

drop user(table) username(tablename) (cascade);

/*6.向建好的用户导入数据表*/

IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:\EXPDAT.DMP COMMIT = Y

/*7.索引*/

create index [index_name] on [table_name]( "column_name ") 



1、 求当前会话的SID，SERIAL# 


SELECT Sid, Serial# 

FROM V$session 

WHERE Audsid = Sys_Context('USERENV', 'SESSIONID'); 

2、 查询session的OS进程ID 


SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#, 

s.Osuser, s.Machine 

FROM V$process p, V$session s, V$bgprocess b 

WHERE p.Addr = s.Paddr 

AND p.Addr = b.Paddr 

And (s.sid=&1 or p.spid=&1) 

UNION ALL 

SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid, 

s.Serial#, s.Osuser, s.Machine 

FROM V$process p, V$session s 

WHERE p.Addr = s.Paddr 

And (s.sid=&1 or p.spid=&1) 

AND s.Username IS NOT NULL; 






在别的session中(DBA)通过   
  查找用户锁   
  select   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   s.sid=l.sid   
  and   o.object_id=l.id1   
  and   s.username   is   not   null   




3、根据sid查看对应连接正在运行的sql 


SELECT /*+ PUSH_SUBQ */ 

Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts, 

Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions, 

Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls, 

Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time, 

SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status 

FROM V$sqlarea 

WHERE Address = (SELECT Sql_Address 

FROM V$session 

WHERE Sid = &sid ); 



/*查看控制文件 */
　　select name from v$controlfile; 
　　
/*查看日志文件 */
　　select member from v$logfile; 

/*导出数据脚本*/
select 'INSERT INTO B_STATTEMPLATE ( N_ID,C_NAME, C_KBH, N_PRINT, N_TYPE, N_APP, N_VALID ) ' ||
       'Values (' || To_Char(N_ID) || ',''' || C_NAME || ''', ''' || C_KBH ||
       ''', ' || To_Char(N_PRINT) || ', ' || To_Char(N_TYPE) || ', ' ||
       '0, 0 );'
  from news





/*查看数据库的版本*/　
Select version FROM Product_component_version
   Where SUBSTR(PRODUCT,1,6)='Oracle';


/*查看数据库的创建日期和归档方式*/

Select Created, Log_Mode, Log_Mode From V$Database;





/*查看数据文件放置的路径 */

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

/*正常方式关闭数据库*/
shutdown normal    

/* 
立即方式关闭数据库。  
在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，  
而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源），  
当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。  
*/
shutdown immediate  
/*直接关闭数据库，正在访问数据库的会话会被突然终止，  
如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。
*/
shutdown abort  


/*启动数据库*/
startup

导出/导入（EXPORT/IMPORT）          逻辑备份

数据导出：
 1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
   $exp auto/369@auto369 file=c:\daochu.dmp full=y feedback=1000
 2 将数据库中system用户与sys用户的表导出
 $exp auto/369@auto369 file=c:\daochu.dmp owner=(auto) feedback=1000
   $exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)
 3 将数据库中的表inner_notify、notify_staff_relat导出
    $exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat) 

 4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
   $exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"
 $exp auto/369@auto369 file=c:\daochu.dmp tables=(news) feedback=1000
  上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
  也可以在上面命令后面 加上 compress=y 来实现。
    

数据的导入
 1 将D:daochu.dmp 中的数据导入 TEST数据库中。
   $imp system/manager@TEST  file=d:daochu.dmp
   $imp aichannel/aichannel@HUST full=y  file=d:datanewsmgnt.dmp ignore=y
   上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
   在后面加上 ignore=y 就可以了。
 2 将d:daochu.dmp中的表table1 导入
 $imp system/manager@TEST  file=d:daochu.dmp  tables=(table1)
 
 
 $imp auto/369@auto369 file=c:\daochu.dmp tables=(news) ignore=y
 
 基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。
在导入导出命令中加上feedback=1000可以让过程显示一个不断增多的“”，以改变以往的闪烁的光标


冷备份、热备份                      物理备份


冷备份
---- 1．所有数据文件 

---- 2．所有控制文件 

---- 3．所有联机REDO LOG文件 

---- 4．Init.ora文件（可选）。 



创建存储过程
create or replace procedure Pr_AddNews(ids in varchar2,titles in varchar2,contexts in varchar2) is
begin
insert into news (id,title,context) values(ids,titles,contexts);
end Pr_AddNews;

执行存储过程
exec pr_addnews('23','23','txt23');
查看存储过程源代码
select   *   from   user_source    order   by   line;








方法1:

1、当前session中commit、rollback都可以解锁   
  2、在别的session中(DBA)通过   
  查找用户锁   
  select   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   s.sid=l.sid   
  and   o.object_id=l.id1   
  and   s.username   is   not   null   
  检查出来谁锁了该表   
  查看结果中object_name='你的表名'   
  然后得到sid,serial#   
  alter   system   kill   session   'sid,   serial#'     
  or   
  alter   system   disconnect   session   'sid,   serial#'   immediate;   
  3、select   *   from   table   for   update   nowait可以指定nowait 

执行后结果： 提示标记要删去的会话 无法释放会话,未成功解锁

方法2:

　a、 找到你要杀掉的那个SESSION, 并记下paddr

　　SELECT sid, username, paddr, status FROM v$session WHERE username = '用户名' ; example:SELECT sid, username, paddr, status FROM v$session WHERE username = 'XNJW0419' ; SID USERNAME PADDR STATUS---------- ------------------------------ -------- -------- 10 XNJW0419 2B5E2E2C KILLED

　　b、 找到这个SESSION所对应的spid

　　SELECT * FROM v$process WHERE addr = '上面查寻的paddr'; example:SELECT ADDR,PID,SPID,USERNAME,SERIAL#,TERMINAL FROM v$process WHERE addr = '2B5E2E2C';ADDR PID SPID USERNAME SERIAL# TERMINAL-------- ---------- ------------ --------------- ---------- ----------------2B5E2E2C 44 1204 SYSTEM -99 JWC

　　c、 杀掉spid所标识的那个进程

　　D:\>orakill sid thread

       sid = 数据库名

      thread=SESSION所对应的spid

　　spid （注：cmd命令窗口中执行 只有oracle服务器端才有orakill命令）example: D:\>orakill oemrep 1204

　　提示：KILL OF THREAD id 1204 IN INSTANCE oemrep successfully signalled.

　　结果：成功删除该SESSION,表也解锁