Sunday, November 17, 2013

Grants given to objects through Roles for a Schema & without Roles (Direct)

Hi Folks,

 

The following queries are very much useful to get the list of Grants given to Roles for a particular schema objects & to List the direct grants given to users.

 

Grants given to objects through Roles for a Schema

**************************************************

SELECT TP.OWNER,TP.GRANTEE,RP.GRANTEE, TP.TABLE_NAME, TP.PRIVILEGE FROM DBA_TAB_PRIVS TP, DBA_ROLE_PRIVS RP WHERE TP.GRANTEE = RP.GRANTED_ROLE AND TP.OWNER='Schema Name'

ORDER BY 1,2,3 

 

 

Individual Grants given to objects without using Roles:

********************************************************

SELECT TP.OWNER,TP.GRANTEE,TP.TABLE_NAME, TP.PRIVILEGE FROM dba_tab_privs TP

where TP.OWNER='<Schema Name>' AND GRANTEE NOT IN (Select ROLE from DBA_ROLES) ORDER BY 2,3,4

 

Thanks & Regards

Fauzi

 

Oracle: History of SQL Statements in Oracle 11g

Hello,

 

The following sql query is very much help full to get the history of sql queries run on the server.

 

SQL:

select a.USER_ID, a.SESSION_ID,b.sql_text from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b where a.sql_id=b.sql_id

 

Thanks & Regards

Fauzi

 

 

Monday, November 11, 2013

Oralce Query to Get Last Sunday or Frieday of the month

The following query is very much useful:

 

 

SELECT next_day(last_day(sysdate)-7,'Friday') from dual

 

SELECT next_day(last_day(sysdate)-7,'Sunday') from dual

 

Thanks & Regards

Fauzi

 

Thursday, May 2, 2013

Oracle: Steps to recover when Redo Files are Lost


RCAT>>select name from v$database;

NAME
---------
RCAT

RCAT>>alter system set db_recovery_file_dist='+DAT' scope=both;
alter system set db_recovery_file_dist='+DAT' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dist='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dist='+DATA' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dest='+DATA' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest_size=10M scope=both
RCAT>>/

System altered.

RCAT>>/ed
SP2-0042: unknown command "/ed" - rest of line ignored.
RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DAT' scope=both
RCAT>>/

System altered.

RCAT>>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
RCAT>>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
RCAT>>startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
RCAT>>shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
RCAT>>startuo
SP2-0042: unknown command "startuo" - rest of line ignored.
RCAT>>startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1896
Session ID: 1 Serial number: 5


RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>
C:\Users\Administrator>
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:03 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select * from v$instance
  2  ;
select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SQL> exit
Disconnected

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:42 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-03113: end-of-file on communication channel
Process ID: 3208
Session ID: 1 Serial number: 3


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:59 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
SQL> alter database mount ;

Database altered.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL>





--
Fauzi
+965-97324246

  


Wednesday, May 1, 2013

Oracle : Recovery Steps when control file is lost. If backup was done


SQL> startup;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> select status fron v$instance;
select status fron v$instance
                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> ed
Wrote file afiedt.buf

  1* select status from v$instance
SQL> /

STATUS
------------
STARTED

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      F:\RCAT\CONTROL01.CTL, F:\RCAT
                                                 \CONTROL02.CTL
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>
SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\RCAT\SYSTEM01.DBF'


SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo01.log
ORA-00328: archived log ends at change 950490, need later change 967649
ORA-00334: archived log: 'F:\RCAT\REDO01.LOG'


SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
INACTIVE                947455 01-MAY-13       950491 01-MAY-13

         3          1          3   52428800        512          1 NO
CURRENT                 959675 01-MAY-13   2.8147E+14

         2          1          2   52428800        512          1 NO
INACTIVE                950491 01-MAY-13       959675 01-MAY-13


SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
CURRENT                 972032 02-MAY-13   2.8147E+14

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0


SQL> set sqlprompt RCAT>>
RCAT>>
--

Fauzi
+965-97324246

  


Oracle: To create spfile copy

From SP file:
 
SYS>>create pfile='f:\oracpara.ora' from spfile;
File created.
 
From Memory:
SYS>>create pfile='f:\oracpara.ora' from memory;
File created.

Oracle: To run a structural change sql statement from RMAN:

To run a structural change sql statement from RMAN:
 
RMAN> sql 'alter database enable block change tracking'
2> ;


Thursday, April 25, 2013

Oracle : SQL to find in Top Activity in EM

Hello,
 
The following SQL from default schema is useful to create TOP activity in Server,

select * from sh.sales,sh.customers, sh.channels

Note: Only for testing server :)
 
It was helpful to find it in Enterprise Manager.
 

 

Tuesday, April 23, 2013

Oracle: To Know Current System Change No.

 To Know Current System Change No. :
 
 select Current_scn from v$database;


Monday, April 22, 2013

Oracle: How to lock a table with select command

How to lock a table with select command:
 
SQL: select * from scott.dept for update;

Remember to Rollback when you want to unlock later :)

Oracle: Best Practice for TABLES & INDEXES Tablespace

Tip of the day: Best Practice (for realtime):

Create tablespaces for TABLES, and separate tablespaces

for INDEXES for better performance and management.

Tuesday, February 12, 2013

Oracle 8i : DB Links script with credentials

The Following script is very much helpful to generate script for DB Links created in the server.

 

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)

||'connect to ' || L.USERID || ' identified by '

||L.PASSWORD||' using ''' || L.host || ''''

||chr(10)||';' TEXT

FROM sys.link$ L, sys.user$ U

WHERE L.OWNER# = U.USER#;