I have been presented with the opportunity to teach at a nearby community college, and I was instantly interested. Little did I know what would be under this newly turned stone.
The class is the 4th part of a 4-part series based (loosely) on Watson & Bersinic's "Oracle 10g All-in-one Certification Exam Prep". After going over the book and finding it dishearteningly weak on practicality, I opted to rewrite the curriculum a little. Or rather, a bit.
The book is interesting; it is a comprehensive, rehashed summarization of the online documentation that aims to get you ready to take the OCA and OCP tests. Perhaps it does that. Ever since Bush's "No Child Left Behind" poorly-implemented federal program cursed our educational systems, I have been convinced that "teaching to the test" is horrible in the long-run and only encourages bad habits of short-term consumption and regurgitation. However, the book is also rife with errors, both in the form of typos, bad index pages, and also technical errors and self-contradictory statements. To top it off, the list price is a freakin' $105!!
Having had dreams of utilizing the tenets of the OCM practicum in a classroom setting, I took this bull by the horns and decided to make all the online homework based on the published OCM outline. Some of the outline topics are rather easy to work up a lab for (ie, creating a database, using RMAN, etc). Some of the topics are a bit more difficult to recreate; for example, what exactly does the 10g OCM practicum test in terms of RAC setup and configuration? But for my purposes, I do not really care. Just as a disclaimer, I have never taken the 10g OCM.
This is a relatively entry-level class, so I am concentrating on the "basics"; I am going to make sure the students know how to create, configure, connect to, manage, backup, recover, tune and optimize the database. The first homework assignment was awesome (IMO). Awesome because these students, after attending the prerequisite classes, had never created a database from the command-line before; they even struggled with some of the things like where to put datafiles, how to size the memory parameters and running the catalog/catproc scripts. And I think that is a good kind of struggle. They asked questions, they explored, they made mistakes. In general, they got their hands dirty doing what we in the real world do. Opposed to answering some silly multi-choice question about which line of sql has an error in it.
And what does this have to do with Certification? Would you hire someone who held an OCP card yet had never created a database? Never had the frustration of a ora-4031 (or any number of ORA errors)? What the heck is Oracle actually certifying as a "Professional" anyway? Oracle ostentaciously says that they require the instructor-led training classes specifically for that "hands-on" aspect of the OCP. Have you ever taken those courses? The labs are a joke. The classes are mostly boring lectures. Unless you end up with someone passionate like Andy Fortunak or Sue Jang. Still, I find the whole scheme rather offensive.
I spotted a blog entry by Dan Norris, who in turn had stumbled up on a thought-provoking blog entry by Bex Huff. Dan also mentioned that Oracle has a Certification blog, so I quickly made my way over there and spouted a little. I really mean what I said there; rewrite the certification paradigm and actually make it worth something, instead of a cash cow for Oracle U; isn't Larry already rolling in enough dough?
To top it all off, my students will not be eligible for certification even if they do pass the OCP. Why? The community college where I teach is not a "Oracle Certified Teaching Partner". So, even though the students will most likely get a much better education at the community college than some class they have to travel to and pay $3500 for, they will not be able to be certitifed as those that are able to expend the time and money to do so.
I dearly hope my students appreciate the hell I am putting them through. =) Fortunately, I have recieved a lot of feedback that they thoroughly enjoy it. It is going to be a good semester.
Monday, January 26, 2009
Thursday, December 04, 2008
Upgrading clusterware/ASM from 10g to 11g - a complaint
I have a RAC setup with 3 environments; DEV, BCP and Production. So, starting with my development cluster, I made an attempt to upgrade the clusterware and ASM from 10g to 11g. That was quite painful. I will not spend much time complaining about the documentation here (and there is a lot I could say *grin*). But allow me to take a little tangent.
I collected all my "constructive criticisms" and suggestions into what I hoped was a cogent document and fired it off to Oracle Support and Erik Peterson of the RAC SIG. Erik forwarded my note to a document manager, and his response was overwhelmingly encouraging and very understanding. The Oracle Analyst in Support that picked up my case was also quick to understand my frustrations. Both of these two different groups were able to collaborate and redeliver a nutshell document that was supposed to supplant/supplement the online documentation.
For the clusterware upgrade, they hit it on the nose. I was able to upgrade the clusterware in my BCP environment with no problem. Well.... I admit, I am still very confused about the CRS and ASM software owners. There seems to be a push to use different users (in both cases) other than the normal "oracle" user. There is not much justification in the documentation, IMO; rather a bland "for security reasons" statement.
The ASM upgrade was a little more tricky. All the documentation that I have, including the new nutshell cookbook, indicate that ASM can be upgraded in a rolling fashion. That is a lie! *grin* I have a new SR open that is exploring this little nuance (or not so little, depending on how much time you have spent trying to wrap your head around this). Apparently, ASM Rolling Upgrades are available in 11gR2. Oh..... well, guess I'll just go download 11gR2 then. Wait - it is not available yet?!? So the documentation lists steps that are not applicable to any version that is currently available? Did I read the right?
So confusing. The good news is that I was able to complete the BCP upgrade with less pain. Since the documentation strongly suggests that one create a new ASM user, there are a ton of manual steps to follow if you choose to go that route. However, smart DBA that I am *sly grin*, I kept the same user and was able to use the DBUA to go from 10g to 11g (10.2.0.3 to 11.1.0.6) - that was sweet. Why would I want to switch users and go through many manual processes? What is the rationale for that? The patchset upgrade from 11.1.0.6 to 11.1.0.7 was less friendly. As I mentioned above, all the documentation states that you should do a rolling upgrade. This is not possible. So I hacked my way through; had to shutdown abort my instances which left shared memory segments hanging around that I had to manually kill off. Not a big deal, but the error messages can be very confusing (ie, ora-12547 TNS lost contanct).
I am looking forward to the document changes that will result from my contacts with Oracle.
I collected all my "constructive criticisms" and suggestions into what I hoped was a cogent document and fired it off to Oracle Support and Erik Peterson of the RAC SIG. Erik forwarded my note to a document manager, and his response was overwhelmingly encouraging and very understanding. The Oracle Analyst in Support that picked up my case was also quick to understand my frustrations. Both of these two different groups were able to collaborate and redeliver a nutshell document that was supposed to supplant/supplement the online documentation.
For the clusterware upgrade, they hit it on the nose. I was able to upgrade the clusterware in my BCP environment with no problem. Well.... I admit, I am still very confused about the CRS and ASM software owners. There seems to be a push to use different users (in both cases) other than the normal "oracle" user. There is not much justification in the documentation, IMO; rather a bland "for security reasons" statement.
The ASM upgrade was a little more tricky. All the documentation that I have, including the new nutshell cookbook, indicate that ASM can be upgraded in a rolling fashion. That is a lie! *grin* I have a new SR open that is exploring this little nuance (or not so little, depending on how much time you have spent trying to wrap your head around this). Apparently, ASM Rolling Upgrades are available in 11gR2. Oh..... well, guess I'll just go download 11gR2 then. Wait - it is not available yet?!? So the documentation lists steps that are not applicable to any version that is currently available? Did I read the right?
So confusing. The good news is that I was able to complete the BCP upgrade with less pain. Since the documentation strongly suggests that one create a new ASM user, there are a ton of manual steps to follow if you choose to go that route. However, smart DBA that I am *sly grin*, I kept the same user and was able to use the DBUA to go from 10g to 11g (10.2.0.3 to 11.1.0.6) - that was sweet. Why would I want to switch users and go through many manual processes? What is the rationale for that? The patchset upgrade from 11.1.0.6 to 11.1.0.7 was less friendly. As I mentioned above, all the documentation states that you should do a rolling upgrade. This is not possible. So I hacked my way through; had to shutdown abort my instances which left shared memory segments hanging around that I had to manually kill off. Not a big deal, but the error messages can be very confusing (ie, ora-12547 TNS lost contanct).
I am looking forward to the document changes that will result from my contacts with Oracle.
Thursday, September 18, 2008
A Tuning experiment
As I learn more about the CBO and Banner, I start to wonder about "system tuning". I like Cary Millsap's "Method R" concept; I was thinking about how to adapt that in the narrow field of sql statements. If, for example, I turn set statistics_level = ALL, I know I can generate rowsource statistics at the cost of perhaps 5% overhead to my system. Doing this in a development environment, I could utilize Wolfgang Breitling's "Cardinality Feedback" to find sql statements that are not performing optimally, and maybe even rank them by degradation. Maybe.
Why would I want to do that? Good question. In and of itself, it is a hard sell if my only purpose is to go on a witch hunt and try to tune the system purely from the sql. However, I think the bigger advantage is to use the various sql statements as teachable moments, a way to develope real-life examples for our DBAs that demonstrate how our application(s) behave, and how to attack "slow" statements. The fortunate side-affect is that we end up making some things faster.
Just an idea. I have not been able to drill down into 11g's automatic tuner yet, but I have to believe it is using something similar to cardinality feedback. If memory serves, I believe it does spend some extra overhead reconsidering previous sql plans in an attempt to find those that are not optimal (or that crazy phrase "suboptimal"). Now I have to go back and read up on that again just to refresh myself....
Why would I want to do that? Good question. In and of itself, it is a hard sell if my only purpose is to go on a witch hunt and try to tune the system purely from the sql. However, I think the bigger advantage is to use the various sql statements as teachable moments, a way to develope real-life examples for our DBAs that demonstrate how our application(s) behave, and how to attack "slow" statements. The fortunate side-affect is that we end up making some things faster.
Just an idea. I have not been able to drill down into 11g's automatic tuner yet, but I have to believe it is using something similar to cardinality feedback. If memory serves, I believe it does spend some extra overhead reconsidering previous sql plans in an attempt to find those that are not optimal (or that crazy phrase "suboptimal"). Now I have to go back and read up on that again just to refresh myself....
Wednesday, August 27, 2008
Character Sets
I have been tasked with presenting on the topic of converting the character set for our Banner system databases. We have been through the exercise several times, which has been quite an eye-opening experience in itself (The DataPump Index work-around, dbms_pclxutil.build_part_index, creating tablespaces). Essentially, the vendor has decided in all its god-like wisdom to force all customers to start using a multibyte Unicode character set that will support international characters (AL32UTF8) when they upgrade to the latest version 8. Yea, not very practical for us at a Big Ten school.
Anyway, I have taken this thing to heart and have been learning a lot about codepoints and various facts about character sets. We will be converting from WE8ISO8859P1 to AL32UTF8, a single-byte ISO 8859-1 West European somewhat basic (eg, simple) codepoint to the latest'n'greatest multibyte grandaddy of Universal character sets. The big issue is making sure that all the data stays the same, even if physically it is stored a little different. Along the way, I have had to come to terms with phrases such as "Lossy Data" and "Truncation Data". April Sims has been a great help in answering some questions and getting me to focus my searches. In the end, Metalink note 252352.1 laid out an example that finally opened my eyes for Lossy Data.
I was having a hard time understanding why one must go through WE8MSWIN1252 first to resolve lossy data. And what was meant by the notation in Metalink Note 260192.1:
Why? Well, the Euro example in note 252352.1 is great. To paraphrase, the Euro is stored as codepoint 128 in WE8ISO8859P1. In this case, that character set essentially has an empty hole there, no previous definitions for that codepoint. So if one were to attempt to select it in some kind of "native" WE8ISO8859P1 environment, there would be nothing. So how did it get there? It is passed in from a different character set. And if using that same character set on the client side, you can easily retrieve the correct symbol.
Since WE8WSWIN1252 is a superset of WE8ISO8859P1 (meaning it has all the same codepoints for existing symbols, and then some), and it is most likely that a client in the US inserted data with a US Microsoft character set, the codepoint will be mapped to a valid symbol. Since that codepoint is "valid" in WE8MSWIN1252, it can then be converted to any other character set that also supports that symbol.
Where this falls apart (the "likly" part) is if the client using a "foreign" character set inserts a symbol that is mapped to a completely different symbol (via the codepoint) in our target client character set. In that case, you are screwed. =) Unless you would want to convert that data manually for each case of incompatible character sets. Not I.
Truncation data is a little easier to grasp. Metalink note 119119.1 mentions the English pound sign (£):
In our case, the fix is handled by setting nls_length_semantics=CHAR (opposed to the default BYTE). We also found out through experimentation that nls_length_semantics is completely ignored for SYS and SYSTEM objects; in fact, the data dictionary is always with BYTE semantics. Interesting, eh?
Metalink Notes:
Others:
Oracle® Database Globalization Support Guide 10g Release 2 (10.2)
Anyway, I have taken this thing to heart and have been learning a lot about codepoints and various facts about character sets. We will be converting from WE8ISO8859P1 to AL32UTF8, a single-byte ISO 8859-1 West European somewhat basic (eg, simple) codepoint to the latest'n'greatest multibyte grandaddy of Universal character sets. The big issue is making sure that all the data stays the same, even if physically it is stored a little different. Along the way, I have had to come to terms with phrases such as "Lossy Data" and "Truncation Data". April Sims has been a great help in answering some questions and getting me to focus my searches. In the end, Metalink note 252352.1 laid out an example that finally opened my eyes for Lossy Data.
I was having a hard time understanding why one must go through WE8MSWIN1252 first to resolve lossy data. And what was meant by the notation in Metalink Note 260192.1:
if you have a WE8ISO8859P1 database and lossy then changing your WE8ISO8859P1 to WE8MSWIN1252 will most likly solve you lossy [sic]
Why? Well, the Euro example in note 252352.1 is great. To paraphrase, the Euro is stored as codepoint 128 in WE8ISO8859P1. In this case, that character set essentially has an empty hole there, no previous definitions for that codepoint. So if one were to attempt to select it in some kind of "native" WE8ISO8859P1 environment, there would be nothing. So how did it get there? It is passed in from a different character set. And if using that same character set on the client side, you can easily retrieve the correct symbol.
Since WE8WSWIN1252 is a superset of WE8ISO8859P1 (meaning it has all the same codepoints for existing symbols, and then some), and it is most likely that a client in the US inserted data with a US Microsoft character set, the codepoint will be mapped to a valid symbol. Since that codepoint is "valid" in WE8MSWIN1252, it can then be converted to any other character set that also supports that symbol.
Where this falls apart (the "likly" part) is if the client using a "foreign" character set inserts a symbol that is mapped to a completely different symbol (via the codepoint) in our target client character set. In that case, you are screwed. =) Unless you would want to convert that data manually for each case of incompatible character sets. Not I.
Truncation data is a little easier to grasp. Metalink note 119119.1 mentions the English pound sign (£):
The pound sign for example is indeed 163 ( A3 in hexadecimal) in the WE8ISO8859P1 and WE8MSWIN1252 charactersets, but in AL32UTF8 the pound sign is code 49827 (C2 A3 in hex).
In our case, the fix is handled by setting nls_length_semantics=CHAR (opposed to the default BYTE). We also found out through experimentation that nls_length_semantics is completely ignored for SYS and SYSTEM objects; in fact, the data dictionary is always with BYTE semantics. Interesting, eh?
Metalink Notes:
- 260192.1 Changing WE8ISO8859P1/ WE8ISO8859P15 or WE8MSWIN1252 to (AL32)UTF8
- 225938.1 Database Character Set Healthcheck
- 252352.1 Euro Symbol Turns up as Upside-Down Questionmark
- 279068.1 Usage of Character Set Scanner Utility (csscan) version 1.1
- 137127.1 Character Sets, Code Pages, Fonts and the NLS_LANG Value
- 241047.1 The Priority of NLS Parameters Explained
- 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
- 179133.1 The correct NLS_LANG in a Windows Environment
- 119119.1 AL32UTF8 / UTF8 (Unicode) Database Character Set Implications
Others:
Oracle® Database Globalization Support Guide 10g Release 2 (10.2)
Friday, August 01, 2008
Software Configuration Manager
I learned that Oracle Support has a couple Oracle blogs up:
http://blogs.oracle.com/supportportal/
http://blogs.oracle.com/Support/
I think I was just checking on them when I noticed that the supportPortal was being a little more aggressive looking for more feedback. Over the years, I have participated in many of the Survey's that Oracle sends you after you close an SR, and have made no small number of comments (both good and bad) to a growing number of contacts I have established within Oracle, so this seemed like yet another opportunity to voice my opinion. I think I do that much more when I get the impression Oracle is actually listening; I don't share the same thoughts here because, well.... I highly doubt Oracle is reading this. =) No offense.
So let me blur the lines a little. First, I think Oracle is doing a much better job at collecting client feedback. However, the more critical part is what they are doing with that feedback. Unfortunately, for us end users, those comments seem to disappear into a black box, never to surface again (much like Enhancement Requests). I chatted with some really nice folks at Oracle (Kristal and Cherryl) and they both reassured me that Oracle is taking our comments seriously, and in fact hinted that the ongoing evolution of Metalink 3 is largely due to user feedback, and even newer things are coming down the pike.
Taking the invitation from the SupportPortal blog seriously, I called up for an appointment and got hooked up right away. I think the lady running the feedback session was working at home (heard a small child in the background), and she had some setup work to accomplish before we got down to it. But 90 minutes later I had made numerous observations about many aspects of the Software Configuration Manager and Metalink 3 in general. It was refreshing (and exhausting) to not only state some things I either liked or did not, but also to demonstrate via a recorded web session. Very satisfying; I recommend this to anyone who deals with metalink on a frequent basis.
So, what did I say? In general, I like the eye-candy of the new interface, but it is also a little slow and sometimes downright confusing. For instance, there is no way to bookmark bugs. The options to configure the layout are a nice thought, but still a little limited; it reminds me of how limited blogger is compared to WordPress, if that helps at all. Don't get me wrong, I think it is quite handy to configure the layout, but I really look forward to improvements. The key point, Software Configuration Manager, is a great concept. Unfortunately, I have some political roadblocks to fully utilizing SCM with my employer, which makes it a little frustrating when attempting to comment on the SCM because it is not SCM's fault at all. =) We require that we either have separate CSI numbers, or implement some sort of access to control such that not all our end-users have equal access to a site-wide license. But if we ignore that for a little while, I think SCM is awesome and has a lot of potential. Just the fact that it helps speed up SRs significantly is a big bonus. I have heard some misgivings about allowing Oracle to retrieve information in such a fashion, even if only passively, but I personally do not have an issue with it.
I do not like how some of the questions are completely inane. For instance, 99% of my SRs are for the Oracle Server - Enterprise Edition. 99% of the time I forget what exactly my "Product" is called and have to hunt for it amongst a list of several hundred product options, most of which we are not even licensed for. Why? What about a list of 10 most recent products for my CSI? What about a list that only contains products I am licensed for? And when attempting to find a category that somewhat matches the subject of an SR, I hate having to select one from the list of 30 or so. I want a searchable list. And a fast search at that; the LOVs in the patch pages really suck.
I concluded by saying that it would be vitally important to me see how Oracle is using this feedback. I have repeated this message to my Oracle contacts and with nearly all feedback I give to Oracle. I long to see what difference I make. Don't you?
Kudos to the teams involved in the feedback gathering operations. And BIG PROPS to Cheryl, Joey and Kristal (all of Oracle).
http://blogs.oracle.com/supportportal/
http://blogs.oracle.com/Support/
I think I was just checking on them when I noticed that the supportPortal was being a little more aggressive looking for more feedback. Over the years, I have participated in many of the Survey's that Oracle sends you after you close an SR, and have made no small number of comments (both good and bad) to a growing number of contacts I have established within Oracle, so this seemed like yet another opportunity to voice my opinion. I think I do that much more when I get the impression Oracle is actually listening; I don't share the same thoughts here because, well.... I highly doubt Oracle is reading this. =) No offense.
So let me blur the lines a little. First, I think Oracle is doing a much better job at collecting client feedback. However, the more critical part is what they are doing with that feedback. Unfortunately, for us end users, those comments seem to disappear into a black box, never to surface again (much like Enhancement Requests). I chatted with some really nice folks at Oracle (Kristal and Cherryl) and they both reassured me that Oracle is taking our comments seriously, and in fact hinted that the ongoing evolution of Metalink 3 is largely due to user feedback, and even newer things are coming down the pike.
Taking the invitation from the SupportPortal blog seriously, I called up for an appointment and got hooked up right away. I think the lady running the feedback session was working at home (heard a small child in the background), and she had some setup work to accomplish before we got down to it. But 90 minutes later I had made numerous observations about many aspects of the Software Configuration Manager and Metalink 3 in general. It was refreshing (and exhausting) to not only state some things I either liked or did not, but also to demonstrate via a recorded web session. Very satisfying; I recommend this to anyone who deals with metalink on a frequent basis.
So, what did I say? In general, I like the eye-candy of the new interface, but it is also a little slow and sometimes downright confusing. For instance, there is no way to bookmark bugs. The options to configure the layout are a nice thought, but still a little limited; it reminds me of how limited blogger is compared to WordPress, if that helps at all. Don't get me wrong, I think it is quite handy to configure the layout, but I really look forward to improvements. The key point, Software Configuration Manager, is a great concept. Unfortunately, I have some political roadblocks to fully utilizing SCM with my employer, which makes it a little frustrating when attempting to comment on the SCM because it is not SCM's fault at all. =) We require that we either have separate CSI numbers, or implement some sort of access to control such that not all our end-users have equal access to a site-wide license. But if we ignore that for a little while, I think SCM is awesome and has a lot of potential. Just the fact that it helps speed up SRs significantly is a big bonus. I have heard some misgivings about allowing Oracle to retrieve information in such a fashion, even if only passively, but I personally do not have an issue with it.
I do not like how some of the questions are completely inane. For instance, 99% of my SRs are for the Oracle Server - Enterprise Edition. 99% of the time I forget what exactly my "Product" is called and have to hunt for it amongst a list of several hundred product options, most of which we are not even licensed for. Why? What about a list of 10 most recent products for my CSI? What about a list that only contains products I am licensed for? And when attempting to find a category that somewhat matches the subject of an SR, I hate having to select one from the list of 30 or so. I want a searchable list. And a fast search at that; the LOVs in the patch pages really suck.
I concluded by saying that it would be vitally important to me see how Oracle is using this feedback. I have repeated this message to my Oracle contacts and with nearly all feedback I give to Oracle. I long to see what difference I make. Don't you?
Kudos to the teams involved in the feedback gathering operations. And BIG PROPS to Cheryl, Joey and Kristal (all of Oracle).
Thursday, June 26, 2008
create tablespace ddl
I recently went looking for ways to recreate tablespaces, as we prepare to migrate our production database to a different character set (necessitated by the vendor, grrr). DBMS_METADATA is really cool, and I like it alot. Data Pump essentially uses DBMS_METADATA (I believe), so I thought one of those cool tools would be the way to go. Close, but not quite. If you do a complete export/import of your database, you end up reorganizing all your data, in essence defragging the whole thing. Which is good (nay, GREAT!). But Data Pump only lets you create tablespaces with the original datafile sizes.
Granted, considering that this is Production, prudence would say that one will have tested this several times (as we are currently doing) before actually running the process in Production. And in doing so, one will know exactly how large the files should be, and can in fact extract the ddl (metadata api, data pump, TOAD, etc) from the test database. As a simple exercise, I still want a way to generate slightly customized tablespace ddl. So I ended up writing my own.
Some of the key things I wanted was to be to dynamically adjust the initial sizes of the datafiles. Also, for the sake of portability, I added a runtime parameter for maxsize. I am still torn about LMT being locked into UNIFORM, but since that is all we use here, I am not too worried for us. =) But I do want LMT and ASSM to be default; for 10g, I see no reason to turn these off at all.
Blogspot is going to horribly screw up the formatting for this, and I have not found a way around it. Sorry.
Granted, considering that this is Production, prudence would say that one will have tested this several times (as we are currently doing) before actually running the process in Production. And in doing so, one will know exactly how large the files should be, and can in fact extract the ddl (metadata api, data pump, TOAD, etc) from the test database. As a simple exercise, I still want a way to generate slightly customized tablespace ddl. So I ended up writing my own.
Some of the key things I wanted was to be to dynamically adjust the initial sizes of the datafiles. Also, for the sake of portability, I added a runtime parameter for maxsize. I am still torn about LMT being locked into UNIFORM, but since that is all we use here, I am not too worried for us. =) But I do want LMT and ASSM to be default; for 10g, I see no reason to turn these off at all.
Blogspot is going to horribly screw up the formatting for this, and I have not found a way around it. Sorry.
REM create_tablespace_ddl.sql
REM Charles Schultz
REM 25-Jun-2008
REM
REM Generates tablespace DDL
REM Assumes the following:
REM - Excluding ('SYSTEM','SYSAUX','USERS','TEMP','TOOLS','UNDOTS')
REM - All LMT
REM - All ASSM
REM - All datafiles AUTOEXTEND, initial size = half of current, or size of initial extent, maxsize defaults to 4000M
REM
REM Retains INITIAL_EXTENT settings for LMT
REM
REM Builds 5 inline materialized views
REM - tablespace_specs: gets the data from dba_tablespaces and dba_data_files, ordering by file_id
REM - first_ddl: generates the initial "CREATE TABLESPACE" statement, along with the first datafile
REM - last_ddl: generates the tablespace options (LMT, ASSM)
REM - mid_ddl: generates statements for any datafiles that are not the first
REM - tablespace_ddl: Combines all ddl, ordered by tablespace name and section
REM
REM Limitations
REM - Does not allow for BIGFILES, but this is a trivial change
REM - Does not allow for LMT Autoallocate. Again, not a difficult modification
PROMPT This script will generate tablespace ddl, making the following assumptions:
PROMPT - All tablespaces are Locally Managed (LMT) with UNIFORM sizes
PROMPT - All tablespaces have automatic segment space management (ASSM)
PROMPT - All datafiles autoextend
PROMPT
accept sqlfile default 'create_tablespaces.sql' char prompt 'Enter name for generated sql file [create_tablespaces.sql]: '
accept initial_size default '50' char prompt 'Percent by which to shrink datafile initial sizes, 0 being none [50]: '
accept max_size default '4000' char prompt 'MAXSIZE of autoextend datafiles, in megabytes [4000]: '
set head off newpage 0 pages 0 verify off feed off lines 200
spool &SQLFILE
with tablespace_specs as
(select row_number() over (partition by tb.tablespace_name order by df.file_id) rn_up,
row_number() over (partition by tb.tablespace_name order by df.file_id desc) rn_down,
tb.BIGFILE,
tb.TABLESPACE_NAME,
df.FILE_NAME,
greatest(df.BYTES*((100-greatest(least(&INITIAL_SIZE,100),0))/100),tb.INITIAL_EXTENT) file_size,
df.INCREMENT_BY*tb.block_size INCREMENT_BY,
tb.INITIAL_EXTENT
from dba_tablespaces tb,dba_data_files df
where tb.TABLESPACE_NAME = df.TABLESPACE_NAME
and tb.tablespace_name not in ('SYSTEM','SYSAUX','USERS','TEMP','TOOLS','UNDOTS')
),
first_ddl as
(select tablespace_name,
1 section_num,
'create tablespace '||tablespace_name||' datafile '''||file_name||''' size '||file_size||' autoextend on next '||increment_by||' maxsize '||&MAX_SIZE||'M' text
from tablespace_specs where rn_up = 1),
last_ddl as
(select tablespace_name,
3 section_num,
'EXTENT MANAGEMENT LOCAL UNIFORM SIZE '||INITIAL_EXTENT||' SEGMENT SPACE MANAGEMENT AUTO;' text
from tablespace_specs where rn_down = 1),
mid_ddl as
(select tablespace_name,
2 section_num,
','''||file_name||''' size '||file_size||' autoextend on next '||increment_by||' maxsize '||&MAX_SIZE||'M' text
from tablespace_specs where rn_up > 1),
tablespace_ddl as
(select text,tablespace_name, section_num from first_ddl
union
select text,tablespace_name, section_num from mid_ddl
union
select text,tablespace_name, section_num from last_ddl
order by tablespace_name, section_num)
select text from tablespace_ddl
/
spool off
Wednesday, June 11, 2008
Stragg
Tom Kyte popularized the STRAGG function, a sometimes critical piece missing from Oracle's arsenal of cool commands, has been bandied about quite a bit:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21961626004
We were recently tasked with using a STRAGG-like function but with dynamic delimiters. Tom Kyte's standard STRAGG does not handle that. Enter Adrian Billington, who took it one step further (look for "10g goes one further"). I really liked his example, and it caused me to research the COLLECTION functionality a little deeper. A little - I do not claim to have mastered it by any means.
Unfortunately for us, we seem to be hitting bug 4381035 (not exactly sure about that, but the best hit I can find). This is under 10.2.0.2.
BANDEV_SQL > CREATE OR REPLACE TYPE oracle.stragg_type AS TABLE OF VARCHAR2(4000);
/
2
Type created.
BANDEV_SQL > CREATE OR REPLACE FUNCTION oracle.stragg (
nt_in IN stragg_type,
delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN varchar2 IS
v_idx PLS_INTEGER;
v_str varchar2(32000);
v_dlm VARCHAR2(10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
END stragg;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Function created.
BANDEV_SQL > drop table test;
Table dropped.
BANDEV_SQL > create table test (a char(2), b char(1));
Table created.
BANDEV_SQL > insert into test values ('aa','1');
1 row created.
BANDEV_SQL > insert into test values ('aa','2');
1 row created.
BANDEV_SQL > insert into test values ('aa','3');
1 row created.
BANDEV_SQL > select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a;
select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
This was a bummer. After a bunch of reading and experimenting, I finally hit upon something that seems to be working:
BANDEV_SQL > select a, oracle.stragg(cast(multiset(select b from test) as oracle.STRAGG_TYPE),'|') b from test group by a;
A
--
B
--------------------------------------------------------------------------------------------------------------------------------------------
aa
1|2|3
I am not exactly sure why MULTISET is working better than COLLECT in this case; it seems to me that both should be producing nested tables.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21961626004
We were recently tasked with using a STRAGG-like function but with dynamic delimiters. Tom Kyte's standard STRAGG does not handle that. Enter Adrian Billington, who took it one step further (look for "10g goes one further"). I really liked his example, and it caused me to research the COLLECTION functionality a little deeper. A little - I do not claim to have mastered it by any means.
Unfortunately for us, we seem to be hitting bug 4381035 (not exactly sure about that, but the best hit I can find). This is under 10.2.0.2.
BANDEV_SQL > CREATE OR REPLACE TYPE oracle.stragg_type AS TABLE OF VARCHAR2(4000);
/
2
Type created.
BANDEV_SQL > CREATE OR REPLACE FUNCTION oracle.stragg (
nt_in IN stragg_type,
delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN varchar2 IS
v_idx PLS_INTEGER;
v_str varchar2(32000);
v_dlm VARCHAR2(10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
END stragg;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Function created.
BANDEV_SQL > drop table test;
Table dropped.
BANDEV_SQL > create table test (a char(2), b char(1));
Table created.
BANDEV_SQL > insert into test values ('aa','1');
1 row created.
BANDEV_SQL > insert into test values ('aa','2');
1 row created.
BANDEV_SQL > insert into test values ('aa','3');
1 row created.
BANDEV_SQL > select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a;
select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
This was a bummer. After a bunch of reading and experimenting, I finally hit upon something that seems to be working:
BANDEV_SQL > select a, oracle.stragg(cast(multiset(select b from test) as oracle.STRAGG_TYPE),'|') b from test group by a;
A
--
B
--------------------------------------------------------------------------------------------------------------------------------------------
aa
1|2|3
I am not exactly sure why MULTISET is working better than COLLECT in this case; it seems to me that both should be producing nested tables.
Subscribe to:
Posts (Atom)
