Blog Archives
21’st Century PL/SQL
I’m fresh from the two day seminar titled 21’st Century PL/SQL, by Steven Feuerstein (pronounced FOYER-STEEN), that was held by Oracle University in Ljubljana. The presentation material is available from here and all the scripts that are mentioned in the material are stored in demo.zip.
All the participants got a copy of his PL/SQL bible, “Oracle PL/SQL Programming”, 4th edition – and of course, most of us (geeks) took the opportunity to get the book signed by Steven.
As I already said, Steven is an excellent technical speaker. Speaking about programming (no matter which computer language is the subject) is not trivial, I believe it’s much harder than speaking about other IT stuff, such as system administration, database performance tuning, or for example about database administration in general. Browsing through code, explaining it, changing it, actually running it, taking the questions from attendees, answering, keeping the audience focused (and awake!), and all this back and forth is not a picnic.
What I can say for the end of this blog, if you have a chance attend Steven Feuersteins seminar, you’ll certainly not regret, no matter how experienced you’re in PL/SQL, you’ll learn something new.
The Log_buffer parameter cannot be changed In 10g R2
According to Oracle10g R2 Reference Guide LOG_BUFFER parameter is not modifiable any more. This is correct.
Guide is also saying that the default value is: 512 KB or 128 KB * CPU_COUNT, whichever is greater
This information is wrong (and the same documentation error is present in 11g R1 Reference Guide) .
According to Metalink note 351857.1 “The Log_buffer Cannot be Changed In 10g R2”, they changed the way how log buffer is allocated. Oracle 10g R2 combines fixed SGA and redo log buffer. Free space that is left after fixed sized SGA is allocated in the memory granule, Oracle dedicates to LOG_BUFFER.
This is why on all our systems log_buffer is around 14MB (our granule is 16MB, according to _ksmg_granule_size parameter, and fixed size SGA is around 2MB).
SQL> show release release 1002000300 SQL> show sga Total System Global Area 9697230848 bytes Fixed Size 2070776 bytes Variable Size 1291847432 bytes Database Buffers 8388608000 bytes Redo Buffers 14704640 bytes
I think this is a good move by Oracle. A lot of awkward “rules of thumb” for LOG_BUFFER sizing are now obsolete.
Oracle11g OUI – default Oracle base location
I’m not sure who is to blame – it must be the new kids on the block at Oracle HQ….
What do you think about 11g new defaults for Oracle base on Windows?
They propose (silly in it’s own way!) default Oracle base directory with this explanation:
Specify a base location for storing all Oracle software and configuration-related files. This location is the
Oracle base directory. Create one Oracle Base for each operating system user. By default, software and configuration files are installed by version and database name in the Oracle Base directory.
Oracle Base: D:\app\scott
Software Location
Name:
Path: D:\apps\scott\product\11.1.0\db_1
What is wrong with simple, yet universally understandable Oracle base, such as D:\ORACLE, for example. Why APPS, why OS user name?
It must be that someone is smoking the wrong stuff.
Regards,
Ales
Export error after applying CPUOCT2007
After you apply CPUOCT2007 (Patch 12) on top of Oracle 10.2.0.3 you’ll get error during full database export, such as the one shown below:
. about to export SYSTEM's tables via Direct Path ... . . exporting table DEF$_AQCALL EXP-00008: ORACLE error 6550 encountered ORA-06550: line 1, column 18: PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204 ORA-06512: at "SYS.DBMS_SQL", line 323 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 97 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126 ORA-06512: at line 1
Bug is documented in Metalink note:
464672.1 “ORA-06512 SYS.DBMS_EXPORT_EXTENSION And PLS-00201 SYS.DBMS_DEFER_IMPORT_INTERNAL in 11g Export Or After OCTCPU2007”
The recommended workaround is to grant the missing privileges:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO user_doing_export; GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO user_doing_export;
By the way, a short tip: we’re running full export with rows=n on a daily basis. Why? Because we’re keeping those dumps long-term, if anyone wants to get some DDL definition or PL/SQL procedure/package from the past, we can extract the source with the excellent free utility DDL Wizard. DDL Wizard can parse empty (rows=n) export dump files.
SQORAS32: An Unsupported operation was attempted.
A sequence:
1) clean installation of 32-bit Oracle 10.2.0.1 client with Windows components/interfaces (ODBC, OLEDB …) on Windows XP (SP2)
2) installation of patchset 10.2.0.3
If you try to run “Data Sources Administrator” (Control Panel -> Administrative tools) to to add system DSN, selecting Oracle ODBC driver, you’ll receive error: SQORAS32: An Unsupported operation was attempted.
You have to apply Patch 5699495 on top of Patchset 10.2.0.3 (p5699495_10203_WINNT.zip). Patch installation is done simply by replacing some files with the ones from the patch 5699495. Refer to readme.txt for instructions.
dbaportal.eu 