Category: SQL scripts

Problem: ~~~~~~~~ When you upgrade the database from Oracle V7 to V8, and then create a profile which limits password_life_time, existing users cannot login to the database. New users can login without a problem. To reproduce this problem: 1. Create a test user (test1) in a V7 database: grant connect, resource to test1 identified by…

Read More “ORA-1841 Error Connecting to Upgraded Database After Set PASSWORD_LIFE_TIME” »

set lines 120 pages 2000 accept pvm prompt “Enter The String You Want to Search -> ” select distinct owner, name, type from dba_source where instr(upper(text), upper(‘&pvm’)) != 0 ; set serveroutput on DECLARE CURSOR cur_triggers IS SELECT owner, trigger_name, table_name, trigger_body FROM dba_triggers; v_position INTEGER; BEGIN DBMS_OUTPUT.ENABLE(NULL); DBMS_OUTPUT.PUT_LINE(‘OWNER TABLE NAME TRIGGER NAME ‘); DBMS_OUTPUT.PUT_LINE(‘===================================================================================================’)…

Read More “find_string_in_database.sql” »

Problem Description: ~~~~~~~~~~~~~~~~~~~~ How do you make all trace files that Oracle creates readable to owner, group, and world or public? Any “*.trc” files created by Oracle have permissions of 640, with owner of Oracle and group DBA. For example: -rw-r—– oracle dba Setting umask in the users login script (.cshrc or .profile) does not…

Read More “How to Make Trace Files Created by Oracle Readable by All Users ?” »

1. Set in INIT.ORA the parameter resource_limit = true and restart Database. Since Oracle 9iR2 (9.2.0.1) you can perform this without restarting Database by issuing as SYS: alter system set resource_limit=true; 2. Connect to Database as SYSTEM or SYS and create the following Profile: create profile single_user limit sessions_per_user 1; The other parameters should be…

Read More “How To Limit The Access To The Database So That Only One User Per Schema Are Connected (One Concurrent User Per Schema)” »

Problem Description ——————- The DBA needs to know which values are assigned to limits set to DEFAULT for a given profile. Querying DBA_PROFILES does not display the actual values but rather lists DEFAULT for those LIMITs. Solution Description ——————– From the SQL*Plus prompt, type: SQL> select * from dba_profiles where profile = ‘DEFAULT’; PROFILE RESOURCE_NAME…

Read More “Default User Profile” »

The REFERENCES object privilege allows a user to describe a table for which he has been granted the privilege on.

Normal triggering events doesn’t succeed when corresponding triggers fails. But there is EXCEPTION for STARTUP events: STARTUP events succeed even if the trigger fails. There are also exceptions for SHUTDOWN events and for LOGON events if you login as SYSTEM

Advanced Security Options. On a UNIX platform run the ‘adapters’ command at the shell. If you have ASO installed you will see something like, Installed Oracle Advanced Security option/Security products are: RC4 40-bit encryption algorithm RC4 56-bit encryption algorithm DES40 40-bit encryption algorithm DES 56-bit encryption algorithm MD5 crypto-checksumming algorithm On Windows you will need…

Read More “How can I tell if ASO is installed ?” »

Help In this Document Purpose Scope and Application All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault 1) Alerts 2) System Privileges 3) Object Privileges 4) Users and Roles 5) User and Tablespace Quotas 6) Profiles and Resource Limits 7) Password Management 8) Connect Internal and Password Files…

Read More “All About oracle password and security from metalink” »

fix: 1) Log on as SYSDBA 2) Create database link between source and target database 3) Create procedure which selects usernames,passwords from DBA_USERS in source database and inserts usernames,passwords into target database Sample SQL-statements source database: SQL> select password from dba_users where username=’SCOTT’; USERNAME PASSWORD —————————— —————— SCOTT F894844C34402B67 target database: Sqlplus> Create user SCOTT…

Read More “How To Transfer Passwords Between Databases (ref note: 199582.1)” »