Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vivek Reddy.

Asked: February 10, 2016 - 8:39 pm UTC

Last updated: February 12, 2016 - 10:57 pm UTC

Version: 11.2.0.1

Viewed 1000+ times

You Asked

Hi Tom,

I am using Oracle RDS instance on Amazon web service and ORACLE SQL DEVELOPER – 4.0.3.16. to connect RDS Instance.

In my oracle schema i have SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12% tables. All these tables are temp tables.

I didn't do any third party migration. I just used third party jdbc driver (jtds-1.2) in ORACLE SQL Developer to connect SQL Server for testing purpose.

If i export my schema, the export log file didn't show the tables mentioned above. But, after import the oracle schema shows all tables including the temp tables (SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%).

I want to know, why these tables are created in my schema when i didn't migrate data from SQL Server or other third party database.
Why they are temp tables and why most of the tables are empty.

Does it impact my oracle schema, if i delete temp tables(SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%)?

and Connor said...

I chatted with Tim Hall at oracle-base.com who has used RDS. He said this:


Hi.

Just fired up a clean instance on RDS for Oracle.

Those objects do not exist by default.

SQL> select count(*) from dba_objects where object_name in ('SS2K5%',
'SYB12%', 'STAGE_SS2K5%', 'STAGE_TERADATA%', 'STAGE_SYB12%');

COUNT(*)
----------
0

1 row selected.

SQL>

RDS instances do have some objects present by default, which are owned by the RDSADMIN user.

SQL> select owner, count(*) from dba_objects group by owner order by 1;

OWNER COUNT(*)
------------------------------ ----------
APPQOSSYS 5
CTXSYS 389
DBSNMP 55
OUTLN 10
PUBLIC 3408
RDSADMIN 28
SYS 9562
SYSTEM 609

8 rows selected.

SQL>

You don't get access to SYS or SYSTEM, so you have to call the rdsadmin.rdsadmin_util package to do some things.

SQL> desc rdsadmin.rdsadmin_util
PROCEDURE ADD_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BYTES BINARY_INTEGER IN DEFAULT
PROCEDURE ADD_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SIZE VARCHAR2 IN
PROCEDURE ALTER_DB_TIME_ZONE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_NEW_TZ VARCHAR2 IN
PROCEDURE ALTER_DEFAULT_TABLESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLESPACE_NAME VARCHAR2 IN
PROCEDURE ALTER_DEFAULT_TEMP_TABLESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLESPACE_NAME VARCHAR2 IN
PROCEDURE ALTER_SUPPLEMENTAL_LOGGING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ACTION VARCHAR2 IN
P_TYPE VARCHAR2 IN DEFAULT
PROCEDURE CHECKPOINT
PROCEDURE CREATE_DIRECTORY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DIRECTORY_NAME VARCHAR2 IN
PROCEDURE DISABLE_DISTR_RECOVERY
PROCEDURE DISCONNECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL NUMBER IN
METHOD VARCHAR2 IN DEFAULT
PROCEDURE DROP_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
GRP BINARY_INTEGER IN
PROCEDURE ENABLE_DISTR_RECOVERY
PROCEDURE FLUSH_BUFFER_CACHE
PROCEDURE FLUSH_SHARED_POOL
PROCEDURE FORCE_LOGGING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ENABLE BOOLEAN IN DEFAULT
PROCEDURE GRANT_APEX_ADMIN_ROLE
PROCEDURE GRANT_SYS_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_OBJ_NAME VARCHAR2 IN
P_GRANTEE VARCHAR2 IN
P_PRIVILEGE VARCHAR2 IN DEFAULT
PROCEDURE KILL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL NUMBER IN
METHOD VARCHAR2 IN DEFAULT
PROCEDURE RENAME_GLOBAL_NAME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_NEW_GLOBAL_NAME VARCHAR2 IN
PROCEDURE RESTRICTED_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ENABLE BOOLEAN IN DEFAULT
PROCEDURE REVOKE_SYS_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_OBJ_NAME VARCHAR2 IN
P_REVOKEE VARCHAR2 IN
P_PRIVILEGE VARCHAR2 IN DEFAULT
PROCEDURE SET_CONFIGURATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VALUE VARCHAR2 IN
PROCEDURE SHOW_CONFIGURATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN DEFAULT
PROCEDURE SWITCH_LOGFILE

SQL>


Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Third Party Tables

Vivek Reddy, February 12, 2016 - 1:55 pm UTC

Hi McDonald,

Thank you for the response.

When I created Oracle Schema, (SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%) objects do not exist. But now, (SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%) objects exist in Oracle Schema.

I would like to let you know that I have access to SYSTEM for RDS Instance and I didn't migrate any data from third-party databases.

Now, I am wondering how and when (SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%) objects are created.

I also want to know if i delete (SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%) objects how does it impact Oracle Schema.


Connor McDonald
February 12, 2016 - 10:57 pm UTC

I would suggest renaming them, leaving them for a while and see if things are ok. If something goes wrong, then rename them back.

Then drop them later.

You should be able to see *when* they were created by looking at xxx_OBJECTS.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library