Thinking Out Loud

February 7, 2013

CLI versus GUI

Filed under: oracle — mdinh @ 3:47 am

extract_user.sql

Lately, I have been working more with CLI after having some dependencies on GUI.

No worries, there is not a lack of information from Google to steal what you want.

Good artists copy, Great artists steal – Steve Jobs

Here is an example on how to recreate the user with the same password and privileges.

C:\SQL>sqlplus system/oracle @extract_user.sql scott

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 22:28:38 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:635FFF7EF7E7AA0C4E561203F88246E6B426778B26E6D86B3478778A6AC5;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
--
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
GRANT "DBA" TO "SCOTT";
GRANT "PLUSTRACE" TO "SCOTT";

GRANT UNLIMITED TABLESPACE TO "SCOTT";
GRANT EXECUTE ON "SYS"."DBMS_CRYPTO" TO "SCOTT";

******************************
Execute: cr_extract_user.sql
******************************
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

To verify the password is indeed the same as the original, I created user mdinh with the same hash value for the password.

C:\SQL>sqlplus system/oracle

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 22:29:07 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user mdinh IDENTIFIED BY VALUES 'S:635FFF7EF7E7AA0C4E561203F88246E6B426778B26E6D86B3478778A6AC5;F894844C34402B67';

User created.

SQL> grant dba to mdinh;

Grant succeeded.

SQL> conn mdinh/tiger
Connected.
SQL> conn scott/tiger
Connected.
SQL>

As you can see, both scott and mdinh are tiger.

What do you prefer more, CLI or GUI?

February 6, 2013

DBA_ERRORS

Filed under: oracle — mdinh @ 12:17 pm

Learn something new every day, from the twitter feed, DBA – Default Blame Acceptor.

Another is DBA_ERRORS

Here is an example:

SQL> alter procedure bad_proc compile;
Warning: Procedure altered with compilation errors.
SQL> show error;
Errors for PROCEDURE BAD_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/8      PL/SQL: ORA-00904: "E": invalid identifier
SQL>
SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
  2  FROM dba_errors
  3  WHERE owner='SCOTT'
  4  AND text LIKE '%ORA-%'
  5  AND name='BAD_PROC'
  6  ORDER BY 1
  7  ;
OWNER||'.'||NAME||'|'||TYPE
--------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SCOTT.BAD_PROC|PROCEDURE
PL/SQL: ORA-00904: "E": invalid identifier
SQL>

Thanks to Luke from Pythian for raising my curiosity.

It’s amazing the number of information you can find when you go digging.

For example: DBMS_UTILITY.COMPILE_SCHEMA

10.2 – procedure compiles all procedures, functions, packages, and triggers in the specified schema
11.2 – procedure compiles all procedures, functions, packages, views and triggers in the specified schema

Let’s test this for 11.2.

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create or replace force view tv as select * from t;
Warning: View created with compilation errors.

SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
2 FROM dba_errors
3 WHERE owner='SCOTT'
4 AND text LIKE '%ORA-%'
5 AND name='TV'
6 ORDER BY 1
7 ;

OWNER||'.'||NAME||'|'||TYPE
--------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SCOTT.TV|VIEW
ORA-00942: table or view does not exist

SQL> create table t as select * from emp;
Table created.

SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
2 FROM dba_errors
3 WHERE owner='SCOTT'
4 AND text LIKE '%ORA-%'
5 AND name='TV'
6 ORDER BY 1
7 ;

OWNER||'.'||NAME||'|'||TYPE
--------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SCOTT.TV|VIEW
ORA-00942: table or view does not exist

SQL> execute DBMS_UTILITY.COMPILE_SCHEMA (schema=>user,compile_all=>false);
PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
2 FROM dba_errors
3 WHERE owner='SCOTT'
4 AND text LIKE '%ORA-%'
5 AND name='TV'
6 ORDER BY 1
7 ;

no rows selected
SQL>

Lastly, be aware of the BUG:
DBMS_UTILITY.COMPILE_SCHEMA Always Turns The DEBUG Flag to TRUE [ID 1208971.1]
PL/SQL – Version: 10.2.0.4 to 11.1.0.7 – Release: 10.2 to 11.1

Blog at WordPress.com.