Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • USE_NL and INDEX hints example Oracle
  • Passing from Unix to PLSQL using bind variables Linux/Unix
  • find_idle_cpu.sql Oracle
  • TRUNCATE Privs Oracle
  • SQL Tracker by SID sqltrackerbysid.sql Oracle
  • find_pk.sql /* Find Primary Key */ Oracle
  • Check Oracle installed products using one command Oracle
  • temp_use.sql diplays usage of temp ts Oracle
  • currwait.sql Oracle
  • Monitor Long Running Job Oracle
  • Goldengate Tutorial Oracle
  • Search and Replace vi editor command. Linux/Unix
  • rm_backup_arch_file.ksh Linux/Unix
  • TRUNCATE table and disabling referential constraints. Oracle
  • check_copy_progress.sh Linux/Unix

My Test Case On 21-OCT-2005

Posted on 21-Oct-2005 By Admin No Comments on My Test Case On 21-OCT-2005

(*) if you grant any role to grantee while grantee is connected to database. He cannot use that role immediately. To use it immediately he needs to execute “Set role role_name” in his session. This will enable that role for him or he needs to logoff and login again.

(*) If you have given any object level grant to grantee, grantee need to execute that as fully qualified path for accessing that object. To access object with simple name you need to create private synonym in grantee’s schema.

(*) If a table is aletered,

(1) its associated trigger & views (dba_dependencies) become invalid.

(2) If some other user are accessing this then trigger and views are compiled automatically and executed if they compiled successfully.

(3) Grants remain intact while this process.

(*) If a table is renamed then

(1) associated trigger, views become invalid.

(2) synonyms that are pointing to this table from other user also unable to resolve target object name and gives ORA-00980 “synonym trnaslation is no longer valid”.

(3) Grantee has all object level grants intact on this new object and this renamed object can be still access with fully qualified name (with schemaname). so user can user those grant. If grantee/owner is able to execute some DML that initiate trigger then trigger will be compiled and validated.

(4) The only problem is view. For views, you need to modify its definition manually and compile. You donot need to re-grant again.

(*) if table is droppped then

(1) Associated trigger also dropped. Views become invalid.

(2) Synonyms that are pointing to this table from other user also unable to resolve target object name and gives ORA-00980 “synonym trnaslation is no longer valid”.

Oracle, SQL scripts

Post navigation

Previous Post: TABLE SIZING WITH DB_BLOCK ARCHITECTURE Reference : Metalink note : 10640.1
Next Post: USER_TABLES.Freelists

Related Posts

  • Good Doc 28-JUN-2006 Oracle
  • TNSNAMES entries details Oracle
  • How to find password change date for user Oracle
  • good note for shared pool tunnig Oracle
  • Specify the Rollback segment to use in Transaction Oracle
  • Explain Plan Doesn’T Change For Sql After New Statistics Generated Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Finding last recovered file on DR and remove all chanracters before any “/” Linux/Unix
  • Wait.sql Oracle
  • currwait.sql Oracle
  • Oracle Material from OTN Oracle
  • .profile Linux/Unix
  • DBMS_UTILITY PACKAGE Oracle
  • create database link syntax Oracle
  • Drop specific SQL plan baseline – spm Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme