你需要写到一个包中:create or replace package pag_cs_power as
type c_type is ref cursor; function fun_cs_getdictlist( v_dictindex in varchar2) return c_type;
end pag_cs_power;
函数代码:
function fun_cs_getdictlist( v_dictindex in varchar2) return c_type as c_cursor c_type;begin open c_cursor for select dictid,dictname from sys_dict where dictindex = v_dictindex; return c_cursor;end fun_cs_getdictlist;----------------------------------------------------------------------3、p4机器安装oracle
8、复制空表结构 create table new_tableas select * from old_table where 1=2; 复制表(含记录) create table new_tableas select * from old_table ;
9、把一个用户下的表导入到另一个用户下,但需要改名
先用exp导出所有的表;用imp将导出的表导入到新用户;在新用户下,执行 select 'rename table '||tname||' to new_'||tname||';' from tab where tabtype='table';将上面的查询结果保存到一个sql文件中,处理后执行就可以了。
10、审计步骤
修改参数文件init.ora,参数audit_trail值为true; 重新启动数据库; 打开审计audit session; (audit session by username) 执行登录操作; 察看审计结果: select * from dba_audit_session; select * from sys.aud$; select * from dba_audit_trail; select * from dba_audit_exists; 关于审计:
对于后面一项的修改只对windows操作系统进行,对unix操作系统则不需要。如果在windows操作系统中不修改该项,在oracle enterprise manager中,连接系统时,会提示如下的错误:ora-12560 tns:protocol adapter error 或者 ora-12545 connect failed because target host or object does not exist 重新连接sql plus worksheet
可以在vc下调用存储过程来实现例子:先修改init.ora例如:utl_file_dir=/usr //路径为 oracle所在的盘:/usr此过程将用户temp的p1过程的代码保存到oracle安装盘下/usr/text.txt中create or replace procedure testis file_handle utl_file.file_type; stor_text varchar2(4000); n number;i number; begini:=1; select max(line) into n from all_source where owner='temp' and name='p1';file_handle:=utl_file.fopen('/usr','test.txt','a');while i<=n loop select text into stor_text from all_source where owner='temp' and name='p1' and line= i; i:=i+1; utl_file.put_line(file_handle,stor_text);end loop; utl_file.fclose(file_handle);commit;end test;/
24、关于修改oracle的列宽(1)、不论如何都要备份数据。(2)、如果没有数据,则可以修改宽度。比如number,char,varchar2(3)、如果有数据,则可以增加宽度。比如number,char,varchar2 注意:不可以减小宽度。(4)、语法:alter talbe tablename modify columnname columntype not null;
25、如何查看用户的存储过程和函数
select name,text from user_source where name= procedurename and type = 'procedure' order by line;
select tb1.r1 from tb1, tb2 where tb1.r2 = tb2.r2 and tb2.r1 = xxx for update of tb1.r1 nowait
28、得到列的信息
desc tablenameselect cname from col where tname='tablename';select column_name from user_tab_columns where table_name='tablename';select column_name from all_tab_columns where table_name='tablename';select column_name from dba_tab_columns where table_name='tablename';select column_name from user_col_comments where table_name='tablename'; select column_name from all_col_comments where table_name='tablename'; select column_name from dba_col_comments where table_name='tablename';
创建索引:create index ind_name on table_name(col1,col2,...);
重建索引:alter index ind_name rebuild;
查看索引:
select * from user_indexes where index_name='ind_name';
35、oracle如何查杀用户的进程 一|根据用户的应用程序和sql语句,在dba studio找到用户的session并断开其连接 二、 (1)、要杀掉一个session应先应知道其sid和serial#,假设你已经知道。 (2)、select paddr from v$session where sid=v_sid and serial#=v_serial# select spid from v$process where addr=paddr(以上语句所查出的);
oracle8i & lower versionconnect sys/passed;update col$ set name=xx where obj#=对象id and name = 字段(一般不要这样用,会造成意想不到的结果)注:最好是删除再建立新的列
39、把用户模式对象所在的表空间移到新的表空间(1). create the new tablesapce(2). alter user test default tablespace test_data;(3). alter user test quota unlimited on test_data;(4). alter table the_table_name move tablespace test_data; 生成脚本: select 'alter table'||tname||' move tablespace test_date;' from tab where tabtype='table'(5). rebuild the indexes;
建立数据库连接create database link dbaselinkname connect to username identified by password using 'netservicename';dbaselinkname 是建立的数据连接名称username 是可以连接到的用户名password 是可以连接到的用户的密码netservicename 是可以连接的数据库网络服务名或是数据库名
46、错误号ora-01536:space quota exceeded for table space 'alcatel'的解决办法 三个解决办法,任你选择: (1) alter user username quota 100m on tablespacename; (2) alter user username quota unlimited on tablespacename; (3) grant unlimited tablespace to username;
47、如何在oracle中捕获到sql语句的全部操作内容
select osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;
48、oracle中如何实现自增字段:
(1)第一种方法 oracle一般的做法是同时使用序列和触发器来生成一个自增字段.create sequence seqname increment by 1 start with 1 maxvalue 99999999/create trigger trgnamebefore insert on table_namereferencing new as :newfor each rowbegin select seqname.nextval into :new.fieldname from dual;end;
(2)第二种方法:create or replace trigger tr1 before insert on temp_table for each rowdeclare com_num number;beginselect max(id) into com_num from temp_table; :new.id:=com_num+1;end tr1;
select userenv('language') from dual 字符集 select userenv('isdba') from dual 是否dba select userenv('sessionid') from dual sessionid select userenv('terminal') from dual 客户端名字 select userenv('instance') from dual 实例数
sys_context() 函数 select sys_context('userenv','current_schema') from dual; 当前模式 select sys_context('userenv','current_schemaid') from dual; 当前模式id select sys_context('userenv','current_user') from dual; 当前用户 select sys_context('userenv','db_name') from dual; 数据库 select sys_context('userenv','host') from dual; 主机 ..........
60、删除重复列的方法
(1) delete from table_name a where rowid > ( select min(rowid) from table_name b where a.key_values = b.key_values);(2) create table table2 as select distinct * from table1; drop table1; rename table2 to table1;(3) delete from mytable where rowid not in( select max(rowid) from mytable group by column_name );(4) delete from mytable t1 where exists (select 'x' from my_table t2 where t2.key_value1 = t1.key_value1 and t2.key_value2 = t1.key_value2 ... and t2.rowid > t1.rowid);
shutdown immediatecopy 备份文件到需要恢复的目录下startup mountrecover database until time '2002-12-26 09:00:00'
alter database open resetlogs
自己仔细检查一下,不会发生这样的问题的。
64、oracle如何设置查询超时
select /*+ timeout 30*/ * from verylargetable
65、修改字符集
(1)、alter databae character set simplified chinese_china.zhs16gbk ;
(2)、update props$ set value$='zhs16cgb231280' where name='nls_characterset';update props$ set value$='zhs16cgb231280' where name='nls_nchar_characterset';
建议不使用(2)
注意:(1)、执行alter database character set必须有sysdba权限,并且在startup restrict模式下执行(2)、原字符集必须是目标字符集的一个真子集(就是浪子所说的只能从we8iso8859p1转到zhs16gbk的原因)(3)、clob字段装换可能有问题,建议在转换以前把有clob字段的表导出后drop,转换以后再导回(4)、该转换不可逆,所以在做这个操作以前建议做数据库全备份
66、修改数据库名字
(1)、启动svrmgrl,以文本方式备份控制文件 oracle>svrmgrl svrmgrl>connect internal svrmgrl>alter system backup controlfile to trace(2)、编辑产生的跟踪文件,在udump目录下 改create controlfile reuse database "ctc" noresetlogs archivelog 中的reuse为set 然后把create controlfile这段语句拷出(3)、正常宕库,后启动到nomount下 svrmgrl>shutdown immediate svrmgrl>startup nomount(4)、执行create controlfile那段语句(5)、打开数据库 svrmgrl>alter database open 如提示用resetlogs选项则使用 svrmgrl>alter database open resetlogs(8)、相应修改初始化参数
67、rownum的用法
select * from (select t.*,rownum id from dept t) where id between 1 and 20
68、oracle的内部参数
select a.ksppinm name, b.ksppstdf default_val, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx=b.indx and substr(a.ksppinm,1,1)='_' order by a.ksppinm
(1)、select upper(f.tablespace_name) 表空间名, d.tot_grootte_mb "表空间大小(m)", d.tot_grootte_mb - f.total_bytes "已使用空间(m)", round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2) "使用比", f.total_bytes "空闲空间(m)", f.max_bytes "最大块(m)" from (select tablespace_name, round(sum(bytes)/(1024*1024),2) total_bytes, round(max(bytes)/(1024*1024),2) max_bytes from sys.dba_free_space group by tablespace_name) f, (select dd.tablespace_name, round(sum(dd.bytes)/(1024*1024),2) tot_grootte_mb from sys.dba_data_files dd group by dd.tablespace_name) dwhere d.tablespace_name = f.tablespace_name order by 4 desc(2)、select tablespace_name,round(sum(bytes)/1024/1024,2) "m" from dba_free_space group by tablespace_name
alter index idx unusable;alter index idx_acctno disable;(only to a function based index)
73、在sqlplus中给指定用户进行 set autotrace on/off 以scott用户为例:
sql>connect scott/tiger connected. sql>@$oracle_home/rdbms/admin/utlxplan.sql table created. sql>connect / as sysdba connected. sql>@$oracle_home/sqlplus/admin/plustrce.sql drop role plustrace; role dropped. create role plustrace; role created. . grant plustrace to dba with admin option; grant succeeded.
select file_name,tablespace_name,autoextensible from dba_data_files;
--数据表空间
create tablespace user_data logging datafile 'd:/oracle/oradata/orcl/test.dbf' size 50m reuse , 'c:/users01112.dbf' size 50m reuse autoextend on next 1280k maxsize 16383m extent management local
--临时表空间
create temporary tablespace user_data_temp tempfile 'd:/temp0111.dbf' size 50m reuse autoextend on next 1024k maxsize 16383m extent management local uniform size 1024k
--增加数据文件
alter tablespace user_data add datafile 'c:/users01113.dbf' size 50m;
alter tablespace user_data add datafile 'c:/users01114.dbf' size 50m autoextend on;
--删除表空间
drop tablespace user_data including contents;
--修改数据文件大小 alter database datafile 'c:/users01113.dbf' resize 40m;
--创建用户、赋予权限
create user user_data profile default identified by user_data default tablespace user_data temporary tablespace user_data account unlock; grant connect to user_data;grant resource to user_data;