ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

when you don't need as sysdba or as sysoper at connect time
=============================================================

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
============================================================



SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 14 21:32:22 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
SQL> conn sys/sys
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL> conn sys/sys as sysdba
Connected.
SQL>


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 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 171969532 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> conn sys/sys
Connected.
SQL>
SQL>
QL> shutdown immediate
RA-01031: insufficient privileges
QL>
QL>

note: if you not connect as sysdba or sysoper you can't shutdown
or startup the database.


Thats why
if O7_DICTIONARY_ACCESSIBILITY=FALSE
you can't select any Dictionary table without connecting as sysdba or as sysoper
then you need to grant privilige "grant select any dictionary to user"

IF O7_DICTIONARY_ACCESSIBILITY=FALSE you can't need this privilige.



There are two side effects of this parameter being set to FALSE:
-----------------------------------------------------------------

1) that "sys/password" will not work without sysoper/sysdba, is
not clearly documented anywhere. The fact that the O7_DICTIONARY_ACCESSIBILITY init.ora
parameter causes this is somewhat hard to track down -- but that is what is causing this.


2)Access to the "real" data dictionary owned by SYS will not be available to users, even
if they have the SELECT ANY TABLE privelege. These are not the data dictionary views
like ALL_OBJECTS but rather the base tables like SYS.OBJ$ that will be unaccessible.

List of Some Database

List of Some Database

Adabas

Adabas is one of the fastest databases in the world. Its high speed and legendary reliability make Adabas the ideal database server for large and mission-critical business applications that require sub-second response times for thousands of users even in peak times.

DB2

DB2 is IBM's offering to the highend database market. The latest version of DB2 (Universal Database) is ideal for OLTP, Data Warehousing, Decision Support and everything in between. It's well priced, extremely scalable and runs on virtually every platform out there from handhelds to mainframes.

Informix

IBM Informix® Dynamic Server (IDS) is a general-purpose database supported on a large range of platforms including AIX, Linux, Windows and Solaris. As well as being ideal for Online Transaction Processing, IDS also supports IBM Informix DataBlade modules to handle spatial, geodetic and time series data.

Microsoft SQL Server

SQL Server is Microsoft's entry to the database server market. SQL Server is very easy to manage and comes with a built in OLAP engine. It has good support for web enabled applications and other Microsft products. SQL Server only runs on the Windows platform.

MySQL

MySQL is the cross-platform open source database server software used extensively in web development and implementaion. It lacks many advanced features that are taken for granted in the enterprise systems, but these shortcomings are offset by ease of use and impressive speed.

Oracle

Oracle is probably the most popular enterprise database server at the moment. This is the server that powers eBay on the web and countless datawarehouses and VLDBs around the world. Oracle is suited for all applications from OLTP to Data Warehousing.

Pervasive.SQL

Pervasive.SQL is the latest offering from the company that originally produced Btrieve®. Pervasive.SQL comes with many advanced features and has excellent support for a multitude of client technologies. It is supported on Windows, Linux and is ideal for web enabled applications.

PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES It provides SQL92/SQL99 language support and other modern features. It is the most advanced open-source database available anywhere.

Sybase

Sybase is a powerful all-round database server designed to support the demanding requirements of Internet and traditional, mission-critical OLTP and DSS applications. Sybase is well priced and there is even a free version for Linux that can be used in a production environment. Also included in the product line-up is ASA, the mobile and embedded market leader, and IQ, the data warehousing solution.

Msql

Progress

Ingres

PC based Database Applications

Corel Paradox

The desktop database that comes with Corel's WordPerfect Office suite.

FileMaker

A powerfull and flexible database management system for intranet/internet publishing. There is also a server version and a palm version of FileMaker

Microsoft Access

The database that comes with Office Professional. Access can handle tens of thousands of rows with ease and has great connectivity support for traditional database servers.

Microsoft Excel

Excel is part of Microsoft's Office suite and interfaces with any ODBC compliant database. With the included VBA language, it's possible to build an entire database frontend using Excel. This is already true with Essbase, a leading OLAP database.

How to know the table/index uses information in oracle

How to know the table/index uses information
=======================================
1. by enabling AUDIT .

2. BY QUERY THE FOLLOWING

SELECT *
FROM v$segment_statistics
WHERE owner = 'USER_NAME'
AND object_name = 'TABLE_NAME'

select owner,object_name,sum((case when STATISTIC# = '0' then value end )) "logical reads"
,sum((case when STATISTIC# = '1' then value end )) "buffer busy waits"
,sum((case when STATISTIC# = '2' then value end )) "gc buffer busy"
,sum((case when STATISTIC# = '3' then value end )) "db block changes"
,sum((case when STATISTIC# = '4' then value end )) "physical reads"
,sum((case when STATISTIC# = '5' then value end )) "physical writes"
,sum((case when STATISTIC# = '6' then value end )) "physical reads direct"
,sum((case when STATISTIC# = '7' then value end )) "physical writes direct"
,sum((case when STATISTIC# = '8' then value end )) "null"
,sum((case when STATISTIC# = '9' then value end )) "gc cr blocks received"
,sum((case when STATISTIC# = '10' then value end )) "gc current blocks received"
,sum((case when STATISTIC# = '11' then value end )) "ITL waits"
,sum((case when STATISTIC# = '12' then value end )) "row lock waits"
,sum((case when STATISTIC# = '14' then value end )) "space used"
,sum((case when STATISTIC# = '15' then value end )) "space allocated"
,sum((case when STATISTIC# = '17' then value end )) "segment scans"
from v$segment_statistics
where owner='USER_NAME'
---and object_name='TABLE_NAME'
and object_type='TABLE'
group by owner,object_name
order by "space used" desc--object_name


3. OR ONLY FOR INSERT/UPDATE/DELETE

SELECT * FROM select * from USER_tab_modifications;

How to add a day/hour/minute/second to a date value

How to add a day/hour/minute/second to a date value
====================================================


SELECT SYSDATE "Now",
SYSDATE + 1 "Tomorow/ next day",
SYSDATE + 7 "Seven days from now",
SYSDATE + 1 / 24 "One hour from now",
SYSDATE + 3 / 24 "Three hours from now",
SYSDATE + 1 / 48 "A half hour from now",
SYSDATE + 10 / 1440 "10 minutes from now",
SYSDATE + 30 / 86400 "30 seconds from now ",
TRUNC (SYSDATE + 1) "Tomorrow at 12 midnight",
TRUNC (SYSDATE + 1) + 8 / 24 "Tomorrow at 8 AM",
NEXT_DAY (TRUNC (SYSDATE), 'MONDAY')+ 12 / 24 "Next Monday at 12:00 noon",
TRUNC (LAST_DAY (SYSDATE) + 1) "1 day of month at 12 midnight",
TRUNC (LEAST (NEXT_DAY (SYSDATE, 'MONDAY'),
NEXT_DAY (SYSDATE, 'WEDNESDAY'),
NEXT_DAY (SYSDATE, 'FRIDAY')))+9/24 "Next Mon,Wed or Friday at 9am"
FROM DUAL ;

Output:=
=========

Now	Tomorow/ next day	Seven days from now	One hour from now	Three hours from now
04-11-2010 13:37:03	04-12-2010 13:37:03	04-18-2010 13:37:03	04-11-2010 14:37:03	04-11-2010 16:37:03
A half hour from now	10 minutes from now	30 seconds from now	Tomorrow at 12 midnight	Tomorrow at 8 AM
04-11-2010 14:07:03	04-11-2010 13:47:03	04-11-2010 13:37:33	04-12-2010 00:00:00	04-12-2010 08:00:00
Next Monday at 12:00 noon	1 day of month at 12 midnight	Next Mon,Wed or Friday at 9am
04-12-2010 12:00:00	05-01-2010 00:00:00	04-12-2010 09:00:00