oracle常用命令

/*创建表空间时指定的文件夹必须存在*/
drop user snym cascade
/*第1步：创建临时表空间  */
create temporary tablespace ices_temp
tempfile 'D:\app\wenfanchao\oradata\oracle11g\ices_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  
 
/*第2步：创建数据表空间  */
create tablespace ices_data  
logging  
datafile 'D:\app\wenfanchao\oradata\oracle11g\ices_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  

/*第3步：创建用户并指定表空间  */
create user ices identified by 111
default tablespace ices_data
temporary tablespace ices_temp
/*或使用默认表空间*/
/*default tablespace users
temporary tablespace temp*/
/*使用表空间配额无限制*/
quota unlimited on ices_data;

/*第4步：给用户授予权限*/
grant connect,resource,dba to ices;
grant execute on dbms_pipe to ices;

导出
exp ices/111@ICES file=d:\2012-7-15b.dmp full=y
exp icesdb/icesdb@ICES2 file=d:\2012-7-6.dmp full=y

查询锁表
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
decode(l.LMODE,1,'空',2,'行级共享锁，其他对象只能查询这些数据行',3,'行级排它锁，在提交前不允许做DML操作',4,'共享锁',5,'共享行级排它锁',6,'排它锁'),
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser,s.SQL_ADDRESS,s.SQL_HASH_VALUE
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    ORDER BY LOCK_LEVEL      



--根据address 和 hash_value 查询对应语句
select * from v$sql where address = '000000055B9A2728' and hash_value = 2183148849;
--全语句
select * from v$sqltext where address = '000000055B9A2728' and hash_value = 2183148849;
--快速
select s.sid,sql_text from v$session s,v$sqlarea a,v$locked_object b  where s.sid=b.session_id and s.PREV_SQL_ADDR=a.address



临时表被锁
select sid,serial# 
from v$session 
where sid=(select sid 
              from v$lock   
             where id1 in (select object_id 
                           from user_objects   
                          where object_name='TMP_REP_5'));


alter system kill session'72,146';
杀锁命令
alter system kill session 'sid,serial#'

查看所有表空间位置
select name from v$datafile;

查看SQL性能
SELECT BUFFER_GETS /*所有子游标运行这条语句导致的读内存次数*/ ,EXECUTIONS /*所有子游标的执行这条语句次数*/ ,SQL_TEXT FROM V$SQLAREA;

查看消耗资源最多的SQL：
Sql代码 SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC;

选出最占用资源的查询

Sql代码 select b.username username,a.disk_reads reads,a.executions exec, a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio, a.sql_text statement from v$sqlarea a,dba_users b where a.parsing_user_id=b.user_id and username = 'SNYM'

--sqlplus
启动监听：lsnrctl start 
查看监听：lsnrctl status 
停止监听：lsnrctl stop 
--登录
sqlplus 用户名/密码@监听名称 as 登录模式(normal/sysdba/sysoper)
--
1启动SQL*PLUS不与数据库连接

   SQLPLUS /NOLOG

2、以SYSDBA角色与Oracle连接

   CONNECT username/password AS SYSDBA

3、启动实例

   1>、启动一个实例，装配和打开一个数据库

       STARTUP;

或

       STARTUP PFILE='d:/oracle/admin/mydb/scripts/initMYDB.ora';

   2>、启动一个实例但不装配数据库

       --典型应用为数据库创建

       STARTUP NOMOUNT;

   3>、启动一个实例，装配数据库但不打开

       --该模式下允许你执行特定的维护操作，包括

       --重命名数据库、增删或重命名日志文件、启用和停用重做归档日志文件选项;

       --执行完整的数据库恢复

       STARTUP MOUNT;

   4>、在启动时限制一个数据库的存取

       --该模式使管理员可用数据库，但一般操作员不可用，应用于

       --执行导入导出、执行SQL*loader的数据装载、临时阻止典型用户使用数据

       --升迁或升级

       --在正常模式下，有CREATE SESSION系统特权的用户可以连接到数据库

       --限制模式下，同时具有CREATE SESSION和RESTRICTED SESSION两个系统特权的用户

       --才可以存取数据 

       STARTUP RESTRICT;

       --关闭限制模式

       ALTER SYSTEM DISABLE RESTRICTED SESSION;

       --将正在运行的正常模式改变为限制模式

       ALTER SYSTEM ENABLE RESTRICTED SESSION;

   5>、强制一个实例启动（用于启动时出现问题情况，少用，慎用！）

       STARTUP FORCE;

   6>、启动实例，装配数据库和启动完全的介质恢复

       STARTUP OPEN RECOVER;

4、改变数据库可用性

   1>、装配数据库

       ALTER DATABASE MOUNT;

   2>、打开数据库

       ALTER DATABASE OPEN;

   3>、以只读或只写或读写方式打开数据库

       ALTER DATABASE OPEN READ ONLY;

       ALTER DATABASE WRITE READ ONLY;

       ALTER DATABASE OPEN READ WRITE; 

<二>、ORACLE数据库关闭

1、正常关闭

   SHUTDOWN NORMAL； 

2、事务性关闭

   --不许新连接，但等待现有的事务执行结束

   SHUTDOWN TRANSACTIONAL；

3、立即关闭

   SHUTDOWN IMMEDIATE；

4、强制关闭    

   --该模式关闭下次启动时要进行实例恢复过程

   SHUTDOWN ABORT；

5、停顿数据库

   --该状态下只允许DBA会话，不允许新的非DBA连接建立

   ALTER SYSTEM QUIESCE RESTRICTED;

   --取消停顿

   ALTER SYSTEM UNQUIESCE;

   --查看实例的停顿状态

   --列ACTIVE_STATE说明：NORMAL未停顿QUIESCING正在停顿，但仍有非DBA会话

   --QUIESCED已停顿

   select ACTIVE_STATE from V$INSTANCE;

6、挂起数据库

   --暂停对数据文件和控制文件的所有IO，可以在无IO干扰情况先进行备份

   --挂起命令可以挂起数据库而并不指定一个实例

   ALTER SYSTEM SUSPEND;

   --恢复到非挂起状态

   ALTER SYSTEM RESUME;   

   --查看挂起状态

   SELECT DATABASE_STATUS FROM V$INSTANCE;


--


11G的DMP 导入 10G
一、在11g服务器上，使用expdp命令备份数据 

11g 导出语句：EXPDP USERID='facial/facial@orcl as sysdba' schemas=facial directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0 

其中，我的用户名是facial，密码是facial，数据库sid是orcl，schemas要导出的用户名是facial，要导入到 10.2.0.1.0版本的Oracle数据库中去。aa.dmp和aa.log将会在11g的dpdump目录中生成，例如我的11g装在了E盘下面，于是aa.dmp将会在E:\app\Administrator\admin\orcl\dpdump目录下被生成。 


二、在10g服务器上，使用impdp命令恢复数据 

准备工作：1.建库2.建表空间3.建用户并授权4.将aa.dmp拷贝到10g的dpdump目录下 

10g 导入语句：IMPDP USERID='facial/facial@orcl as sysdba' schemas=facial directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0 
--给用户赋予创建临时表权限
grant create any table to <user>


-- 查询某个时间段的 SCN 系统快照号
select timestamp_to_scn(to_timestamp('2012-07-19 15:00:00','YYYY-MM-DD HH24:MI:SS')) from dual;
--根据系统快照号查询在该 SCN 号下 查询表对应的 数据集如
select * from text1(表名) as of scn 25107913(SCN号);
--通过 Insert into 语句还原表
insert into text1(表名) select * from select * from text1(表名) as of scn 25107913(SCN号);

select dbms_flashback.get_system_change_number from dual;

 
就是oracle的会话数超出了限制，一般都是由于多次connect建立多个连接会话引起的，最后导致oracle无法响应新的请求，从而出现ora-12516错误。

相关解决办法：




1.查看当前连接进程数

SQL>select count(*) from v$process;


2.查看连接数上限

SQL>select value from v$parameter where name = 'processes';

 


3.查看当前数据库的processes设置

SQL> show parameter processes



NAME                                 TYPE        VALUE 
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150 




SQL> show parameter sessions
NAME                                 TYPE        VALUE
java_soft_sessionspace_limit         integer     0l
icense_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
sessions                             integer     170
shared_server_sessions               integer




4.只要会话连接数超过上面的process数150或者sessions数170，再来一个的会话进程，就会产生12516错误。因此可以修改一下该值：

sessions=1.1*processes+5;//这个是sessions值和processes值的关系，最好按照这样做，


因此如果要将processes数设置为500，则sessions数必须为1.1*500+5=555




SQL> alter system set processes=500 scope=spfile;
系统已更改。
SQL> alter system set sessions=555 scope=spfile;
系统已更改。


1。会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT PRESERVE ROWS；
2。事务特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable

select * from DBA_USERS;
SELECT * FROM ALL_USERS; 


---

create temporary tablespace impr_temp tempfile  '+ASM_DATA/snym/tempfile/impr_temp.dbf' size 10m autoextend on next 10m maxsize  unlimited;


create tablespace impr_idata logging datafile  '+ASM_DATA/snym/datafile/impr_idata.dbf' size 20m autoextend on next 20m maxsize  unlimited;


create user impr identified by impr123456 default tablespace impr_idata  temporary tablespace impr_temp;
grant connect,resource,dba to impr;


create temporary tablespace snym_temp tempfile  '+ASM_DATA/snym/tempfile/snym_temp.dbf' size 10m autoextend on next 10m maxsize  unlimited;


create tablespace snym_idata logging datafile  '+ASM_DATA/snym/datafile/snym_idata.dbf' size 20m autoextend on next 20m maxsize  unlimited;

--创建数据链
create public database link DB63
  connect to 用户名 identified by "密码"
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.201.8.63)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';
--创建同义词
create public synonym test_syn for scott.test2@DB104

---解决提示动态执行表不可访问
用SYS登录，授权给相应的用户，用下面的语句，
grant select on v_$statname to user;
自动统计功能可以关掉。
最后的这个，超时断开问题，可以设置USER_PROFILES里面看到的IDLE_TIME值，
首先查看你的当前用户的PROFILE是哪个，
select profile from dba_users where username='SCOTT' ;
假如是DEFAULT这个PROFILE，那么修改IDLE_TIME这个值
alter profile default limit idle_time unlimited ;
必要时创建新的PROFILE。
--实际应用中是不能对远程的表 使用递归语句的 但是可以通过使远程表与本地表产生笛卡尔积的方式变相解决使用递归语句


-------------------------------------------------------oracle赋权------------------------------------------
我们将通过介绍命令的方式，谈谈Oracle用户权限表的管理方法，希望对大家有所帮助。

　　我们将从创建Oracle用户权限表开始谈起，然后讲解登陆等一般性动作，使大家对Oracle用户权限表有个深入的了解。

　　一、创建

　　sys;//系统管理员，拥有最高权限

　　system;//本地管理员，次高权限

　　scott;//普通用户，密码默认为tiger,默认未解锁

　　sys;//系统管理员，拥有最高权限

　　system;//本地管理员，次高权限

　　scott;//普通用户，密码默认为tiger,默认未解锁

　　二、登陆

　　sqlplus / as sysdba;//登陆sys帐户

　　sqlplus sys as sysdba;//同上

　　sqlplus scott/tiger;//登陆普通用户scott

　　sqlplus / as sysdba;//登陆sys帐户

　　sqlplus sys as sysdba;//同上

　　sqlplus scott/tiger;//登陆普通用户scott

　　三、管理用户

　　create user zhangsan;//在管理员帐户下，创建用户zhangsan

　　alert user scott identified by tiger;//修改密码

　　create user zhangsan;//在管理员帐户下，创建用户zhangsan

　　alert user scott identified by tiger;//修改密码

　　四，授予权限

　　1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限

　　/*管理员授权*/

　　grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限

　　grant unlimited session to zhangsan;//授予zhangsan用户使用表空间的权限

　　grant create table to zhangsan;//授予创建表的权限

　　grante drop table to zhangsan;//授予删除表的权限

　　grant insert table to zhangsan;//插入表的权限

　　grant update table to zhangsan;//修改表的权限

　　grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

　　/*管理员授权*/

　　grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限

　　grant unlimited session to zhangsan;//授予zhangsan用户使用表空间的权限

　　grant create table to zhangsan;//授予创建表的权限

　　grante drop table to zhangsan;//授予删除表的权限

　　grant insert table to zhangsan;//插入表的权限

　　grant update table to zhangsan;//修改表的权限

　　grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

　　2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权

　　/*oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的*/

　　grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限

　　grant drop on tablename to zhangsan;//授予删除表的权限

　　grant insert on tablename to zhangsan;//授予插入的权限

　　grant update on tablename to zhangsan;//授予修改表的权限

　　grant insert(id) on tablename to zhangsan;

　　grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update

　　grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

　　/*oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的*/

　　grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限

　　grant drop on tablename to zhangsan;//授予删除表的权限

　　grant insert on tablename to zhangsan;//授予插入的权限

　　grant update on tablename to zhangsan;//授予修改表的权限

　　grant insert(id) on tablename to zhangsan;

　　grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update

　　grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

　　五、撤销权限

　　基本语法同grant,关键字为revoke

　　基本语法同grant,关键字为revoke

　　六、查看权限

　　select * from user_sys_privs;//查看当前用户所有权限

　　select * from user_tab_privs;//查看所用用户对表的权限

　　select * from user_sys_privs;//查看当前用户所有权限

　　select * from user_tab_privs;//查看所用用户对表的权限

　　七、操作表的用户的表

　　/*需要在表名前加上用户名，如下*/

　　select * from zhangsan.tablename

　　/*需要在表名前加上用户名，如下*/

　　select * from zhangsan.tablename

　　八、权限传递

　　即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：

　　grant alert table on tablename to zhangsan with admin option;//关键字 with admin option

　　grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似

　　grant alert table on tablename to zhangsan with admin option;//关键字 with admin option

　　grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似

　　九、角色

　　角色即权限的集合，可以把一个角色授予给用户

　　create role myrole;//创建角色

　　grant create session to myrole;//将创建session的权限授予myrole

　　grant myrole to zhangsan;//授予zhangsan用户myrole的角色

　　drop role myrole;删除角色

　　/*但是有些权限是不能授予给角色的，比如unlimited tablespace和any关键字*/
--建立JOB
declare 
 v_jobno number;
 begin
   dbms_job.submit(v_jobno,'prc_fistone(1);',sysdate,'sysdate+1');
   dbms_output.put_line('号：'|| v_jobno);
   commit;
 end;
 --修改
 begin
  sys.dbms_job.change(job => 23,
                      what => 'prc_nextyear(1);',
                      next_date => to_date('06-03-2013', 'dd-mm-yyyy'),
                      interval => 'sysdate+1');
  commit;
end;
-------------------------------------------------------------------------------------------

我们在确定应用程序性能的时候，更多地会关注其中SQL语句的执行情况。通常应用的性能瓶颈会在数据库这边，因此数据库的sql语句是我们优化的重点。 

要对sql语句进行优化，首先要知道应用程序执行了哪些SQL语句。下面我介绍三种方法来获得应用程序所执行的SQL语句。 

方法一： 

如果你采用的数据库是oracle的话，那么可以利用oracle本身的10046事件跟踪SQL语句。步骤如下： 

1、首先获得spid、sid、serial#，machine为你的机器名 


　　
　　SQL> select b.spid,a.sid,a.serial#,
    a.machine from v$session a,v$process b where 
    a.paddr = b.addr and a.machine='yz';
　　
　　SPID　　　　　　　　SID　　SERIAL#　MACHINE
　　
　　9497　　　　　　　　 49　　　 3406　　 yz 

　　 
2、利用10046事件开始跟踪 


　　
　　SQL>execute sys.dbms_system.set_ev(49, 3406,10046,1,'');
　　PL/SQL procedure successfully completed. 

　　 
3、这时候你可以运行应用程序，对于web 应用你就可以打开你认为性能比较差页面。 

4、如果你要查看这段时间执行了哪些sql语句，可以执行下面的语句结束跟踪: 


　　
　　SQL>execute sys.dbms_system.set_ev(49,3406,10046,0,'');
　　
　　PL/SQL procedure successfully completed. 

　　 
5、SQL trace 工具会收集这个过程中执行的SQL的性能状态数据，记录到一个跟踪文件中.这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等。 

6、这时候你可以通过下面的语句获得产生的跟踪文件所在的目录: 

　

　
　　SQL> select value from v$parameter where name = 'user_dump_dest';
　　
　　VALUE
　　
　  /opt/oracle/admin/ocn/udump 

　　 
7、在/opt/oracle/admin/ocn/udump下找到yzoracle_ora_9497.trc。9497是你当前应用的spid。 

8、注意yzoracle_ora_9497.trc是不可读的，我们需要执行oracle的tkprof命令，将yzoracle_ora_9497.trc转化为我们可读的文本文件。 

　

　
　　tkprof　yzoracle_ora_9497.trc　yzoracle_ora_9497.sql 

　　 
这样你就可以在yzoracle_ora_9497.sql文件中看到所有的sql语句执行次数,CPU使用时间等数据。 
------------------------------------------------------------------------------------
修改Oracle 11g账户过期时间
 
开发过程中不需要限制账户的过期时间，所以需要进行一些设置。 
  
1 查看用户的proifle是哪个，一般是default： 
 
SELECT username,PROFILE FROM dba_users; 
 
2 查看指定概要文件（如default）的密码有效期设置： 
 
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'; 
  www.2cto.com  
3 将密码有效期由默认的180天修改成“无限制”： 
 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
 
修改之后不需要重启动数据库，会立即生效。 
 
4 修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示； 
 
已经被提示的帐户必须再改一次密码，举例如下： 
 
$sqlplus / as sysdba 
 
sql> alter user smsc identified by <原来的密码> ----不用换新密码 
 
oracle11g启动参数resource_limit无论设置为false还是true，密码有效期都是生效的，所以必须通过以上方式进行修改。以上的帐户名请根据实际使用的帐户名更改。
 

-----------------发现的问题----------------
oracle 不能删除没有主外键关系的视图数据
如 A,B两表 没有主外键关系则
delete from (select * from a,b where a.id = b.id)
会产生 ora-01752 错误
-------------------------------------------