Hello,experts
i execute two SQL query below is Ok,see my query process as follows,
SYS@ysyktest> set linesize 300
SYS@ysyktest> set pagesize 300
SYS@ysyktest> col sample_time for a25
SYS@ysyktest> select distinct h.sql_id from dba_hist_active_sess_history h,dba_hist_sqltext s where h.sql_id = s.sql_id and h.sample_time between to_date('2017-11-07 09:29','yyyy-mm-dd hh24:mi:ss') and to_date('2017-11-07 10:51','yyyy-mm-dd hh24:mi:ss') order by 1;
SQL_ID
--------------------------
0nwnndkak8k63
1am664kr6yg6f
3ufgtqbyzb1c2
772s25v1y0x8k
a1xgxtssv5rrp
aq8yqxyyb40nn
bunssq950snhf
dc995nm730t2n
f2rqzkgkdas2t
fzrshwabvtwc0
g4gp07gt2z920
11 rows selected.
About SQL_ID of the second SQL query below(in where clause) comes from the first SQL query's result,see my second SQL query as follows,
SYS@ysyktest> select sql_fulltext from v$sqlstats where sql_id in ('0nwnndkak8k63','1am664kr6yg6f','3ufgtqbyzb1c2','772s25v1y0x8k','a1xgxtssv5rrp','aq8yqxyyb40nn','bunssq950snhf','dc995nm730t2n','f2rqzkgkdas2t','fzrshwabvtwc0','g4gp07gt2z920');
SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------
update sys.job$ set this_date=:1 where job=:2
SELECT T.TABLE_NAME, T.TABLESPACE_NAME, T.NUM_ROWS, TC.COMMENTS, T.LOGGING, T.TABLE_LOCK, T.ROW_MOVEMENT, T.CLUSTER_NAME, T.PCT_FREE, T.PCT_USED, T.INI_TRANS, T.MAX_TRANS, T.INITIAL_EXTENT, T.NEXT_EXTENT, T.MIN_EXTENTS, T.MAX_EXTENTS, T.PCT_INCREASE, T.FREELISTS, T.FREELIST_GROUPS, T.BLOCKS, T.EMPTY_BLOCKS, T.AVG_SPACE, T.CHAIN_CNT, T.AVG_SPACE_FREELIST_BLOCKS, T.NUM_FREELIST_BLOCKS, T.SAMPLE_SIZE, T.GLOBAL_STATS, T.USER_STATS, T.DURATION, T.SKIP_CORRUPT, T.AVG_ROW_LEN, T.OBJECT_ID_TYPE, T.TABLE_TYPE_OWNER, T.TABLE_TYPE, T.COMPRESS_FOR, T.STATUS DROP_TABLE_STATUS, T.COMPRESSION, T.DROPPED, T.CLUSTER_OWNER, T.DEPENDENCIES, T.IOT_NAME, T.BACKED_UP, T.DEGREE, T.INSTANCES, T.CACHE, T.LAST_ANALYZED, T.PARTITIONED, T.IOT_TYPE, T.TEMPORARY, T.SECONDARY, T.NESTED, T.BUFFER_POOL, T.MONITORING, (SELECT STATUS FROM SYS.ALL_OBJECTS WHERE OWNER = T.OWNER AND OBJECT_NAME = T.TABLE_NAME AND OBJECT_TYPE = 'TABLE' AND SUBOBJECT_NAME IS NULL) STATUS, (SELECT GENERATED FROM SYS.ALL_OBJECTS WHERE OWNER = T.OWNER AND OBJECT_NAME = T.TABLE_NAME AND OBJECT_TYPE = 'TABLE' AND SUBOBJECT_NAME IS NULL) GENERATED FROM SYS.ALL_ALL_TABLES T, SYS.ALL_TAB_COMMENTS TC WHERE T.IOT_NAME IS NULL AND T.NESTED = 'NO' AND T.SECONDARY = 'N' AND NOT EXISTS (SELECT 1 FROM SYS.ALL_MVIEWS MV WHERE MV.OWNER = T.OWNER AND MV.MVIEW_NAME = T.TABLE_NAME) AND TC.OWNER(+) = T.OWNER AND TC.TABLE_NAME(+) = T.TABLE_NAME AND T.OWNER = 'SZD_BASE_V2'
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice
select shared_pool_size_for_estimate s, shared_pool_size_factor * 100 f, estd_lc_load_time l, 0 from v$shared_pool_advice
select sum(used_blocks), ts.ts# from GV$SORT_SEGMENT gv, ts$ ts where gv.tablespace_name = ts.name and ts.bitmapped <> 0 group by ts.ts#
select max(FA#) from SYS_FBA_FA
select count(distinct(s.area_id))
,count(*)
,nvl(sum(s.primary_teacher_num),0) + nvl(sum(s.junior_teacher_num),0) + nvl(sum(s.senior_teacher_num),0) + nvl(sum(s.teacher_num),0) as teacher_cnt
,nvl(sum(s.primary_student_num),0) + nvl(sum(s.junior_student_num),0) + nvl(sum(s.senior_student_num),0) + nvl(sum(s.student_num),0) as student_cnt
from base_school s, base_area a
where a.delete_flag = 0
and s.area_id = a.area_id
and a.area_code like :1
and (s.xxjgbxlxm3='218' or s.xxjgbxlxm3='211' or s.xxjgbxlxm3='311' or s.xxjgbxlxm3='321' or s.xxjgbxlxm3='312' or s.xxjgbxlxm3='342' or s.xxjgbxlxm3='341' or s.xxjgbxlxm3='345' or s.xxjgbxlxm2='36' )
and (s.delete_flag=0 or (s.delete_flag=1 and s.delete_type=9))
and (s.school_status in ('schoolstatus.1') or s.school_status is null)
and s.st_schoolnet_bandwidth = 'schoolnet.bandwidth.00'
insert into sd_res_resource_status_final1 (select distinct * from SD_RES_RESOURCE_STATUS_FINAL)
select /*+rule*/ object_name, owner object_owner, status, object_type, created, last_ddl_time
from sys.all_objects o
where object_type = 'TABLE'
and object_name not like 'BIN$%'
order by decode(owner, user, 0, 1),owner,object_name
update sys.scheduler$_job set last_start_date = :1, running_instance = :2, running_slave = :3, job_status = :4 where obj# = :5
But when i have combined two SQL query above to only one and it looks like below,
SYS@ysyktest> select sql_fulltext from v$sqlstats where sql_id in
2 (
3 select distinct h.sql_id from dba_hist_active_sess_history h,dba_hist_sqltext s
4 where h.sql_id = s.sql_id
5 and h.sample_time between to_date('2017-11-07 09:29','yyyy-mm-dd hh24:mi:ss') and to_date('2017-11-07 10:51','yyyy-mm-dd hh24:mi:ss')
6 order by 1
7 );
order by 1
*
ERROR at line 6:
ORA-00907: missing right parenthesis
But why show error 'ORA-00907: missing right parenthesis' in my third SQL query ?
Please guide me for solving this strange issue,thanks!
Best Regards,
Quanwen Zhao