Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 129,480
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Export failed when trying using SYS user. * (ORA-28009: connection as SYS should be as SYSDBA or SYSOPER)

Posted by FatDBA on December 23, 2012

[oracle@localhost ~]$ expdp sys/oracle90 directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true

Errors:
UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

SQL> show parameter dictionary

NAME                                 TYPE        VALUE
———————————— ———– ——————————
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

 

With this parameter in False, it will not allow accessing anyone dictionary views, tables objects in SYS schema without SYSDBA/SYSOPER roles. O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in “any schema” do not allow access to objects in the SYS schema.

 

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1337632 bytes
Variable Size             406849248 bytes
Database Buffers          109051904 bytes
Redo Buffers                5869568 bytes
Database mounted.
Database opened.

SQL> show parameter dictionary

NAME                                 TYPE        VALUE
———————————— ———– ——————————
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

[oracle@localhost ~]$ expdp sys/oracle90 directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true

Export: Release 11.2.0.1.0 – Production on Sun Dec 23 12:12:29 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ORA-28002: the password will expire within 7 days            //   **** This error points to change password for user the table belongs to ‘Larry’****

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TABLE_01″:  sys/******** directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “LARRY”.”EMP”                               8.046 KB       2 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/tmp/emp.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at 12:12:47

 

Or —->>>>
Export it with SYSDBA role.

[oracle@localhost ~]$ expdp \’sys/oracle90 as sysdba\’ directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true

Export: Release 11.2.0.1.0 – Production on Sun Dec 23 16:42:15 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TABLE_01″:  “sys/******** AS SYSDBA” directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true
Estimate in progress using BLOCKS method…

Advertisements

2 Responses to “Export failed when trying using SYS user. * (ORA-28009: connection as SYS should be as SYSDBA or SYSOPER)”

  1. santhosh said

    Thanks a lot which is working

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: