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