Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

there is a error "ORA-00907: missing right parenthesis" when I run this SQL

Quanwen ZhaoMay 22 2018 — edited May 24 2018

Hello, guys

My original SQL query is as follows (It runs okay),

SQL> SET LINESIZE 300

SQL> SET PAGESIZE 300

SQL>

SQL> COLUMN table_owner FORMAT a20

SQL> COLUMN table_name  FORMAT a25

SQL> COLUMN truncated   FORMAT a5

SQL>

SQL> SELECT table_owner

  2         , table_name

  3         , inserts

  4         , updates

  5         , deletes

  6         , timestamp

  7         , truncated

  8         , drop_segments

  9  FROM dba_tab_modifications

10  WHERE table_owner NOT IN (

11                            'ANONYMOUS'

12                            , 'APEX_030200'

13                            , 'APEX_PUBLIC_USER'

14                            , 'APPQOSSYS'

15                            , 'CTXSYS'

16                            , 'DBSNMP'

17                            , 'DIP'

18                            , 'EXFSYS'

19                            , 'FLOWS_FILES'

20                            , 'MDDATA'

21                            , 'MDSYS'

22                            , 'MGMT_VIEW'

23                            , 'OLAPSYS'

24                            , 'ORACLE_OCM'

25                            , 'ORDDATA'

26                            , 'ORDPLUGINS'

27                            , 'ORDSYS'

28                            , 'OUTLN'

29                            , 'OWBSYS'

30                            , 'OWBSYS_AUDIT'

31                            , 'SCOTT'

32                            , 'SI_INFORMTN_SCHEMA'

33                            , 'SPATIAL_CSW_ADMIN_USR'

34                            , 'SPATIAL_WFS_ADMIN_USR'

35                            , 'SQLTXADMIN'

36                            , 'SQLTXPLAIN'

37                            , 'SYS'

38                            , 'SYSMAN'

39                            , 'SYSTEM'

40                            , 'WMSYS'

41                            , 'XDB'

42                            , 'XS$NULL'

43                           )

44  AND table_name NOT LIKE 'BIN$%'

45  ORDER BY timestamp DESC

46           , table_owner

47           , table_name

48  ;

TABLE_OWNER          TABLE_NAME                   INSERTS    UPDATES    DELETES TIMESTAMP           TRUNC DROP_SEGMENTS

-------------------- ------------------------- ---------- ---------- ---------- ------------------- ----- -------------

...

...

41 rows selected.

SQL>

But I feel that excluding each production username manually one by one is so troublesome, hence I find a simple SQL for querying all of production username on oracle database.

It looks like below,

SQL> SELECT name schema_to_exclude

  2  FROM system.LOGSTDBY$SKIP_SUPPORT

  3  WHERE action = 0

  4  ORDER BY schema_to_exclude

  5  /

SCHEMA_TO_EXCLUDE

-----------------

ANONYMOUS

APEX_030200

APPQOSSYS

BI

CTXSYS

DBSNMP

DIP

DMSYS

EXDSYS

EXFSYS

LBACSYS

MDSYS

MGMT_VIEW

MTSSYS

ODM

ODM_MTR

OLAPSYS

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

SI_INFORMTN_SCHEMA

SYS

SYSMAN

SYSTEM

TSMSYS

WKPROXY

WKSYS

WK_TEST

WMSYS

XDB

XS$NULL

XTISYS

35 rows selected.

SQL>

So I put the SQL above into my first SQL's ellipsis that place ("WHERE table_owner NOT IN (...)") and then I eliminate each production username manually one by one.

Unfortunately I run the previous SQL I've modified finally, it always shows error "ORA-00907: missing right parenthesis".

Please see my execution result below,

SQL> SET LINESIZE 300

SQL> SET PAGESIZE 300

SQL>

SQL> COLUMN table_owner FORMAT a20

SQL> COLUMN table_name  FORMAT a25

SQL> COLUMN truncated   FORMAT a5

SQL>

SQL> SELECT table_owner

  2         , table_name

  3         , inserts

  4         , updates

  5         , deletes

  6         , timestamp

  7         , truncated

  8         , drop_segments

  9  FROM dba_tab_modifications

10  WHERE table_owner NOT IN

11  (

12    SELECT name schema_to_exclude

13    FROM system.LOGSTDBY$SKIP_SUPPORT

14    WHERE action = 0

15    ORDER BY schema_to_exclude

16  )

17  AND table_name NOT LIKE 'BIN$%'

18  ORDER BY timestamp DESC

19           , table_owner

20           , table_name

21  ;

  ORDER BY schema_to_exclude

  *

ERROR at line 15:

ORA-00907: missing right parenthesis

SQL>

Could you help me to troubleshooting it? Thanks a lot.

Best Regards

Quanwen Zhao

This post has been answered by SeánMacGC on May 22 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 21 2018
Added on May 22 2018
11 comments
13,835 views