1

For example, are there any differences between:

SCOTT@ORCL> create index a_idx on a(x);

and

SYS@ORCL> create index scott.a.idx on scott.a(x);
1

2 Answers 2

1

Apart form all the DOs and DONTs about using SYS in this case is that "if you connect as sysdba, your schema name will appear to be SYS".

Phil's answer demonstrates it. A nice quote by Thomas Kyte on a similar question -

In general, unless the documentation tells you, you will NEVER LOG IN as sys or system, they are our internal data dictionary accounts and not for your use. You will be best served by forgetting they exist.

sysdba and sysoper are ROLES - they are not users, not schemas. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.

In real life, you hardly EVER need sysdba - typically only during an upgrade or patch.

We keep saying the following so many times -

  • Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
  • SYS/SYSDBA is special
  • SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
  • SYS/SYSDBA does not act like any other user
  • When you use SYS/SYSDBA Oracle deactivates some code path and activates others
  • Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user. Use SYS/SYSDBA ONLY for something that can't be done by someone else.

You might also like to read this.

-1

I'll start my answer with this:

As a general rule: Never, ever, create objects as the user SYS/SYSTEM (or any other "internal" Oracle user) unless directed to do so by Oracle support.

A simple test case shows that there's a big difference, due to ownership.

As SCOTT:

SQL> create table a ( b number );

Table created.

SQL> create index a_idx on scott.a ( b ) ;

Index created.

SQL> show user
USER is "SCOTT"
SQL> select owner from all_indexes where table_name = 'A';

OWNER
------------------------------
SCOTT

SQL> drop index a_idx;

Index dropped.

As SYS:

SQL> conn / as sysdba
Connected.
SQL> create index a_idx on scott.a ( b ) ;

Index created.

SQL> show user
USER is "SYS"
SQL> select owner from all_indexes where table_name = 'A';

OWNER
------------------------------
SYS

SQL>

This obviously causes problems due to ownership etc, as well as the data residing in a tablespace that you don't want user objects belonging in (from above example, because we didn't specify a TABLESPACE clause:

SQL> select owner, tablespace_name from all_indexes where table_name = 'A';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SYS                            SYSTEM

SQL>
4
  • Although not going to disagree what you put here, note that the OP DID indicate a schema name in his create index .. so NO, in his case, the index does create under SCOTT, not SYS. No argument on anything else though ;)
    – Ditto
    Commented Feb 9, 2015 at 14:01
  • @Ditto it's not possible to qualify with a SCHEMA name, as the OP suggests: SQL> create index scott.a.idx on scott.a(x); create index scott.a.idx on scott.a(x) * ERROR at line 1: ORA-00969: missing ON keyword SQL>
    – Philᵀᴹ
    Commented Feb 9, 2015 at 16:13
  • Sure it is .. just did in Oracle 11. under schema "test": create table junk (id number); under "SYS": create index test.junk_idx on test.junk(id); index shows as "owned" by "test". In your example, you didn't prefix the index name with the user, so it creates under SYS.
    – Ditto
    Commented Feb 9, 2015 at 16:19
  • Hmm, I see, probably the formatting style used ... I'm just using "schema.index" .. not the "schema.table.index" OP used and you used above. That's probably throwing it for a loop ;) SQL> create index test.junk_idx on test.junk(id); Index created. I read that as a typo though, since his first example was "a_idx" .. not "a.idx" ;)
    – Ditto
    Commented Feb 9, 2015 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.