An easy way to generate a value string from the ssl is to use openssl
Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important.
The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A-
$ echo &SafePassw0rDABCDEFGHIJ\c& | openssl dgst -sha1
(stdin)= 47cce479ef3d776ccd9e0d0158842bb
With this hash, I can construct my value
SQL& create user testuser identified by values 'S:47CCE479EF3D776CCD9E0D48494A';
User created.
SQL& grant create
Grant succeeded.
SQL& conn testuser/SafePassw0rD
Connected.
If you prefer PL/SQL over shell, use DBMS_CRYPTO
SQL& exec dbms_output.put_line('S:'||dbms_crypto.hash(utl_raw.cast_to_raw('SafePassw0rDABCDEFGHIJ'),dbms_crypto.HASH_SH1)||utl_raw.cast_to_raw('ABCDEFGHIJ'))
S:47CCE479EF3D776CCD9E0D48494A
PL/SQL procedure successfully completed.
In 12c there is also a “T” String. According to the doc
The cryptographic hash function used for generating the 12C verifier is based on a de-optimized algorithm involving PBKDF2 and SHA-512.
Share this:Like this:Like Loading...
Most permissions issues are due to a missing role or privilege.
But in the following test case you need to revoke the right to get more privileges.
create table tt(x number);
create view v as select *
I’ve created a read-write role on a view. The owner of the role is the DBA, but the owner of the view is the application. Next release, the role may prevent an application upgrade
SQL& create or replace view v as select *
ORA-01720: grant option does not exist for 'SYS.DUAL'
Ok, if I drop the role, it works
Role dropped.
SQL& create or replace view v as select *
View created.
It is not always a good thing to grant privileges on a view, when you are not the owner of that view
Share this:Like this:Like Loading...
The syntax that you are looking for is
DROP [TEMPORARY] TABLE [IF EXISTS]
&&&&tbl_name [, tbl_name] ...
&&&&[RESTRICT | CASCADE]
Wait, this does not work !
drop table if exists t
&&&&&&&&&&&&&&*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Okay. It was the Oracle MySQL book ?
In the Oracle database, I have created my own droptableifexists script.
I went for a SQL*Plus no-plsql approach. PL/SQL is also possible but it generated different error messages (ORA-06512: at line 1) and different feedback (PL/SQL procedure successfully completed.)
So I check the dictionary, put a command to drop in the sqlplus buffer if a table exists, then run that command first.
droptableifexists.sql
set feed off ver off pages 0 newp none
def cmd=&select 'OK: Table does not exist' from dual&
col cmd new_v cmd nopri
select 'drop table &'||table_name||'&' cmd
from user_tables
where table_name='&1';
set feedb 6 head off
set head on
col cmd clear
Ok, let’s try
SQL& create table t(x number);
Table created.
SQL& @droptableifexists T
Table dropped.
SQL& @droptableifexists T
OK: Table does not exist
A PL/SQL approach could be
for f in (
&&select 'drop table &'||table_name||'&' cmd
&&from user_tables where table_name='T')
&&execute immediate f.
SQL& create table t(x number);
Table created.
SQL& exec for f in (select 'drop table &'||table_name||'&' cmd from user_tables where table_name='T')loop execute immediate f.end loop
PL/SQL procedure successfully completed.
SQL& exec for f in (select 'drop table &'||table_name||'&' cmd from user_tables where table_name='T')loop execute immediate f.end loop
PL/SQL procedure successfully completed.
A bit easier to read. Same has to be done for USER, VIEW and so on.
PS: there are also other versions around catching for ORA-942, but ORA-942 may be ORA-943 in next release, try ; in 11g and 12c to see those things really happen !
Share this:Like this:Like Loading...
Thanks to all my readers for being so faithful ?
I’ll post a new solution to calculate factorial.
This is the one I posted 10 years ago :
I also used it in the
with function f (x number) return number
&&return case x when 1 then x else x*f(x-1)
select value(t), f(value(t))
from table(sys.odcinumberlist(4,6))t
&&VALUE(T) F(VALUE(T))
---------- -----------
&&&&&&&& 4&&&&&&&&&&24
&&&&&&&& 6&&&&&&&& 720
It is neither quicker nor shorter than the one I posted in 2005, but it could not have worked in 2005
Share this:Like this:Like Loading...
When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba.
Test case (21 is a not something to do in real life):
SQL& alter system set processes=21 scope=
System altered.
SQL& startup force quiet
ORACLE instance started.
Database mounted.
Database opened.
From now on, sqlplus as sysdba is impossible.
$ sqlplus -s -L / as sysdba
ORA-00020: maximum number of processes (40) exceeded
SP2-0751: Unable to connect to Oracle.&&Exiting SQL*Plus
Okay, if you really really need to run one script, you could connect with
and restart the database.
if :|sqlplus / as sysdba|grep ORA-00020
&&echo shu abort|sqlplus -prelim / as sysdba
&&echo startup quiet|sqlplus / as sysdba
If ORA-20 is detected, then the database will be restarted.
Share this:Like this:Like Loading...
I just added a few features to WordPress, please report any bug and test your comments here.
Share this:Like this:Like Loading...
Send to Email Address
Your Email Address
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this:Leave a Message | HL Dennis
I’m excited to announce that I have a brand new website at . I’ll be keeping this website for reference but for more information please visit my new website.
Secret Breakers
‘Hi! Well done for checking out the ‘Messages’ part of the site!
At Station X they had an internal mail system for sending messages. They wrote notes, put them inside small containers and then passed them into a vacuum pipe system that sent them hurtling to where they could be read …and answered. We have a similar system here! Write your comments below and they will whiz their way through cyberspace to find their way to me. And if I can, I’ll send a reply back to you here on this part of the site!
Thanks then… and looking forward to hearing from you!’ H.L.