Tuesday, March 2, 2010

DBA_TABLES vs DBA_OBJECTS

CJUSTICE@TESTING>SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Last night I was installing the Unit Testing repository for SQL Developer for a fun little post. After installing the repository, I just did a brief sanity check.
CJUSTICE@TESTING>SELECT owner, COUNT(*) c
2 FROM dba_objects
3 GROUP BY owner
4 ORDER BY 1;

OWNER C
------------------------------ ----------
...snip
SI_INFORMTN_SCHEMA 8
SYS 22970
SYSMAN 1341
SYSTEM 454
S_NQ_SCHED 3
TSMSYS 3
WMSYS 242
XDB 682

27 rows selected.
Strange.

I created a user, UNIT_TESTING, to house the data and fed it to SQL Developer. Did I create the user?
CJUSTICE@TESTING>SELECT COUNT(*) 
FROM dba_users
WHERE username = 'UNIT_TESTING';

COUNT(*)
----------
1
Yeah.

I check DBA_OBJECTS using UNIT_TESTING as the predicate:
CJUSTICE@TESTING>SELECT * FROM dba_objects WHERE owner = 'UNIT_TESTING';

no rows selected
Really?
CJUSTICE@TESTING>SELECT table_name
2 FROM dba_tables
3 WHERE owner = 'UNIT_TESTING';

TABLE_NAME
------------------------------
UT_LIB_TEARDOWNS
UT_LOOKUP_CATEGORIES
UT_LOOKUP_DATATYPES
UT_LOOKUP_VALUES
UT_METADATA
UT_TEST
UT_TEST_ARGUMENTS
UT_TEST_IMPL
UT_VALIDATIONS
UT_TEST_IMPL_ARGUMENTS
UT_LIB_STARTUPS
UT_LIB_VALIDATIONS
UT_LIB_DYN_QUERIES
UT_SUITE
UT_SUITE_TEST
UT_TEST_IMPL_VAL_RESULTS
UT_TEST_IMPL_ARG_RESULTS
UT_TEST_IMPL_RESULTS
UT_TEST_COVERAGE_STATS
UT_TEST_RESULTS
UT_SUITE_RESULTS
UT_SUITE_TEST_RESULTS

22 rows selected.
WTF?

Does this mean that my data dictionary is corrupted? This is a sandbox so it is very well possible...just never seen this kind of thing before.

Update 03/29/2010
I figured it out, user error...explanation is here.

No comments: