The STATDEPTH function returns the number of status lists that Oracle OLAP has saved for a specified dimension. The current status list of a dimension is at the top of the stack which means that it has a depth of 1 (one.

Return Value



STATDEPTH (dimension)



The name of the dimension for which you want to retrieve information.


What is a Status List Stack?

A status list stack for a dimension is a stack of the dimension statuses that Oracle OLAP uses to manage status when executing PUSH and POP statements. The depth of the list varies depending on what Oracle OLAP statements have executed:

  • When you first attach an analytic workspace, the current status of each dimension is ALL and the status list stack for each dimension has a depth of one (that is, there is only one status list in the stack).

  • The depth of the status list stack for a dimension stays at one (for the current status list) no matter how often current status changes (that is, no matter how many LIMIT commands execute against it), except in the following situations:

    • Oracle OLAP executes a PUSH statement for the dimension. After a PUSH statement executes, each time a LIMIT command executes for a pushed dimension, Oracle OLAP adds status list to the status list stack for that dimension. A POP statement for the dimension originally pushed, clears the status list stack for the dimension. The status list stack for the dimension, once again becomes one.

    • Oracle OLAP executes a statement (like CHGDIMS or TEMPSTAT) that temporarily changes the status of the dimension. In this case, Oracle OLAP adds a status list to the status list stack during the time the statement is in effect, and removes it immediately afterward. You can only access the status list from the status list stack while the temporary status is in effect.