Search Text in Varchar2 Column

prodb]$ cat find_value.sql

SET SERVEROUTPUT ON SIZE 100000

DECLARE

  match_count INTEGER;

-- Type the owner of the tables you are looking at

  v_owner VARCHAR2(255) :='MATRIX';

-- Type the data type you are look at (in CAPITAL)

-- VARCHAR2, NUMBER, etc.

  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at

  v_search_string VARCHAR2(4000) :='GCSE';

BEGIN

  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type  ) LOOP

-- dbms_output.put_line(t.table_name);

    EXECUTE IMMEDIATE

    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'

    INTO match_count

    USING v_search_string;

  --  dbms_output.put_line(t.table_name);

    IF match_count > 0 THEN

      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );

    END IF;

  END LOOP;

END;

/

--AnnScript

Block Developers from Different TOOLS

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod

  AFTER LOGON ON DATABASE

DECLARE

  v_prog sys.v_$session.program%TYPE;

BEGIN

  SELECT program INTO v_prog

    FROM sys.v_$session

  WHERE  audsid = USERENV('SESSIONID')

    AND  audsid != 0  -- Don't Check SYS Connections

    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's

  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad

     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator

     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer

     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects

     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in

  THEN

     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');

  END IF;

END;

/

SHOW ERRORS

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

UTL_HTTP && Network ACL 11g

SELECT host, acl,

DECODE(

DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'VOLUME', 'resolve'),

1, 'GRANTED', 0, 'DENIED', NULL) privilege

FROM dba_network_acls;

SELECT host, lower_port, upper_port, acl,

DECODE(

DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'VOLUME', 'connect'),

1, 'GRANTED', 0, 'DENIED', null) privilege

FROM dba_network_acls;

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

SELECT * FROM DBA_NETWORK_ACLS;

begin

dbms_network_acl_admin.assign_acl( 'volume_utl_http.xml', '*.com');

end;

begin

dbms_network_acl_admin.create_acl (

acl => 'volume_utl_http.xml',

description => 'HTTP Access',

principal => 'VOLUME',

is_grant => TRUE,

privilege => 'connect',

start_date => null,

end_date => null

);

dbms_network_acl_admin.add_privilege (

acl => 'volume_utl_http.xml',

principal => 'VOLUME',

is_grant => TRUE,

privilege => 'resolve',

start_date => null,

end_date => null

);

dbms_network_acl_admin.assign_acl( 'volume_utl_http.xml', '*.com');

commit;

end;

Simple Rollup

SELECT

--to display "Sub Total" column with "Total" at the End

DECODE(grouping(TRUNC(date_created,'MM')),0,NULL,'Total') "Sub Total" ,

TO_CHAR(TRUNC(date_created,'MM'),'MONTH-YYYY') "Months" ,

COUNT(*) "This Month Tweets"

FROM twitter

GROUP BY rollup (TRUNC(date_created,'MM') )

ORDER BY TRUNC(date_created,'MM');

Oracle SQL STRIP HTML Tags

You can strip all HTML Tags from a text string using:

SELECT REGEXP_REPLACE('string or column containing HTML goes here','<[^>]+>','') FROM DUAL

-- CREATE FUNCTION

CREATE OR REPLACE

FUNCTION "STRIPHTML" (strArg IN CLOB)

RETURN CLOB IS

BEGIN

RETURN regexp_replace(strArg, '<[^>]+>', NULL);

END STRIPHTML;

ORA-06553: PLS-801: internal error [56319]

Few things to do

1) Running utlirp.sql (this is going to invalidate most (for me around 80,000 objects) and then recompiles them again) Took a helluva time.. but got it sorted out. in the end.

2) Had to run utlirp.sql when database is in migration mode. (remember I ported the db from 32 bit to 64 bit).

I am sure that this "IS A" solutions as now, I am getting the same error with 32bit version of oracle software. However with 64 bit I am fine. now. before this the error was with 64 bit and 32 bit was clean.

-------------- Other Scenario---------------------------------------------------------

Worked great, I had to restore a 32 bit database on a 64 bit system. After restore/recover operations with RMAN I did the following.

1) startup upgrade;
2) @?/rdbms/admin/utlirp.sql
3) shutdown immediate;
4) startup;

------------Other Scenario-----------------------------------------------------

Received the "ORA-06553: PLS-801: internal error [56319]" error after cloning a db, while trying to drop a user.

SQL> drop user QCSITEADMIN_DB0 cascade;
drop user QCSITEADMIN_DB0 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56319]

and it got resolved after executing "utlirp.sql".

---------------------------------------------------My scenario -----------------

SYS @ bsqrac >>conn volume/trace

ERROR:

ORA-06553: PLS-801: internal error [56327]

Error accessing package DBMS_APPLICATION_INFO

Connected.

VOLUME @ bsqrac >>

VOLUME @ bsqrac >>@utlirp.sql

VOLUME @ bsqrac >>WHENEVER SQLERROR EXIT;

VOLUME @ bsqrac >>DOC

DOC>#######################################################################

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if there the database was not opened in UPGRADE mode

DOC>

DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and

DOC> re-execute utlirp.sql

DOC>#######################################################################

DOC>#######################################################################

DOC>#

VOLUME @ bsqrac >>SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

2 WHERE status != 'OPEN MIGRATE';

SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

*

ERROR at line 1:

ORA-00942: table or view does not exist

Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production

With the Automatic Storage Management option

[oracle@VOL-ORATEST admin]$ s

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 24 12:35:48 2011

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

Connected to:

Oracle Database 11g Release 11.2.0.1.0 - Production

With the Automatic Storage Management option

SYS @ bsqrac >>shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS @ bsqrac >>startup upgrade

ORACLE instance started.

Total System Global Area 246910976 bytes

Fixed Size 1335752 bytes

Variable Size 192941624 bytes

Database Buffers 50331648 bytes

Redo Buffers 2301952 bytes

Database mounted.

Database opened.

SYS @ bsqrac >>@utlirp.sql

......................

.........................

............................

PL/SQL procedure successfully completed.

SYS @ bsqrac >>

SYS @ bsqrac >>DOC

DOC>#######################################################################

DOC>#######################################################################

DOC> utlirp.sql completed successfully. All PL/SQL objects in the

DOC> database have been invalidated.

DOC>

DOC> Shut down and restart the database in normal mode and run utlrp.sql to

DOC> recompile invalid objects.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SYS @ bsqrac >>

SYS @ bsqrac >>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS @ bsqrac >>startup

ORACLE instance started.

Total System Global Area 246910976 bytes

Fixed Size 1335752 bytes

Variable Size 192941624 bytes

Database Buffers 50331648 bytes

Redo Buffers 2301952 bytes

Database mounted.

Database opened.

SYS @ bsqrac >>

SYS @ bsqrac >>connect volumexx/xxxxx

Connected.

VOLUME @ bsqrac >>

Solution of ORA-28002: the password will expire within 5 days

Error Description:
-----------------------------------
Whenever a user try to connect to database it raise ORA-28002: error.

-bash-3.00$ sqlplus Hemesh/a

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 7 23:58:46 2008

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

ERROR:
ORA-28002: the password will expire within 10 days


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Cause of The Problem:
-----------------------------------
Lets know the user profile.
SQL> select profile from dba_users where username='HEMESH';

PROFILE
------------------------------
DEFAULT

Now lets know the resource settings 'PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME' of default profile.

SQL> select LIMIT, RESOURCE_NAME from dba_profiles where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME') and PROFILE=(select profile from dba_users where username='HEMESH');

LIMIT RESOURCE_NAME
---------------------------------------- --------------------------------
60 PASSWORD_LIFE_TIME
1800 PASSWORD_REUSE_TIME
UNLIMITED PASSWORD_REUSE_MAX
10 PASSWORD_GRACE_TIME

The resource PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused.

In this case our interested resource is PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME.

The resource of Default profile PASSWORD_LIFE_TIME specify the number of days the same password can be used for authentication.

The resource PASSWORD_GRACE_TIME specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

Here in the profile of HEMESH user the value of PASSWORD_GRACE_TIME is set to 10. So it just arises a warning ORA-28002 but still allow users to logon to database. The password will expire if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.

One may interpret wrongly of parameter PASSWORD_LIFE_TIME with account creation time. Actually the PASSWORD_LIFE_TIME limit of a profile is measured from the last time an account's password was changed or the account creation time if the password has never been changed.

The account creation time and password change time can be seen from USER$.CTIME and USER$.PTIME respectively. Like,
SQL> select ctime, ptime from sys.user$ where name='HEMESH';

CTIME PTIME
--------- ---------
08-MAY-08 08-MAY-08

You can also get the account creation time from dba_users view.
SQL> SELECT CREATED FROM DBA_USERS WHERE USERNAME = 'HEMESH';

CREATED
---------
08-MAY-08

Now let's look current time which is 08-JUL-08
SQL> select sysdate from dual;

SYSDATE
---------
08-JUL-08

So between the password change time and current time there it is passed 60 which is equal to PASSWORD_LIFE_TIME. Now the setting of PASSWORD_GRACE_TIME to 10 allow the user HEMESH to connect to database 10 days more but will issue a warning.

Solution of The Problem:
--------------------------------------
A)Change the user password.
------------------------------------
If you just want to avoid the error temporary then change the user password.
SQL> conn Hemesh/a
ERROR:
ORA-28002: the password will expire within 10 days
SQL> password
Changing password for HEMESH
Old password:
New password:
Retype new password:
Password changed
SQL> conn Hemesh/a!12
Connected.

Now you can see the change time by .
SQL> select ctime, ptime from sys.user$ where name='HEMESH';

CTIME PTIME
--------- ---------
08-MAY-08 08-JUL-08

This is a temporary solution. After 60 days the user will again see the warning message.

B)Change PASSWORD_LIFE_TIME resource of profile assigned to user.
----------------------------------------------------------------------------------------

The permanent solution is to change PASSWORD_LIFE_TIME resource of profile DEFAULT which is assigned to user HEMESH.

SQL> select profile from dba_users where username='HEMESH';

PROFILE
------------------------------
DEFAULT
If you make it unlimited then user never will see above error. Like,
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL > select * from dba_users where username='HEMESH';

ACCOUNT_STATUS = Expired(GRACED)

SQL> ALTER USER HEMESHidentified by newpassworld;

user altered.

SQL > select * from dba_users where username='HEMESH';

ACCOUNT_STATUS = OPEN

******************* Open an Expired Account***********************************

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS

--------------------------------

EXPIRED

Up to 10g ) SQL> select password from dba_users where username='THOMAS';

from 11g) SQL>select password from SYS.user$ where name ='THOMAS';

PASSWORD

-----------------------------

063D8DE086C2860D

SQL> alter user thomas identified by values '063D8DE086C2860D';

User altered.

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS

--------------------------------

OPEN

SQL> conn thomas/thomas

Connected.

Word Count Function

Word Count Function :
create or replace
FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
words PLS_INTEGER := 0;
len PLS_INTEGER := NVL(LENGTH(str),0);
inside_a_word BOOLEAN;
BEGIN
FOR i IN 1..len + 1
LOOP
IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
THEN
IF inside_a_word
THEN
words := words + 1;
inside_a_word := FALSE;
END IF;
ELSE
inside_a_word := TRUE;
END IF;
END LOOP;
RETURN words;
END;

Date Arithmetic

Action		Interval Time
Execute daily		'SYSDATE + 1'
Execute every 4 hours		'SYSDATE + 4/24'
Execute every 10 minutes		'SYSDATE + 10/1440'
Execute every 30 seconds		'SYSDATE + 30/86400'
Execute every 7 days		'SYSDATE + 7'
Do no re-execute and remove job		NULL

NOTE: Remember that job intervals expressed as shown in the previous table do not guarantee that the next execution will happen at a specific day or time, only that the spacing between executions will be at least that specified. For instance, if a job is first executed at 12:00 p.m. with in interval of 'SYSTEM + 1', it will be scheduled to execute the next day at 12:00 p.m. However, if a user executes the job manually at 4:00 p.m. the next day using DBMS_JOB.RUN, then it will be rescheduled for execution at 4:00 p.m. the next day. Another possibility is that the database is down or the job queue so busy that the job cannot be executed exactly at the time scheduled. In this case, the job will run as soon as it can, but the execution time will have migrated away from the original submission time due to the later execution. This "drift" in next execution times is characteristic of jobs with simple interval expressions.




Jobs with type 2 execution requirements involve more complex interval date expressions, as see in the following table.

Action	Interval Time
Every day at 12:00 midnight	TRUNC(SYSDATE + 1)
Every day at 8:00 p.m.	TRUNC(SYSDATE + 1) + 20/24
Every Tuesday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 12/24
First day of the month at midnight	TRUNC(LAST_DAY(SYSDATE) + 1)
Last day of the quarter at 11:00 p.m.	TRUNC(ADD_MONTH(SYSDATE + 2/24,3),'Q') - 1/24
Every Monday, Wednesday and Friday at 9:00 p.m.	TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 21/24

WHERE RD.DATE_CREATED >= (TRUNC(ADD_MONTHS(SYSDATE,-1),'MM') + 19) AND RD.DATE_CREATED <= (TRUNC(SYSDATE ,'MM') + 19) -- Between 20th of current and previous month


SELECT
TO_CHAR((NEXT_DAY(TRUNC(SYSDATE-14), 'MONDAY') + 0 ), 'DD-MON-YYYY HH24:MI:SS') AS "Last2L Monday" ,
TO_CHAR((NEXT_DAY(TRUNC(SYSDATE-7), 'MONDAY') + 0 ), 'DD-MON-YYYY HH24:MI:SS') AS "Last Monday"
FROM DUAL; --- between last to Mondays

Create Read only user for a Schema

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.

I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.
Let's start by creating PROD user.
SQL> CREATE USER PROD IDENTIFIED BY P;
User created.

SQL> GRANT DBA TO PROD;
Grant succeeded.

SQL> CONN PROD/P;
Connected.

SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.

SQL> INSERT INTO PROD_TAB1 VALUES(1,2);
1 row created.

SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);
Table created.

SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1
BEGIN
INSERT INTO PROD_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;
View created.


Method 1: Granting Privilege Manually

Step 1: Create devels user
SQL> CREATE USER DEVELS IDENTIFIED BY D;
User created.

Step 2: Grant only select session and create synonym privilege to devels user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;
Grant succeeded.

Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_prod.sql
@@/oradata2/select_only_script.sql
SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.

Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql

SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';
SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';

Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql

GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;
GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;

Step 6:
Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.
SQL> @/oradata2/select_only_to_prod.sql

Step 7:
Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.
To create synonym do the following,

SQL>CONN DEVELS/D;

SQL>host vi /oradata2/script_synonym.sql

SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_prod.sql
@@/oradata2/synonym_script.sql
SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_prod.sql

Step 8: At this stage you have completed your job. Log on as devels schema and see,
SQL> select * from prod_tab1;
1 2

SQL> show user
USER is "DEVELS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into prod_tab1 values(4,3);
insert into prod_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code
This is script for table :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='PROD';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/

This is the script for grant select permission for views.

DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='PROD';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

To create synonym on prod schema,
Log on as devels and execute the following procedure.

SQL>CONN DEVELS/D
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='PROD'
UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

Method 3: Writing a Trigger


After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.

create or replace trigger log_on_after_devels
after logon ON DEVELS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';
END;
/


Related Documents

Drop User in Oracle

ORA-01940: Cannot drop a user that is currently connected

Create user in oracle

A user can do work in his schema with only Create Session Privilege.