oracle 判断游标是否有值_oracle游标执行动态sql

Oracle常用操作手册（持续更新务实版） 　　一般日常维护的都会放进碗里来的 　　数据库配置 　　修改Oracle内存 　　查看当前内存配置 　　show parameter memory,万一配置错了起不来，只能通过pfile文件重新启动



　　修改memory_target 　　MEMORY_TARGET：oracle所能使用的最大内存，该参数可以动态调整 　　MEMORY_MAX_TARGET：MEMORY_TARGET参数所能动态设定的最大值，不能动态调整，需要重启数据库 　　SQL>alter system set memory_max_target=2048M scope=spfile; 　　SQL> alter system set memory_target =2048M scope=spfile;

　　重启数据库后生效：

　　连接数/会话 　　oracle限制某个用户的连接数 　　1. 查看resource_limit参数： 　　SQL> show parameter resource_limit 　　设置 RESOURCE_LIMIT参数为TRUE，开启资源限制： 　　alter system set resource_limit =TRUE; 　　该改变对密码资源无效，密码资源总是可用的 　　2. 创建PROFILE ： 　　SQL>create profile sessnum limit sessions_per_user 20; –最大连接数限制为20 　　3. 将PROFILE指定给用户： 　　SQL>alter user test profile sessnum; 　　另外，SESSIONS_PER_USER不会像10g和11g那样限制12C中的并行从进程。 　　drop profile sessnum cascade; 　　当某个profile被删除时，如果这个profile已经被分配给某个用户，那么我们在删除的时候要加上cascade，并且已经被分配的用户的profile会被自己修改成default profile。 　　当前进程的连接数 　　select count(*) from v$process; 　　当前会话的连接数 　　select count(*) from v$session; 　　查看数据库的并发连接数 　　select count(*) from v$session where status=’ACTIVE’; 　　查询数据库允许的最大连接数 　　select value from v$parameter where name = ‘processes’; 　　修改数据库允许的最大连接数： 　　alter system set processes = 300 scope = spfile; 　　(需要重启数据库才能实现连接数的修改) 　　查看当前数据库建立的会话情况 　　select count(1),username from v$session group by username 　　结束/杀掉会话 　　SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = ‘ETC’; 　　ALTER SYSTEM KILL SESSION ‘13,772’; 　　字符集 　　SIMPLIFIED CHINESE_CHINA.ZHS16GBK 　　AMERICAN_AMERICA.ZHS16GBK 　　注意：以上两种字符集是一样的 　　select * fromnls_database_parameters(服务端字符) 　　select * fromnls_session_parameters(客户端字符) 　　查询语句：select userenv(‘language’) from dual;(客户端字符) 　　查询结果：SIMPLIFIED CHINESE_CHINA.ZHS16GBK 　　oracle最大游标数修改和查询 　　查看ORACLE最大游标数 　　SQL> show parameter open_cursors; 　　查看当前打开的游标数目 　　SQL> select count(*) from v$open_cursor; 　　修改ORACLE最大游标数 　　SQL> alter system set open_cursors=1000 scope=both; 　　关闭密码过期 　　–1、查看用户的proifle是哪个，一般是default： （DBSNMP=》MONITORING_PROFILE） 　　SELECT username,PROFILE FROM dba_users; 　　–2、查看指定概要文件（如default）的密码有效期设置： 　　SELECT * FROM dba_profiles s WHERE resource_name=’PASSWORD_LIFE_TIME’; 　　–3、将密码有效期由默认的180天修改成“无限制”： 　　ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 　　ALTER PROFILE MONITORING_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; 　　查看Oracle版本 32位/64位 　　select * from v$version 　　Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 　　Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 　　启动数据库 　　startup nomount选项：（通常启动到这里可以做create database , create or recreate control file 或者是mount standby database等动作） 　　startup mount 选项 （通常启动到这里的目的有recover dtabase ; backup database ; rename db files ,change archivelog mode等） 　　startup open 选项：（打开数据文件，日志文件） 　　startup mount 　　alter database open read only; 　　startup mount 　　alter database open read write; 　　【注意：alter database * 命令需要在database为mount状态下才行，如果数据库已启动，先要shutdown】 　　关闭数据库 　　重启数据库： 　　shutdown immediate; 　　startup; 　　使用shutdown的时候 normal和immediate可能都因为连接没有释放而造成数据库无法重启的现象。 　　shutdown abort; 　　startup 　　表名信息查询 　　select * from tabs where table_name like ‘%BAK’ 　　重新配置em https=>http 　　emca -config dbcontrol db 　　zjts 　　1521

　　http://opcserver252:1158/em/console 　　

　　监听日志（windows下监听日志超过4G监听会失败） 　　

　　日志位置：/home/oracle/app/oracle/diag/tnslsnr/primary/listener/trace 　　日志内容：

　　1.>lsnrctl set log_status off # 先把日志状态停掉，这样就不会写监听器日志 　　2. 备份或者删除监听日志 　　3.>lsnrctl set log_status on # 重新打开日志，开始记录监听器日志。该文件会自动创建 　　4.>lsnrctl status #检查监听状态 　　归档操作 　　删除归档日志 　　rman target system/Zjts8356@zjts 　　list archivelog all; 　　delete archivelog all completed before ‘sysdate-3’; 　　主库执行后，备库已同步控制文件，所以备库直接删除物理日志好了 　　可以直接删除物理文件 　　直接系统删除后，控制文件中的记录还在 　　RMAN> crosscheck archivelog all;（将磁盘或者磁带上不存在的日志标记为expired 这个检查应该是控制文件跟实际文件进行的一个效验） 　　delete expired archivelog all; 删除过期的归档日志（这里的过期是指手动实际删除日志，这里的删除应该是控制文件中的记录删除） 　　（备库的日志基本不受控制，在备库上操作跟在主库上操作一致） 　　Linux下操作删除 　　[oracle@primary Desktop]$ rman 　　RMAN> connect target / 　　关闭归档 　　shutdown immediate; 　　startup mount; 　　alter database noarchivelog; 　　archive log list; 　　alter database open; 　　审计 　　查看有哪些对象审计权限和对哪些用户进行了对象权限审计 　　select * from dba_priv_audit_opts order by user_name; 　　*在11g中默认启用了对登录注销操作LOGON/LOGOFF的审计 　　select count(1),username from dba_audit_trail group by username –登录登出数量统计 　　select action_name,count(*) from dba_audit_trail group by action_name; –基本都是LOGON LOGOFF 　　取消此类审计： 　　noaudit session whenever successful; 　　可以取消对一些登录特别频繁的用户的审计： 　　SQL> noaudit session by ts_ahs; （无效） 　　SQL> noaudit session whenever successful;（有效） 　　查看审计参数 　　SQL> show parameter audit 　　NAME TYPE VALUE 　　———————————— ———– —————————— 　　audit_file_dest string C:\APP\ADMINISTRATOR\ADMIN\ZJTS\ADUMP 　　audit_sys_operations boolean FALSE 　　audit_trail string DB 　　开启审计功能后，这个值会非常大 　　select count(*) sum from AUD$; 　　truncate table aud$; –清除数据 　　关闭审计功能 　　alter system set audit_trail=none scope=spfile; 　　shutdown immediate; 　　startup; 　　如果不想重启数据库，毕竟在用的东西哪能说重启就重启，可以使用如下： 　　SQL> noaudit all statements; 　　Noaudit succeeded. 　　SQL> noaudit all privilege; 　　Noaudit succeeded. 　　通过上面2条命令基本上都可以关闭了，还剩下一个EXEMPT ACCESS POLICY，单独noaudit一下就可以了： 　　SQL> noaudit EXEMPT ACCESS POLICY; 　　Noaudit succeeded. 　　自此所有开启的审计选项全部关闭了。 　　表和表空间 　　查看所有表的行数 　　如果你想刷新 user_tables 表，可以通过执行以下语句来实现： 　　EXEC DBMS_STATS.GATHER_TABLE_STATS(‘USER’, ‘TABLE_NAME’); 　　其中，将 USER 替换为你自己的用户名， TABLE_NAME 替换为你想要刷新的表的名称。 　　视图表 　　–dba_tables : 系统里所有的表的信息，需要DBA权限才能查询 　　–all_tables : 当前用户有权限的表的信息 　　–user_tables: 当前用户名下的表的信息 　　查看有哪些表空间： 　　select tablespace_name from dba_tablespaces; 　　select tablespace_namefrom dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’USERS’,’UNDOTBS1′) and contents not in (‘TEMPORARY’) 　　查询表空间和物理文件的对应关系 　　select file_name,tablespace_name,bytes from dba_data_files order by tablespace_name,file_name 　　删除非空表空间，包含物理文件 　　drop tablespace NCC_INDEX03 including contents and datafiles; 　　表空间重命名 　　alter tablespace jdmp_data rename to energy; 　　修改表空间的数据文件名字（联机状态） 　　查看表空间状态 　　select tablespace_name,status from dba_tablespaces; 　　查看是否BigFile 是否自增长 已分配容量，最大容量 　　SELECT T.TABLESPACE_NAME,T.bigfile,D.FILE_NAME, 　　D.AUTOEXTENSIBLE,D.BYTES/(1024 * 1024)||’MB’,D.MAXBYTES/(1024 * 1024)||’MB’,D.STATUS 　　FROM DBA_TABLESPACES T,DBA_DATA_FILES D 　　WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 　　ORDER BY TABLESPACE_NAME,FILE_NAME;

　　给某表空间添加数据文件 　　如果表空间不是大文件类型，会有最大容量限制，默认32GB，如果满了，则需要添加数据文件。 　　ALTER TABLESPACE HRRTF ADD DATAFILE 　　’/home/oracle/zjtsdb/orcl/hrrtf2.dbf’ SIZE 100M 　　AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; 　　查看表空间使用情况 　　select f.tablespace_name, 　　a.total “total (M)”, 　　f.free “free (M)”, 　　round((f.free / a.total) * 100) “% Free” 　　from (select tablespace_name, sum(bytes / (1024 * 1024)) total 　　from dba_data_files 　　group by tablespace_name) a, 　　(select tablespace_name, round(sum(bytes / (1024 * 1024))) free 　　from dba_free_space 　　group by tablespace_name) f 　　where a.tablespace_name = f.tablespace_name(+) 　　order by “% Free”; 　　创建表空间CREATE BIGFILE TABLESPACE 　　CREATE BIGFILE TABLESPACE “HRRTF” DATAFILE ‘/home/oracle/zjtsdb/orcl/HRRTF’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 　　CREATE BIGFILE TABLESPACE “NNC_DATA01” DATAFILE ‘/home/oracle/zjtsdb/orcl/NNC_DATA01’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 　　CREATE BIGFILE TABLESPACE “NNC_DATA02” DATAFILE ‘/home/oracle/zjtsdb/orcl/NNC_DATA02’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 　　CREATE BIGFILE TABLESPACE “NNC_DATA03” DATAFILE ‘/home/oracle/zjtsdb/orcl/NNC_DATA03’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 　　CREATE BIGFILE TABLESPACE “NNC_INDEX01” DATAFILE ‘/home/oracle/zjtsdb/orcl/NNC_INDEX01’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 　　CREATE BIGFILE TABLESPACE “NNC_INDEX02” DATAFILE ‘/home/oracle/zjtsdb/orcl/NNC_INDEX02’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 　　CREATE BIGFILE TABLESPACE “NNC_INDEX03″ DATAFILE ‘/home/oracle/zjtsdb/orcl/NNC_INDEX03’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 　　查看某用户用到了哪些表空间 　　select DISTINCT owner,tablespace_name from dba_extents where owner like ‘NC50′; 　　查看用户和其默认表空间 　　select username,default_tablespace,temporary_tablespace from dba_users where username=’YFRTF’; 　　查看表和表所在表空间 　　Select Table_Name, Tablespace_Name From Dba_Tables Where Tablespace_Name = ‘NNC_INDEX01′; 　　创建数据库 dbca 　　存储过程 　　查看有多少存储过程 　　select count(*) from user_objects where object_type=’PROCEDURE’; 　　用户账号 　　创建用户 　　Create user yfrtf identifiedby zjts8356; 　　Grant connect,resource,dba to yfrtf; 　　Alter user yfrtf default tablespace hrrtf; 　　删除用户 　　drop user : 仅仅是删除用户,drop user username cascade :会删除此用户名下的所有表和视图 　　查看用户状态 　　select username,account_status from dba_users; 　　查看有哪些用户 　　select * from all_users; 　　解锁账户 　　ALTER USER DANGZHENG ACCOUNT UNLOCK; 　　修改密码 　　修改报警用户的密码 　　alter user zjts identified by zjts8356; 　　查看当前用户所有对象和数量统计 　　select object_type , count(*) from user_objects group by object_type 　　查看当前用户下有多少表（登录后查询） 　　select count(*) from user_tables 　　数据备份还原 　　1、数据泵工具运行于服务器端，相比客户端的exp/imp其性能更好，并能实现exp/imp的全部功能。 　　2、通过使用exclude,include,content等参数，数据泵可以为数据及数据对象提供更细微级别的选择性。 　　3、通过设定数据库版本号，数据泵可以兼容老版本的数据库系统。 　　4、并行执行。 　　5、通过estimate_only参数，数据泵可以预估导出作业所需的磁盘空间。 　　6、支持分布式环境中通过数据库连接实现导入\导出。 　　7、支持导入时重新映射功能（即将对象导入到新的目标数据文件、架构及表空间等）。 　　8、支持数据压缩及数据采样。 　　创建逻辑目录 　　create directory bakdata as ‘/home/oracle/bak’; 　　grant read,write on directory bakdata to system; 　　exp/imp 数据备份还原 　　还原数据： 　　imp yfrtf/zjts8356 file=/home/oracle/_0101.dmp feedback=10000 buffer= ignore=y full=y log=/home/oracle/impYfrtf.log 　　还原指定表： 　　imp yfrtf/zjts8356 file=/home/oracle/_0101.dmp feedback=10000 buffer= ignore=y log=/home/oracle/impAtt.log tables=attdaypay 　　远程备份 　　exp yfrtf/zjts8356@192.168.12.88/orcl direct=y recordlength=65535 file=%backuppath%\yfrtf%curdate%.dmp log=%backuppath%\%curdate%.log tables=(peremployee) 　　expdp/impdp数据泵 　　只导出表结构： 　　expdp nc50/nc50 directory=bakdata dumpfile=nc02.dmp CONTENT=METADATA_ONLY tables=XX_XLOG,SM_OPERATELOG 　　只导出指定表 　　导出全部表但不包括指定的表： 　　expdp nc50/nc50 schemas=NC50 directory=bakdata dumpfile=NC50_%U.dmp logfile=NC50.log parallel=8 EXCLUDE=TABLE:\”IN (‘XX_XLOG’,’SM_OPERATELOG’,’BD_CUMANDOC_0531BAK’,’BD_PRODUCE_BAK’,’FA_CARDHISTORY_BAK’,’GL_BANKRECEIPT_BAK’,’GL_FREEVALUEBAK’,’PUB_BILLTEMPLET_BBAK’,’SO_SALEORDER_B_BAK’,’GL_DETAILBACKUP’)\” 　　开8线程导出表： 　　expdp system/Zjts8356 schemas=YFRTF directory=bakdata dumpfile=YFRTF_%U.dmp logfile=YFRTF.log parallel=8; 　　导入数据： 　　impdp system/zjts8356 schemas=YFRTF directory=bakdata dumpfile=YFRTF_%U.dmp logfile=YFRTF_imp.log parallel=8; 　　导入数据并变更用户名 　　从user1用户导出的数据，要导入到user2用户下： 　　REMAP_SCHEMA=user1:user2 　　示例：

　　导入数据并变更表空间 　　remap_tablespace=原表空间名称:新表空间 　　停止数据泵任务 　　复制备份文件至另一服务器 　　查杀僵死session 　　查找是否有用户类型的锁 　　TM DML排队 　　TX 事务处理排队 　　UL 用户提供 　　select * from v$lock where type in (‘TM’,’TX’,’UL’) 　　再查相应的sid和serial 　　select sid,serial#,terminal,program,event from v$session where sid=168 　　杀掉它 　　alter system kill session ‘168,62660’ 　　–若出现错误ORA-00031: 标记要终止的会话，则 　　SELECT P.SPID, S.SID, S.SERIAL#, S.USERNAME, S.PROGRAM FROM V$PROCESS P,v$session S 　　WHERE P.ADDR=S.PADDR AND S.STATUS=’KILLED’; 　　系统级杀死进程 　　–ORAKILL用法: 　　orakill sid thread 　　其中： 　　sid：表示要杀死的进程属于的实例名 　　thread：是要杀掉的线程号，即第3步查询出的spid。 　　例：c:>orakill orcl 12345 　　kill –9 SPID;（linux）