ORACLE EXPERT

materialized views: a brief introduction of materialized views makes sense in this chapter about views. the intent of this section is to illustrate the concept of materialized views. normally,materialized views are mainly used in complex data warehousing environment where the tables grow so big that the data volume cause unacceptable performance problems. an important property of data warehousing environments is that you donot change the data very often. typically, there is a separate extraction,transformation,loding process that updates the data warehousing contents. materialized views are also  often used with distributed database. in such environments, accessing data over the network can become a performance bottleneck. you can use materialized views to replicate data in a distributed database.

sql*plus supports the following three variable types: substitution variables,user-defined variables and system varibales. substitution variables appear in sql or sql*plus commands. sql*plus prompts for a value when you execute those commands.substitution variable values are volatile; that is ,sql*plus does not remember them and does not store them anywhere. this is what distinguished substitution variables from the other two types. the default character that makes sql*plus prompt for a substitution variable value is the amperhand also known as the define character.
bind variables are extremely important if you want to develop database application for critical infromation systems. the first step is setting up an obejct-relational environmenty is the definition of the appropriate collection of object types and methods.. once you have defined your object types, you can use them to create object tables,thus crearing a truely object relational environment.

collection datatypes are a special case of user-defined datatypes. collection datatypes support attributes that can have multiple values. an importent difference is that nested tables require one extra table. you createa type and then use it to define arrays. for nested tables,you first create a type, then you create a table type based on that type, and then you create a nested table based on that table type.

the base tables of the oracle data dictionary are stored in the sys schema;in other words,use sys is owner of the data dictioanry. when you create a new database, the oracle dnms creates and populates the sys schema automatically. you should never direnctly manipulate the tables of the sys schema. these tables are maintained by the oracle dbms. all views have infromation about all accessible data. user views have information about your own data. dba views have database-wide information. v$ views are dynamic performance views. you need dba privileges to access these views. the first three categories are commonly referred to as static to distinguish them from fourth category, the dynamic performance views.

fiels- we will go through the set of five files that make uo the database and the instance. these are the paramater.data,temp and redo log files. memory structures referred to as the system global area  physical processes or threads, we will go through the three different types of processes that will be running on the database server processes,backgound processes and slave processes. the relationship between the database abd instance is that a database may be mounted and opened by many instances.  an instance may mount and opne a singel databse at any point in time. the database that an instance opens and mounts do not  have to be the same every time it is started. an instance is simply a set of operating system processes and some memory. they can operate on a database, a databse just beinf a collection of diles(data files,temporary files,redo log files,control files.) oracle has a large chunk of memory call the sda where it will store many internal data structures that all processes need access to;cache data from disk,cache redo data before writing it to disk,hold parsed sql plans and so on . oracle has a set of processes that are attached to this sga and the mechanism by which they attach differs by operating system. in a unix environment,they will physically attach to a large shared memory segement- a chunk of memory allocated in the os that may be accessed by many processes concurrently. under windows, they simply use the c call malloc() to allocate the memoory since they are reallly threads  in one big processes.
the hierarchy of storage in oracle is as follow: a dababse is made up of one or more tablsepaces, a tablespace is made up of one or more data files. a tablespace contains segments; a segement is made up of one or more extents. a segement exists in a tablespace,but may have data in many data files within that tablespace. an extent is a contigous set of blocks on disk.an extent is in  a single tablsespace and furthermore,is always in a single file within that tablespace. a block is the smallest unit of allocation in the database. a block is the smallest unit of i/o used by the database. sga system global area this is a large shared memory segment that virtually all oracle prcocess will access at one point or another. pga process global area this is memory which is private to a singlle process or thread and is not accessibel from other processes /thread. uga user global area this is memory associated with your session. it will be found either in the sga or the pga depending on whether you are running in mts mode. the sga is broken up into various pools .they are :java pool, the java pool is a fixed amount of memory allocated for the jvm running in the database;  large pool the large pool is used by the mts for session memory. shared pool the shared pool contains shared cursors,stored procedures,state objects,dictionary caches and many dozen of other bits of data. the null pool this one does not really have a name. it is the memory dedicated to block buffers . a lock is a mechnism used o regulate concurrent access to a shared resource. locks are used in the database to permit concurrent access to these share resources while at the same time providing data integrity and consistency.
oracle provides many diferent types of indexes for us to use. breifly they are as follows: b*tree indexed these are what i refer to as conventional indexes. they are by far the most common indexes in use in oracle and most other databasees. similar in construct to a binary tree, they provide fast access, by key to an individual row or range of rows normally requiring few reads to finds the corrent row. index organized tables a table stored in a b* tree structure.b*tree indexes are the most commonly used type of indexing structure in the database. they are similar in implementation to a binary search tree. their goal is to minimizer the amount of time oracle spends searching for data. exp and imp are tools you will probably use at some point or another. a transportable tablespace is a mechanism for taking the formatted datafiles of one database and attaching them to another. instead of unloading the data from  one database to a flat file,or a dmp file and inserting that data into the other database,transporting a tablespace lets you move the data as fast as you can copy files. there are some restrictions on transporting the tablepspace,namely: the dource and target databse must be running on the same hardware platforms. the source database must not have a table by the same name.  we can use exp to generate mucj of the ddl from our databse.