SQL> BEGIN 2 FOR I IN( select TEXT from dba_views where view_name not like '%$%' and view_name like 'V_%') LOOP 3 IF INSTR(I.TEXT,'U')>0 then 4 dbms_output.put_line(I.TEXT); 5 end if; 6 end loop; 7 end; 8 /
PL/SQL procedure successfully completed
SQL> set serveroutput on SQL> /
SELECT hist_id, seqno, sess_id, sess_ser, state, totsql2cpp, totcppexec, totcpp2sql FROM GV_Olapi_Session_History WHERE inst_id = USERENV('Instance') SELECT hist_id, seqno, xsoqsehi_seqno, iface_name, parent, state, xsoqophi_seqno, total_sql2cpp, total_cppexec, total_cpp2sql FROM GV_OLAPI_IFACE_OBJECT_HISTORY WHERE inst_id = USERENV('Instance') SELECT hist_id, seqno, xsoqojhi_seqno, method_name, rpcstyle, state, sql2cpp_time, cppexec_time, cpp2sql_time, global_heap, global_heap_size, global_heap_allocated_size, global_heap_nonperm_size, session_heap session_heap_size, session_heap_allocated_size, session_heap_nonperm_size FROM GV_OLAPI_IFACE_OP_HISTORY WHERE inst_id = USERENV('Instance') SELECT hist_id, seqno, leak, xsoqophi, alt_seqno, state, memop, heap, memaddr, memsize, filename, line FROM GV_OLAPI_MEMORY_OP_HISTORY WHERE inst_id = USERENV('Instance')
PL/SQL procedure successfully completed
SQL>
能够操作 LONG 的 SQL 语句: 1、Select语句2、Update语句中的SET语句3、Insert语句中的VALUES语句限制:1、一个表中只能包含一个 LONG 类型的列。2、不能索引LONG类型列。3、不能将含有LONG类型列的表作聚簇。4、不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into ...select。5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。6、不能对LONG类型列加约束条件(NULL、NOT NULL、DEFAULT除外),如:关键字列(PRIMARY KEY)不能是 LONG 数据类型。7、LONG类型列不能用在Select的以下子句中:where、group by、order by,以及带有distinct的select语句中。8、LONG类型列不能用于分布查询。9、PL/SQL过程块的变量不能定义为LONG类型。10、LONG类型列不能被SQL函数所改变,如:substr、instr。
用to_lob试试.由于to_lob只能用在insert into 的select 子句中,需要新建个表. SQL> drop table dba_views_b;
这个部分执行报错因为text内容比较多,超过了255 ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line ORA-06512: 在"SYS.DBMS_OUTPUT", line 35 ORA-06512: 在"SYS.DBMS_OUTPUT", line 133 ORA-06512: 在line 5这个部分是查询什么的啊?我执行的时候报表或视图不存在
不知道我这个思路能否解决问题哈,我的想法是 text like '%U%' 这个条件能否去掉或者是被替换掉呢?
--sql窗口 begin DBMS_OUTPUT.ENABLE(1000000); end;--命令窗口 exec DBMS_OUTPUT.ENABLE(1000000);
2 FOR I IN( select TEXT from dba_views where view_name not like '%$%' and view_name like 'V_%') LOOP
3 IF INSTR(I.TEXT,'U')>0 then
4 dbms_output.put_line(I.TEXT);
5 end if;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed
SQL> set serveroutput on
SQL> /
SELECT hist_id, seqno, sess_id, sess_ser, state,
totsql2cpp, totcppexec, totcpp2sql
FROM GV_Olapi_Session_History
WHERE inst_id = USERENV('Instance')
SELECT hist_id, seqno, xsoqsehi_seqno, iface_name, parent, state,
xsoqophi_seqno, total_sql2cpp, total_cppexec, total_cpp2sql
FROM GV_OLAPI_IFACE_OBJECT_HISTORY
WHERE inst_id = USERENV('Instance')
SELECT hist_id, seqno, xsoqojhi_seqno, method_name, rpcstyle, state,
sql2cpp_time, cppexec_time, cpp2sql_time, global_heap,
global_heap_size, global_heap_allocated_size, global_heap_nonperm_size,
session_heap session_heap_size, session_heap_allocated_size,
session_heap_nonperm_size
FROM GV_OLAPI_IFACE_OP_HISTORY
WHERE inst_id = USERENV('Instance')
SELECT hist_id, seqno, leak, xsoqophi, alt_seqno, state,
memop, heap, memaddr, memsize, filename, line
FROM GV_OLAPI_MEMORY_OP_HISTORY
WHERE inst_id = USERENV('Instance')
PL/SQL procedure successfully completed
SQL>
1、Select语句2、Update语句中的SET语句3、Insert语句中的VALUES语句限制:1、一个表中只能包含一个 LONG 类型的列。2、不能索引LONG类型列。3、不能将含有LONG类型列的表作聚簇。4、不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into ...select。5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。6、不能对LONG类型列加约束条件(NULL、NOT NULL、DEFAULT除外),如:关键字列(PRIMARY KEY)不能是 LONG 数据类型。7、LONG类型列不能用在Select的以下子句中:where、group by、order by,以及带有distinct的select语句中。8、LONG类型列不能用于分布查询。9、PL/SQL过程块的变量不能定义为LONG类型。10、LONG类型列不能被SQL函数所改变,如:substr、instr。
SQL> drop table dba_views_b;
Table dropped
SQL>
SQL> create table dba_views_b
2 (owner varchar2(30),
3 view_name varchar2(30),
4 text_length number,
5 text clob);
Table created
SQL>
SQL> insert into dba_views_b
2 select t.owner,t.view_name,t.text_length,to_lob(t.text) from dba_views t;
3681 rows inserted
SQL> commit;
Commit complete
SQL> select * from dba_views_b t where view_name not like '%$%'
2 and view_name like 'V_%'
3 and text like '%U%' and rownum=1;
OWNER VIEW_NAME TEXT_LENGTH TEXT
------------------------------ ------------------------------ ----------- --------------------------------------------------------------------------------
SYS V_OLAPI_SESSION_HISTORY 157 SELECT hist_id, seqno, sess_id, sess_ser, state,
totsql2cpp, totcppexec, to
SQL>
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: 在"SYS.DBMS_OUTPUT", line 35
ORA-06512: 在"SYS.DBMS_OUTPUT", line 133
ORA-06512: 在line 5这个部分是查询什么的啊?我执行的时候报表或视图不存在
begin
DBMS_OUTPUT.ENABLE(1000000);
end;--命令窗口
exec DBMS_OUTPUT.ENABLE(1000000);
你想直接修改dba_views来改变视图定义??
你可以先查出要修改的view,然后导出定义,再使用ue批量更改,然后再导入到数据库.
SQL> create or replace view t_test_emp as--首先scott下创建view
2 select empno,ename from emp
3 ;
View created
SQL> select t.obj# from obj$ t where t.name='T_TEST_EMP'--sys下查询对象id
2 ;
OBJ#
----------
63039
SQL> select * from view$ t where t.obj#=63039;--修改前数据
OBJ# AUDIT$ COLS INTCOLS PROPERTY FLAGS TEXTLENGTH TEXT
---------- -------------------------------------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
63039 -------------------------------------- 2 2 0 0 28 select empno,ename from emp
SQL> select * from view$ t where t.obj#=63039;--手工使用for update修改后数据
OBJ# AUDIT$ COLS INTCOLS PROPERTY FLAGS TEXTLENGTH TEXT
---------- -------------------------------------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
63039 -------------------------------------- 2 2 0 0 21 select empno from emp
SQL> select * from t_test_emp;--修改后在scott下查询view,报错.
select * from t_test_emp
ORA-00600: 内部错误代码, 参数: [12820], [], [], [], [], [], [], []