MemoSoup - Mix of the things I always forget

Oracle: get the tablespace structure

The easiest method to get the tablespace structure is to use the dbms_metadata package:

SET linesize 200 LONG 50000 pagesize 5000

SELECT dbms_metadata.get_ddl('TABLESPACE',tablespace_name )
FROM dba_tablespaces
WHERE tablespace_name IN ( '&your_tablespace' );

Additionally, exp/imp (or expdp/impdp) utilities could be used. They could generate the DDL statements for all objects in the database, which could be very useful.

exp/imp

exp userid=" '/ as sysdba' " rows=n file=my.dmp full=y
imp userid=" '/ as sysdba' " file=my.dmp full=y show=y > full_structure.sql 2>&1

Warning! exp could not get the contents of the encrypted tablespaces.

expdp/impdp

expdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR content=metadata_only full=y
impdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR sqlfile=full_structure.sql

Outlook 2007: set default font in Outlook

I think, the “Options” window in Outlook is one of the most tangled things in IT. The programmers were so smart to create real “spaghetti” window! Did they ever hear about “user friendly” interfaces? They could at least add the “Search” field somewhere – otherwise, it’s not possible to find anything.

Here is the way to change the default font:

Tools -> Options
Click “Mail Format” tab
Click “Stationery and Fonts…” button
Select “Personal Stationery” tab
Click “Font” button to change the font

It’s nice, isn’t it?

Here there are even more paths for different options.

Oracle: checksum function for strings

CREATE OR REPLACE FUNCTION get_checksum(source_string IN VARCHAR)
RETURN VARCHAR IS
raw_string RAW(2048);
result RAW(2048);
BEGIN
raw_string := utl_i18n.string_to_raw(source_string);
result := sys.dbms_crypto.hash(raw_string, sys.dbms_crypto.hash_sh1);
RETURN result ;
END;
/

Perl: SOAP problem with dateTime

The following error message was received by Perl application:
Unrecognized type ‘{http://www.w3.org/1999/XMLSchema}dateTime

The problem is caused by “dateTime” type: it was not supported in the 1999 XML Specification.
The solution is to switch to 2001 schema:

my $soap = SOAP::Lite->uri( $NAMESPACE )->proxy( $PROXY_URL )->xmlschema ('2001');

Perl: Soap deserialization error

Sometimes the following error is displayed, if application uses the old version of CPAN SOAP module:
“Client Application failed during request deserialization: no element found at line 1, column 0, byte -1 at XML/Parser.pm”

The possilbe solution is to change SOAP::Transport::HTTP module (SOAP/Transport/HTTP.pm), function “handle”:

sysread( STDIN, $buffer, $length )

should be replaced by

read( STDIN, $buffer, $length )

AutoIt: script for hotkeys usage

This script allows You to define the global Windows hotkeys via the configuration file.

The syntax of the config file is simple:

Hotkey,Program,Parameters

Here “Hotkey” is the combination of the keyboard key with one of the identifiers:

^ Ctrl
! Alt
+ Shift
# Windows-Key

Example:

;Win+C – start calculator
#{c},calc.exe

;Win+Shift+N – start notepad
+#{n},notepad.exe

;Win+N – start notepad++ together with apploc.exe
#{n},%windir%\AppPatch\AppLoc.exe,”%ProgramFiles%\Notepad++\notepad++.exe” -nosession /L0419

Continue reading AutoIt: script for hotkeys usage

Oracle: buffer overflow (ORU-10027)

Sometimes it’s necessary to show the output of the PL/SQL script.
Usually the dbms_output package is used:

SET serveroutput ON
DECLARE
i NUMBER ;
BEGIN
i:=0 ;
WHILE i< 100000 LOOP
i:=i+1 ;
dbms_output.put_line('This is just test line #' || TO_CHAR(i) ) ;
END LOOP ;
END;
/

After several thousands lines the following error message is displayed:

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 8

The error message means, that the internal buffer of dbms_output is full. How to increase it?
The following methods could be used:

...
dbms_package.enable(100000) ;
...

...
SET serveroutput ON SIZE 100000
...

In Oracle 10gR2 the new addition “size unlimited” could be used:

...
SET serveroutput ON SIZE unlimited
...

If these methods do not work, the temporary table could be used:

CREATE SEQUENCE my_output_seq ;
CREATE global TEMPORARY TABLE my_output_table( my_row NUMBER, my_out VARCHAR2(120) ) ;
...
INSERT INTO my_output_table VALUES ( my_output_seq.NEXTVAL, 'This is some string' ) ;
...
SELECT my_out FROM my_output_table ORDER BY my_row ;
DROP TABLE my_output_table ;
DROP SEQUENCE my_output_seq ;

MS Word: put syntax highlighed code into the document

Here is the simple method to put the code with highlighting into the Word document:

Download and install Notepad++. This is easy-to-use and yet very powerful text editor with a lot of features, support of the plugins etc.
Put your code into the Notepad++ edit window

Configure the language, if Notepad++ did not do this automatically:
Language->your language

If necessary the additional configuration could be done in the Style Configurator: Settings->Style Configurator…

Export the highlighted text with help of NppExport plugin: Plugins->NppExport
It has the following options:

Select “Copy RTF to clipboard”
Paste the content of the clipboard into the Word document
Make any changes in the Word document if necessary

Oracle: how to clone profiles

Here is the simple script for cloning the Oracle profiles.

SET serveroutput ON

DECLARE
CURSOR c_profiles IS
SELECT PROFILE, RESOURCE_NAME, LIMIT
FROM dba_profiles
ORDER BY PROFILE, resource_name;

s_PROFILE dba_profiles.PROFILE%TYPE ;
s_prev_PROFILE dba_profiles.PROFILE%TYPE ;
s_RESOURCE_NAME dba_profiles.RESOURCE_NAME%TYPE ;
s_LIMIT dba_profiles.LIMIT%TYPE ;
BEGIN

s_prev_PROFILE := 'no_such_profile' ;

dbms_output.enable(1000000);
OPEN c_profiles;
LOOP
FETCH c_profiles INTO s_PROFILE,s_RESOURCE_NAME,s_LIMIT ;
IF ( s_prev_profile <> s_profile ) THEN
BEGIN
dbms_output.put_line ( '--');
dbms_output.put_line ( 'create profile "'||s_profile||'" limit ' ||s_RESOURCE_NAME|| ' ' || s_LIMIT||';' ) ;
s_prev_profile := s_profile ;
END;
ELSE
dbms_output.put_line ( 'alter profile "'||s_profile|| '" limit ' ||s_RESOURCE_NAME|| ' ' || s_LIMIT || ';' ) ;
END IF;
EXIT WHEN c_profiles%NOTFOUND ;
END LOOP ;

CLOSE c_profiles ;

END;
/

Continue reading Oracle: how to clone profiles

Windows: local printer option is greyed out

Some time ago I need to add the local printer to the system, however, this option was greyed out in the “Add printer” wizard.
The problem was caused by local policy, preventing the adding of the new devices to the system.

The following should be done to enable “Add local printer” option

Start the policy editor (Control Panel -> Administrative tools -> Local security policy)
Select “Local policy” item
Find the item “Devices: Prevent Users from Installing Printer Drivers” in the section Security Settings -> Security Options and change it to Enabled
Go to “Security Settings -> User Rights Assignment” section and check, that “Load & Unalod Drivers” option contains the active user group. It important, that even if it has “All Staff” already, it still could miss “Administrators” group, so it’s necessary to add it manually.

Meta

Tags