Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\xsls\kustat.xsl
<?xml version="1.0"?> <!-- NAME kustat.xsl DESCRIPTION XSLT stylesheet for XML => DDL conversion of t_stat_t, and i_stat_t UDTs NOTES Do NOT modify this file under any circumstance. Copy the file if you wish to use this stylesheet with an external XML/XSL parser MODIFIED MM/DD/YY abodge 01/14/09 - Copy 10.2.0.5GC on top of 11.2 DB Control dgagne 01/30/08 - split stats so table and partitions are in differnt pl/sql blocks lbarton 11/02/07 - bug 6605086: escape single quote (move EscapeString to kucommon dgagne 06/19/07 - Don't analyze tables for system generated columns remove analyze where possible sdavidso 02/21/07 - fix ordering of partitions for lrg tests sdavidso 01/10/07 - sort columns for lrg tests dgagne 08/17/06 - add new optimizer support sdavidso 11/02/05 - fix inconsistent stylesheet format dgagne 04/27/05 - add double quotes around schema name/table name/ index name/ part_name etc dgagne 04/22/05 - if date is 0000-00-00 then use null dgagne 03/10/05 - use index owner, not base table owner when importing index stats dgagne 01/13/05 - use NULL keyword when index_stats have null values returned dgagne 10/14/04 - add transform parameter stats_lock to lock statistics dgagne 07/22/04 - double quote index name in dbms_stats call dgagne 06/15/04 - use stats table for statistics dgagne 04/15/04 - add no-aggregate flag to dbms_stats calls dgagne 02/24/04 - update using new xml dgagne 12/26/03 - add stats locking information dgagne 12/08/03 - escape strings that have single ' to two single '' dgagne 12/03/03 - add missing subpartion index statistics dgagne 11/05/03 - use fixed view for finding the system gen index dgagne 02/21/03 - fix 4 stats bugs dgagne 01/02/03 - split statistics into multi ddl statements dgagne 12/04/02 - fix stats for subpartitioned tables dgagne 11/21/02 - fix statistics for table and bit map indexes dgagne 12/02/02 - change base object for ind stats to ind, not tab jdavison 10/31/02 - Fix quoting of subname dgagne 08/29/02 - combine stats subcommands into single command htseng 08/02/02 - add more parse items htseng 07/26/02 - add more parse params dgagne 07/24/02 - add doparse for each column statatistic dgagne 03/26/02 - dgagne_mdapi_stats dgagne 01/14/02 - Created --> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- Import required scripts --> <xsl:import href="kucommon.xsl"/> <!-- Top-level parameters --> <xsl:param name="PRETTY">1</xsl:param> <xsl:param name="SQLTERMINATOR">0</xsl:param> <xsl:param name="EXPORT">0</xsl:param> <xsl:param name="LOCK_STATS">0</xsl:param> <xsl:param name="LRG">0</xsl:param> <xsl:param name="STAT_SCHEMA" select="''"/> <xsl:param name="STAT_TABLE" select="''"/> <!-- params for parse --> <xsl:param name="PRS_DDL">0</xsl:param> <xsl:param name="PRS_DELIM">\{]`</xsl:param> <xsl:param name="PRS_VERB">0</xsl:param> <xsl:param name="PRS_OBJECT_TYPE">0</xsl:param> <xsl:param name="PRS_SCHEMA">0</xsl:param> <xsl:param name="PRS_NAME">0</xsl:param> <xsl:param name="PRS_GRANTEE">0</xsl:param> <xsl:param name="PRS_GRANTOR">0</xsl:param> <xsl:param name="PRS_BASE_OBJECT_SCHEMA">0</xsl:param> <xsl:param name="PRS_BASE_OBJECT_NAME">0</xsl:param> <xsl:param name="PRS_BASE_OBJECT_TYPE">0</xsl:param> <!-- Table stats specific templates start here --> <!-- Template to recreate Table Statistics --> <xsl:template match="T_STAT_T"> <!-- Table statistics consists of a dbms_stats call for the table and one for each column, then one for each partition and one for each column in the partition. --> <xsl:choose> <!-- If there are system generated columns and the version is prior to 2.1 then generate the analyze table call. Otherwise there is enough information stored to restore the statistics - even on the generated column names --> <xsl:when test="(TAB_INFO/SYSGEN_COLS > 0) and ((VERS_MAJOR = 1) or ((VERS_MAJOR = 2) and (VERS_MINOR = 0)))"> <!-- Analyze the table --> <xsl:call-template name="AnalyzeTable"/> </xsl:when> <!-- If the Major version of the xml document is 1, then do the stats the old way --> <xsl:when test="VERS_MAJOR=1"> <xsl:call-template name="OldTabStats"/> </xsl:when> <xsl:otherwise> <!-- Do the table statistics the new way--> <xsl:call-template name="NewTabStats"/> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- analyze table happens with an xml document with a version prior to 2.1 and database version 11.2 if there are system generated columns in the table --> <xsl:template name="AnalyzeTable"> <!-- add parse item for analyze table command --> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">ANALYZE</xsl:with-param> <xsl:with-param name="ObjectType">TABLE_STATISTICS</xsl:with-param> <xsl:with-param name="BaseSchemaNode" select="OWNER_NAME"/> <xsl:with-param name="BaseNameNode" select="NAME"/> <xsl:with-param name="BaseObjectType" select="TYPE_NAME"/> </xsl:call-template> <xsl:text> ANALYZE TABLE "</xsl:text> <xsl:value-of select="BASE_OBJ/OWNER_NAME"/> <xsl:text>"."</xsl:text> <xsl:value-of select="BASE_OBJ/NAME"/> <xsl:text>" COMPUTE STATISTICS</xsl:text> <xsl:if test="$SQLTERMINATOR=1"> <!-- Terminate the SQL statement --> <xsl:text>; </xsl:text> </xsl:if> </xsl:template> <!-- With an xml document that is older than major verison 2, the stats are restored using DBMS_STATS.SET_TABLE_STATS. This is the old/slow way of restoring statistics. --> <xsl:template name="OldTabStats"> <xsl:call-template name="DoTabStats"> <xsl:with-param name="Base" select="."/> <xsl:with-param name="Node" select="TAB_INFO"/> </xsl:call-template> <!-- Do the table partitions --> <xsl:choose> <xsl:when test="$LRG!=0"> <xsl:for-each select="PTAB_INFO_LIST/PTAB_INFO_LIST_ITEM"> <xsl:sort select="SCHEMA_OBJ/SUBNAME" data-type="text"/> <xsl:call-template name="DoTabStats"> <xsl:with-param name="Base" select="../.."/> <xsl:with-param name="Node" select="."/> </xsl:call-template> </xsl:for-each> </xsl:when> <xsl:otherwise> <xsl:for-each select="PTAB_INFO_LIST/PTAB_INFO_LIST_ITEM"> <xsl:call-template name="DoTabStats"> <xsl:with-param name="Base" select="../.."/> <xsl:with-param name="Node" select="."/> </xsl:call-template> </xsl:for-each> </xsl:otherwise> </xsl:choose> <!-- Lock the statistics if needed--> <xsl:call-template name="LockStats"> <xsl:with-param name="Base" select="."/> <xsl:with-param name="Node" select="TAB_INFO"/> </xsl:call-template> </xsl:template> <!-- Build the actual dbms_stats commands --> <xsl:template name="DoTabStats"> <xsl:param name="Base"/> <xsl:param name="Node"/> <!-- add parse item for standard parse items if requested --> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">DBMS_STATS</xsl:with-param> <xsl:with-param name="ObjectType">TABLE_STATISTICS</xsl:with-param> <xsl:with-param name="BaseSchemaNode" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="BaseNameNode" select="$Base/BASE_OBJ/NAME"/> <xsl:with-param name="BaseObjectType" select="$Base/BASE_OBJ/TYPE_NAME"/> </xsl:call-template> <xsl:text>BEGIN</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DBMS_STATS.SET_TABLE_STATS(</xsl:text> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/TAB_INFO/SCHEMA_OBJ/NAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <!-- If supplied, use SUBNAME, otherwise, insert "NULL" --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Node/SCHEMA_OBJ/SUBNAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:text>NULL, NULL, </xsl:text> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/ROWCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/BLKCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/AVGRLN"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/FLAGS"/> <xsl:with-param name="Trailing"> + 4, NULL, FALSE, </xsl:with-param> <xsl:with-param name="NullVal">0 + 4, NULL, FALSE, </xsl:with-param> </xsl:call-template> <!-- If supplied, use CACHEDBLK, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/CACHE_INFO/CACHEDBLK"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- If supplied, use CACHEHIT, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/CACHE_INFO/CACHEHIT"/> <xsl:with-param name="Trailing">); </xsl:with-param> <xsl:with-param name="NullVal">NULL); </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>END; </xsl:text> <xsl:call-template name="DoTerminator"> <xsl:with-param name="Text"/> </xsl:call-template> <xsl:text>
</xsl:text> <!-- Do column statistics for this table or partition for running LRGs, we need a deterministic order, for benchmarks. --> <xsl:choose> <xsl:when test="$LRG!=0"> <xsl:for-each select="$Node/COL_STATS/COL_STATS_ITEM"> <!-- add parse item for standard parse items if requested --> <xsl:sort select="INTCOL_NUM" data-type="number"/> <xsl:call-template name="DoColStats"> <xsl:with-param name="Schema" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Table" select="$Base/TAB_INFO/SCHEMA_OBJ/NAME"/> <xsl:with-param name="SubName" select="$Node/SCHEMA_OBJ/SUBNAME"/> <xsl:with-param name="Node" select="."/> <xsl:with-param name="CName" select="COLNAME"/> </xsl:call-template> </xsl:for-each> </xsl:when> <xsl:otherwise> <xsl:for-each select="$Node/COL_STATS/COL_STATS_ITEM"> <!-- add parse item for standard parse items if requested --> <xsl:call-template name="DoColStats"> <xsl:with-param name="Schema" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Table" select="$Base/TAB_INFO/SCHEMA_OBJ/NAME"/> <xsl:with-param name="SubName" select="$Node/SCHEMA_OBJ/SUBNAME"/> <xsl:with-param name="Node" select="."/> <xsl:with-param name="CName" select="COLNAME"/> </xsl:call-template> </xsl:for-each> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="DoColStats"> <xsl:param name="Schema"/> <xsl:param name="Table"/> <xsl:param name="SubName"/> <xsl:param name="Node"/> <xsl:param name="CName"/> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">DBMS_STATS</xsl:with-param> <xsl:with-param name="ObjectType">TABLE_STATISTICS</xsl:with-param> <xsl:with-param name="BaseSchemaNode" select="$Schema"/> <xsl:with-param name="BaseNameNode" select="$Table"/> <xsl:with-param name="BaseObjectType">TABLE</xsl:with-param> </xsl:call-template> <xsl:text>DECLARE</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> SREC DBMS_STATS.STATREC; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>BEGIN</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- if LOWVAL supplied, use it, otherwise use null --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/LOWVAL"/> <xsl:with-param name="Leading"> SREC.MINVAL := '</xsl:with-param> <xsl:with-param name="Trailing">'; </xsl:with-param> <xsl:with-param name="NullVal"> SREC.MINVAL := NULL; </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- if HIVAL supplied, use it, otherwise use null --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/HIVAL"/> <xsl:with-param name="Leading"> SREC.MAXVAL := '</xsl:with-param> <xsl:with-param name="Trailing">'; </xsl:with-param> <xsl:with-param name="NullVal"> SREC.MAXVAL := NULL; </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Fill in EAVS --> <xsl:text> SREC.EAVS := </xsl:text> <xsl:value-of select="$Node/EAV"/> <xsl:text>; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- fill in all ep values specified --> <xsl:text> SREC.CHVALS := DBMS_STATS.CHARARRAY(</xsl:text> <!-- fill in epvalues if specified --> <xsl:if test="HIST_GRAM_LIST/HIST_GRAM_LIST_ITEM/EPVALUE"> <!-- if not first epvalue, add "," before new value --> <xsl:for-each select="HIST_GRAM_LIST/HIST_GRAM_LIST_ITEM"> <!-- if not first epvalue, add beginning "," before new value --> <xsl:if test="position() != 1"> <xsl:text>, </xsl:text> </xsl:if> <xsl:text>'</xsl:text> <xsl:value-of select="EPVALUE"/> <xsl:text>'</xsl:text> </xsl:for-each> </xsl:if> <!-- add end of line --> <xsl:text>); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- if endpoint values and buckets specified, use them, otherwise, use Minimum and Maximum values --> <xsl:choose> <xsl:when test="HIST_GRAM_LIST/HIST_GRAM_LIST_ITEM"> <xsl:text> SREC.NOVALS := DBMS_STATS.NUMARRAY(</xsl:text> <xsl:for-each select="HIST_GRAM_LIST/HIST_GRAM_LIST_ITEM"> <!-- if not first endpoint value, add add comma --> <xsl:if test="position() != 1"> <xsl:text>, </xsl:text> </xsl:if> <xsl:value-of select="ENDPOINT"/> </xsl:for-each> <!-- add end of line --> <xsl:text>); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> SREC.BKVALS := DBMS_STATS.NUMARRAY(</xsl:text> <xsl:for-each select="HIST_GRAM_LIST/HIST_GRAM_LIST_ITEM"> <!-- if not first bucket, add comma --> <xsl:if test="position() != 1"> <xsl:text>, </xsl:text> </xsl:if> <xsl:value-of select="BUCKET"/> <xsl:if test="position() = last()"> <!-- add end of line --> <xsl:text>); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> SREC.EPC := </xsl:text> <xsl:value-of select="last()"/> <xsl:text>; </xsl:text> </xsl:if> </xsl:for-each> </xsl:when> <xsl:when test="HIST_GRAM_MIN/ENDPOINT"> <xsl:text> SREC.NOVALS := DBMS_STATS.NUMARRAY(</xsl:text> <xsl:value-of select="HIST_GRAM_MIN/ENDPOINT"/> <xsl:text>, </xsl:text> <xsl:value-of select="HIST_GRAM_MAX/ENDPOINT"/> <xsl:text>); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> SREC.BKVALS := DBMS_STATS.NUMARRAY(0, 1); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> SREC.EPC := 2; </xsl:text> </xsl:when> <xsl:otherwise> <xsl:text> SREC.NOVALS := DBMS_STATS.NUMARRAY(); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> SREC.BKVALS := DBMS_STATS.NUMARRAY(); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> SREC.EPC := 0; </xsl:text> </xsl:otherwise> </xsl:choose> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DBMS_STATS.SET_COLUMN_STATS(</xsl:text> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Schema"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Table"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$CName"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <!-- If supplied, use SUBNAME, otherwise, insert "NULL" --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$SubName"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:text>NULL, NULL, </xsl:text> <xsl:value-of select="$Node/DISTCNT"/> <xsl:text>, </xsl:text> <xsl:value-of select="$Node/DENSITY"/> <xsl:text>, </xsl:text> <xsl:value-of select="$Node/NULL_CNT"/> <xsl:text>, srec, </xsl:text> <xsl:value-of select="$Node/AVGCLN"/> <xsl:text>, </xsl:text> <xsl:value-of select="$Node/CFLAGS"/> <xsl:text> + 4); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>END; </xsl:text> <xsl:call-template name="DoTerminator"> <xsl:with-param name="Text"/> </xsl:call-template> </xsl:template> <!-- If the major version of the xml document is 2 or greater, dbms_stats.import_table_stats will be used to restore statistics. It is much faster than dbms_stats.set_table_stats. --> <xsl:template name="NewTabStats"> <xsl:call-template name="InsertTabStats"> <xsl:with-param name="Base" select="."/> <xsl:with-param name="Node" select="TAB_INFO"/> </xsl:call-template> <!-- Do the table partition statistics the new way --> <xsl:choose> <xsl:when test="$LRG!=0"> <xsl:for-each select="PTAB_INFO_LIST/PTAB_INFO_LIST_ITEM"> <xsl:sort select="partname" data-type="text"/> <xsl:call-template name="InsertTabStats"> <xsl:with-param name="Base" select="../.."/> <xsl:with-param name="Node" select="."/> </xsl:call-template> </xsl:for-each> </xsl:when> <xsl:otherwise> <xsl:for-each select="PTAB_INFO_LIST/PTAB_INFO_LIST_ITEM"> <xsl:call-template name="InsertTabStats"> <xsl:with-param name="Base" select="../.."/> <xsl:with-param name="Node" select="."/> </xsl:call-template> </xsl:for-each> </xsl:otherwise> </xsl:choose> <!-- Lock the statistics --> <xsl:call-template name="LockStats"> <xsl:with-param name="Base" select="."/> <xsl:with-param name="Node" select="TAB_INFO"/> </xsl:call-template> </xsl:template> <xsl:template name="InsertTabStats"> <xsl:param name="Base"/> <xsl:param name="Node"/> <!-- add parse item for standard parse items if requested --> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">DBMS_STATS</xsl:with-param> <xsl:with-param name="ObjectType">TABLE_STATISTICS</xsl:with-param> <xsl:with-param name="BaseSchemaNode" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="BaseNameNode" select="$Base/BASE_OBJ/NAME"/> <xsl:with-param name="BaseObjectType" select="$Base/BASE_OBJ/TYPE_NAME"/> </xsl:call-template> <xsl:text>DECLARE </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> colname varchar2(60); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>BEGIN</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Truncate the table --> <xsl:text> DELETE FROM "</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"."</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>"; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> INSERT INTO "</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"."</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>" (type, version, flags, c1, c2, c3, c5, n1, n2, n3, n4, n10, n11, n12, d1) VALUES ('T', 5, </xsl:text> <xsl:value-of select="$Node/FLAGS"/> <!--c1 is table name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/TAB_INFO/TABNAME"/> <xsl:with-param name="Leading">, '</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> </xsl:call-template> <!--c2 is table partition name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Node/PARTNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--c3 is table subpartition name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Node/SUBPARTNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--c5 is schema name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!--n1 is row count --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/ROWCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n2 is block count --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/BLKCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n3 is average length --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/AVGRLN"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n4 is sample size --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/SAMPLE_SIZE"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n10 is chdblk = cached block --> <!-- If supplied, use CACHEDBLK, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/CACHE_INFO/CACHEDBLK"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n11 is chr "cache hit ratio --> <!-- If supplied, use CACHEHIT, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/CACHE_INFO/CACHEHIT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n12 is ???lgrds??? --> <xsl:text>NULL, </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!--d1 is last analyzed time --> <xsl:variable name="BADTIME">0000-00-00</xsl:variable> <xsl:choose> <xsl:when test="contains($Node/ANALYZETIME, $BADTIME)"> <xsl:text>NULL);</xsl:text> </xsl:when> <xsl:otherwise> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/ANALYZETIME"/> <xsl:with-param name="Leading">TO_DATE('</xsl:with-param> <xsl:with-param name="Trailing">', 'YYYY-MM-DD:HH24:MI:SS'));</xsl:with-param> <xsl:with-param name="NullVal">NULL);</xsl:with-param> </xsl:call-template> </xsl:otherwise> </xsl:choose> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Do column statistics for this table/partition/subpartition --> <xsl:for-each select="$Base/COLUMN_LIST/COLUMN_LIST_ITEM"> <xsl:variable name="ColNode" select="."/> <xsl:choose> <xsl:when test="$Node/COL_STATS/COL_STATS_ITEM[INTCOL_NUM=INTCOL_NUM]"> <xsl:for-each select="$Node/COL_STATS/COL_STATS_ITEM[INTCOL_NUM= $ColNode/INTCOL_NUM]"> <xsl:text> colname := </xsl:text> <xsl:call-template name="GetColName"> <xsl:with-param name="Base" select="$Base/BASE_OBJ"/> <xsl:with-param name="ColNode" select="$ColNode"/> </xsl:call-template> <xsl:text>; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Do histograms if present --> <xsl:choose> <xsl:when test="HIST_GRAM_LIST/HIST_GRAM_LIST_ITEM"> <xsl:for-each select="HIST_GRAM_LIST/HIST_GRAM_LIST_ITEM"> <xsl:call-template name="InsertColStats"> <xsl:with-param name="Schema" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Table" select="$Base/TAB_INFO/TABNAME"/> <xsl:with-param name="TINode" select="../../../.."/> <xsl:with-param name="Node" select="../.."/> <xsl:with-param name="CName" select="$ColNode/COLNAME"/> </xsl:call-template> </xsl:for-each> </xsl:when> <xsl:otherwise> <!-- There must be min and max, do that now. --> <xsl:call-template name="InsertColStats"> <xsl:with-param name="Schema" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Table" select="$Base/TAB_INFO/TABNAME"/> <xsl:with-param name="TINode" select="../.."/> <xsl:with-param name="Node" select="."/> <xsl:with-param name="CName" select="$ColNode/COLNAME"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:for-each> </xsl:when> <xsl:otherwise> <xsl:text> colname := </xsl:text> <xsl:call-template name="GetColName"> <xsl:with-param name="Base" select="$Base/BASE_OBJ"/> <xsl:with-param name="ColNode" select="$ColNode"/> </xsl:call-template> <xsl:text>; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Histogram is null, load default null values --> <xsl:call-template name="InsertColStats"> <xsl:with-param name="Schema" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Table" select="$Base/TAB_INFO/TABNAME"/> <xsl:with-param name="TINode" select="$Node"/> <xsl:with-param name="Node" select="$Node"/> <xsl:with-param name="CName" select="$ColNode/COLNAME"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:for-each> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DBMS_STATS.IMPORT_TABLE_STATS(</xsl:text> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/TAB_INFO/TABNAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <xsl:text>NULL, '"</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>"', NULL, NULL, '"</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"'); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DELETE FROM "</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"."</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>"; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>END; </xsl:text> <xsl:call-template name="DoTerminator"> <xsl:with-param name="Text"/> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> </xsl:template> <xsl:template name="InsertColStats"> <xsl:param name="Schema"/> <xsl:param name="Table"/> <xsl:param name="TINode"/> <!-- Table Information node --> <xsl:param name="Node"/> <!-- Stats Node --> <xsl:param name="CName"/> <xsl:text> INSERT INTO "</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"."</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>" (type, version, c1, c2, c3, c4, c5, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, d1, r1, r2, ch1, flags) VALUES ('C', 5, </xsl:text> <!--c1 is table name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Table"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> </xsl:call-template> <!--c2 is table partition name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$TINode/PARTNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--c3 is table subpartition name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$TINode/SUBPARTNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--c4 is column name --> <xsl:text>colname, </xsl:text> <!--c5 is owner name --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Schema"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!--n1 is distinct count --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/DISTCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">0, </xsl:with-param> </xsl:call-template> <!--n2 is density --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/DENSITY"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">0, </xsl:with-param> </xsl:call-template> <!--n3 is spare1 if available --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/SPARE1"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">0, </xsl:with-param> </xsl:call-template> <!--n4 is sample size if available --> <xsl:choose> <xsl:when test="($Node/SAMPLE_SIZE > 0) or ($Node/ROWCNT > 0)"> <xsl:value-of select="$Node/SAMPLE_SIZE"/> <xsl:text>, </xsl:text> </xsl:when> <xsl:otherwise> <xsl:text>NULL, </xsl:text> </xsl:otherwise> </xsl:choose> <!--n5 is null count --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/NULL_CNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">0, </xsl:with-param> </xsl:call-template> <!--n6 is minimum --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/MINIMUM"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n7 is maximum --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/MAXIMUM"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--n8 is average length --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/AVGCLN"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">0, </xsl:with-param> </xsl:call-template> <!--n9 is (if histogram = 1, otherwise null --> <!-- to check if this is a histogram, see ../HIST_GRAM_LIST_ITEM exists --> <xsl:choose> <xsl:when test="../HIST_GRAM_LIST_ITEM"> <xsl:text>1, </xsl:text> </xsl:when> <xsl:otherwise> <xsl:text>NULL, </xsl:text> </xsl:otherwise> </xsl:choose> <!--n10 is bucket number --> <!--n11 is endpoint --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="BUCKET"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="ENDPOINT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!--d1 is last analyzed --> <xsl:variable name="BADTIME">0000-00-00</xsl:variable> <xsl:choose> <xsl:when test="contains($TINode/ANALYZETIME, $BADTIME)"> <xsl:text>NULL, </xsl:text> </xsl:when> <xsl:otherwise> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$TINode/ANALYZETIME"/> <xsl:with-param name="Leading">TO_DATE('</xsl:with-param> <xsl:with-param name="Trailing">', 'YYYY-MM-DD:HH24:MI:SS'), </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> </xsl:otherwise> </xsl:choose> <!--r1 is min value --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/LOWVAL"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--r2 is max value --> <!-- if HIVAL supplied, use it, otherwise use null --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/HIVAL"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!--ch1 is epvalue --> <!--flags is flags + 4 if epvalue --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="EPVALUE"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', 4 + </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$Node/CFLAGS"/> <xsl:with-param name="Trailing">);</xsl:with-param> <xsl:with-param name="NullVal">0);</xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> </xsl:template> <xsl:template name="LockStats"> <xsl:param name="Base"/> <!-- Lock stats only if they were previously locked --> <xsl:if test="(($Base/TAB_INFO/TRIGFLAG mod 268435456) >= 67108864) or ($LOCK_STATS = 1)"> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">DBMS_STATS</xsl:with-param> <xsl:with-param name="ObjectType">TABLE_STATISTICS</xsl:with-param> <xsl:with-param name="BaseSchemaNode" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="BaseNameNode" select="$Base/BASE_OBJ/NAME"/> <xsl:with-param name="BaseObjectType" select="$Base/BASE_OBJ/TYPE_NAME"/> </xsl:call-template> <xsl:text>BEGIN</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DBMS_STATS.LOCK_TABLE_STATS(</xsl:text> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/BASE_OBJ/OWNER_NAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/BASE_OBJ/NAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> <xsl:choose> <xsl:when test="($Base/TAB_INFO/TRIGFLAG mod 268435456)>= 201326592"> <xsl:text>'ALL'); </xsl:text> </xsl:when> <xsl:when test="($Base/TAB_INFO/TRIGFLAG mod 268435456)>= 134217728"> <xsl:text>'CACHE'); </xsl:text> </xsl:when> <xsl:when test="($Base/TAB_INFO/TRIGFLAG mod 268435456)>= 67108864"> <xsl:text>'DATA'); </xsl:text> </xsl:when> <xsl:otherwise> <xsl:text>'ALL'); </xsl:text> </xsl:otherwise> </xsl:choose> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>END; </xsl:text> <xsl:call-template name="DoTerminator"> <xsl:with-param name="Text"/> </xsl:call-template> <xsl:text>
</xsl:text> </xsl:if> </xsl:template> <!-- table stats specific templates complete --> <!-- Index stats specific templates start here --> <!-- Template to recreate Index Statistics --> <xsl:template match="I_STAT_T"> <xsl:choose> <xsl:when test="VERS_MAJOR=1"> <!-- Do the index statistics the old way--> <xsl:call-template name="OldIndStats"/> </xsl:when> <xsl:otherwise> <!-- Do the table statistics the new way--> <xsl:call-template name="NewIndStats"/> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- Index statistics consists of a dbms_stats call for the index and one for each partition. Do the index statistics if analyzed. Partitions can be analyzed with the base index not analyzed. If that is the case, just do the partitions. With an xml document that is older than major verison 2, the stats are restored using DBMS_STATS.SET_INDEX_STATS. This is the old/slow way of restoring statistics. If later than version 2, the stats are restored using DBMS_STATS.IMPORT_INDEX_STATS. This is the new/fast way. OldIndStats is for version < 2 NewIndStats is for version > 2 --> <xsl:template name="OldIndStats"> <xsl:if test="ROWCNT"> <xsl:call-template name="DoIndStats"> <xsl:with-param name="Base" select="BASE_IND_OBJ"/> <xsl:with-param name="Node" select="BASE_IND_OBJ"/> </xsl:call-template> </xsl:if> <!-- Do the index partition statistics --> <xsl:for-each select="PARTITION_LIST/PARTITION_LIST_ITEM"> <xsl:call-template name="DoIndStats"> <xsl:with-param name="Base" select="../../BASE_IND_OBJ"/> <xsl:with-param name="Node" select="."/> </xsl:call-template> <!-- Do the same for each subpartition statistics --> <xsl:for-each select="SUBPARTITION_LIST/SUBPARTITION_LIST_ITEM"> <xsl:call-template name="DoIndStats"> <xsl:with-param name="Base" select="../../../../BASE_IND_OBJ"/> <xsl:with-param name="Node" select="."/> </xsl:call-template> </xsl:for-each> </xsl:for-each> </xsl:template> <xsl:template name="NewIndStats"> <!-- Do the index statistics the new way--> <!-- add parse item for standard parse items if requested --> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">DBMS_STATS</xsl:with-param> <xsl:with-param name="ObjectType">INDEX_STATISTICS</xsl:with-param> <xsl:with-param name="BaseSchemaNode" select="BASE_IND_OBJ/OWNER_NAME"/> <xsl:with-param name="BaseNameNode" select="BASE_IND_OBJ/NAME"/> <xsl:with-param name="BaseObjectType" select="BASE_IND_OBJ/TYPE_NAME"/> </xsl:call-template> <xsl:text>DECLARE IND_NAME VARCHAR2(60); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> IND_OWNER VARCHAR2(60); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> colname DBMS_METADATA.T_VAR_COLL; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>BEGIN</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Truncate the stats table --> <xsl:text> DELETE FROM "</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"."</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>"; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:call-template name="InsertIndStats"> <xsl:with-param name="Base" select="BASE_IND_OBJ"/> <xsl:with-param name="Node" select="BASE_IND_OBJ"/> <xsl:with-param name="Truncate">1</xsl:with-param> </xsl:call-template> <!-- Do the index partition statistics --> <xsl:for-each select="PARTITION_LIST/PARTITION_LIST_ITEM"> <xsl:call-template name="InsertIndStats"> <xsl:with-param name="Base" select="../../BASE_IND_OBJ"/> <xsl:with-param name="Node" select="."/> <xsl:with-param name="Truncate">0</xsl:with-param> </xsl:call-template> <!-- Do the same for each subpartition statistics --> <xsl:for-each select="SUBPARTITION_LIST/SUBPARTITION_LIST_ITEM"> <xsl:call-template name="InsertIndStats"> <xsl:with-param name="Base" select="../../../../BASE_IND_OBJ"/> <xsl:with-param name="Node" select="."/> <xsl:with-param name="Truncate">0</xsl:with-param> </xsl:call-template> </xsl:for-each> </xsl:for-each> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DBMS_STATS.IMPORT_INDEX_STATS('"' || ind_owner || '"', '"' || ind_name || '"', NULL, '"</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>"', NULL, '"</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"'); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DELETE FROM "</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"."</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>"; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>END; </xsl:text> <xsl:call-template name="DoTerminator"> <xsl:with-param name="Text"/> </xsl:call-template> </xsl:template> <!-- Create a dbms_stats call for the specified table/partition/subpartition --> <xsl:template name="DoIndStats"> <xsl:param name="Base"/> <xsl:param name="Node"/> <!-- add parse item for standard parse items if requested --> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">DBMS_STATS</xsl:with-param> <xsl:with-param name="ObjectType">INDEX_STATISTICS</xsl:with-param> <xsl:with-param name="BaseSchemaNode" select="$Base/OWNER_NAME"/> <xsl:with-param name="BaseNameNode" select="$Base/NAME"/> <xsl:with-param name="BaseObjectType" select="$Base/TYPE_NAME"/> </xsl:call-template> <xsl:text>DECLARE </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> IND_OWNER VARCHAR2(60); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> IND_NAME VARCHAR2(60); </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> COLNAME dbms_metadata.t_var_coll; </xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>BEGIN</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Get the index name --> <xsl:call-template name="GetIndex"> <xsl:with-param name="Base" select="$Base"/> <xsl:with-param name="Node" select="$Node"/> </xsl:call-template> <xsl:text> DBMS_STATS.SET_INDEX_STATS('"' || ind_owner || '"', '"' || ind_name || '"', </xsl:text> <xsl:choose> <!-- If supplied, use SUBNAME, otherwise, insert "NULL" --> <xsl:when test="$Node/SCHEMA_OBJ/SUBNAME"> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Node/SCHEMA_OBJ/SUBNAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Node/SUBNAME"/> <xsl:with-param name="Leading">'"</xsl:with-param> <xsl:with-param name="Trailing">"', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> </xsl:otherwise> </xsl:choose> <xsl:text>NULL, NULL, </xsl:text> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="ROWCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="LEAFCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="DISTKEY"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="LBLKKEY"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="DBLKKEY"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="CLUFAC"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="BLEVEL"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="IND_FLAGS"/> <xsl:with-param name="Trailing"> + 4, NULL, FALSE, NULL, </xsl:with-param> <xsl:with-param name="NullVal">0 + 4, NULL, FALSE, NULL, </xsl:with-param> </xsl:call-template> <!-- If supplied, use CACHEDBLK, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="CACHE_INFO/CACHEDBLK"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- If supplied, use CACHEHIT, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="CACHE_INFO/CACHEHIT"/> <xsl:with-param name="Trailing">); </xsl:with-param> <xsl:with-param name="NullVal">NULL); </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>END; </xsl:text> <xsl:call-template name="DoTerminator"> <xsl:with-param name="Text"/> </xsl:call-template> </xsl:template> <xsl:template name="InsertIndStats"> <xsl:param name="Base"/> <xsl:param name="Node"/> <!-- Get the index name --> <xsl:call-template name="GetIndex"> <xsl:with-param name="Base" select="$Base"/> <xsl:with-param name="Node" select="$Node"/> </xsl:call-template> <xsl:text> INSERT INTO "</xsl:text> <xsl:call-template name="InsertStatTabSchema"/> <xsl:text>"."</xsl:text> <xsl:call-template name="InsertStatTabName"/> <xsl:text>" (type, version, flags, c1, c2, c3, c5, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, d1, cl1) VALUES ('I', 5, </xsl:text> <xsl:value-of select="IND_FLAGS"/> <xsl:text>, </xsl:text> <!-- c1 is index name --> <xsl:text>IND_NAME, </xsl:text> <!-- c2 is index partition name --> <!-- If supplied, use Partname, otherwise, insert "NULL" --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Node/PARTNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- c3 is index subpartition name --> <!-- If supplied, use subpartname, otherwise, insert "NULL" --> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Node/SUBPARTNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- c5 is the owner name --> <xsl:text>IND_OWNER, </xsl:text> <!-- n1 is number of rows --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="ROWCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n2 is number of leaf blocks --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="LEAFCNT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n3 is number distinct keys --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="DISTKEY"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n4 is average # leaf blocks "lblkkey" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="LBLKKEY"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n5 is average # data blocks DBLKKEY --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="DBLKKEY"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n6 is clufaq --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="CLUFAC"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n7 is blevel --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="BLEVEL"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n8 is sample_size or null--> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="SAMPLE_SIZE"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n9 is ???guessq??? --> <xsl:text>NULL, </xsl:text> <!-- n10 cached block --> <!-- If supplied, use CACHEDBLK, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="CACHE_INFO/CACHEDBLK"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n11 is cache hit ratio --> <!-- If supplied, use CACHEHIT, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="CACHE_INFO/CACHEHIT"/> <xsl:with-param name="Trailing">, </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <!-- n12 is ???lgrds??? --> <xsl:text>NULL, </xsl:text> <!-- d1 is last analyzed --> <xsl:variable name="BADTIME">0000-00-00</xsl:variable> <xsl:choose> <xsl:when test="contains(ANALYZETIME, $BADTIME)"> <xsl:text>NULL, </xsl:text> </xsl:when> <xsl:otherwise> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="ANALYZETIME"/> <xsl:with-param name="Leading">TO_DATE('</xsl:with-param> <xsl:with-param name="Trailing">', 'YYYY-MM-DD:HH24:MI:SS'), </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> </xsl:otherwise> </xsl:choose> <!-- cl1 is the new CLOB for 11.1 --> <!-- If supplied, use CLOB, otherwise, insert "NULL" --> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="CLOB"/> <xsl:with-param name="Trailing">); </xsl:with-param> <xsl:with-param name="NullVal">NULL);</xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> </xsl:template> <xsl:template name="GetIndex"> <xsl:param name="Base"/> <xsl:param name="Node"/> <!-- If there is a system generated index, determine the index name --> <xsl:choose> <xsl:when test="CNST_COL_LIST/CNST_COL_LIST_ITEM"> <xsl:for-each select="CNST_COL_LIST/CNST_COL_LIST_ITEM"> <xsl:text> colname(</xsl:text> <xsl:value-of select="position()"/> <xsl:text>) := </xsl:text> <xsl:call-template name="GetColName"> <xsl:with-param name="Base" select="$Base/../BASE_TAB_OBJ"/> <xsl:with-param name="ColNode" select="."/> </xsl:call-template> <xsl:text>;</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> </xsl:for-each> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> DBMS_METADATA.GET_STAT_INDNAME(</xsl:text> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/../BASE_TAB_OBJ/OWNER_NAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/../BASE_TAB_OBJ/NAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', colname, </xsl:with-param> </xsl:call-template> <xsl:value-of select="COLS"/> <xsl:text>, ind_owner, ind_name); </xsl:text> </xsl:when> <xsl:otherwise> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/NAME"/> <xsl:with-param name="Leading"> ind_name := '</xsl:with-param> <xsl:with-param name="Trailing">'; </xsl:with-param> </xsl:call-template> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/OWNER_NAME"/> <xsl:with-param name="Leading"> ind_owner := '</xsl:with-param> <xsl:with-param name="Trailing">'; </xsl:with-param> </xsl:call-template> </xsl:otherwise> </xsl:choose> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> </xsl:template> <!-- Index stats specific templates complete --> <!-- generic stats specific templates start here --> <xsl:template name="InsertStatTabSchema"> <xsl:choose> <xsl:when test="$EXPORT=1"> <xsl:text>SYS</xsl:text> </xsl:when> <xsl:when test="string-length($STAT_SCHEMA)!=0"> <xsl:value-of select="$STAT_SCHEMA"/> </xsl:when> <xsl:otherwise> <xsl:text>your_schema</xsl:text> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="InsertStatTabName"> <xsl:choose> <xsl:when test="$EXPORT=1"> <xsl:text>IMPDP_STATS</xsl:text> </xsl:when> <xsl:when test="string-length($STAT_TABLE)!=0"> <xsl:value-of select="$STAT_TABLE"/> </xsl:when> <xsl:otherwise> <xsl:text>your_stats_table</xsl:text> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="GetColName"> <xsl:param name="Base"/> <xsl:param name="ColNode"/> <xsl:choose> <xsl:when test="($ColNode/DEFAULT_VAL) or ($ColNode/ATTR_COLNAME)"> <xsl:text> DBMS_METADATA.GET_STAT_COLNAME(</xsl:text> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/OWNER_NAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$Base/NAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$ColNode/DEFAULT_VAL"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$ColNode/ATTR_COLNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">', </xsl:with-param> <xsl:with-param name="NullVal">NULL, </xsl:with-param> </xsl:call-template> <xsl:call-template name="ValueOrNull"> <xsl:with-param name="OrigStr" select="$ColNode/NESTED_TABLE"/> <xsl:with-param name="Trailing">)</xsl:with-param> <xsl:with-param name="NullVal">0)</xsl:with-param> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:choose> <xsl:when test="$ColNode/COLNAME"> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$ColNode/COLNAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">'</xsl:with-param> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="EscapeString"> <xsl:with-param name="OrigStr" select="$ColNode/NAME"/> <xsl:with-param name="Leading">'</xsl:with-param> <xsl:with-param name="Trailing">'</xsl:with-param> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="ValueOrNull"> <xsl:param name="OrigStr"/> <xsl:param name="Leading"/> <xsl:param name="Trailing"/> <xsl:param name="NullVal"/> <xsl:choose> <xsl:when test="string-length($OrigStr)=0"> <xsl:value-of select="$NullVal"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="$Leading"/> <xsl:value-of select="$OrigStr"/> <xsl:value-of select="$Trailing"/> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- generic stats specific templates complete --> </xsl:stylesheet>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de