OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P14

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P14: There is high demand for professionals in the information technology (IT) industry, andOracle certifications are the hottest credential in the database world. You have made theright decision to pursue certification, because being Oracle Database 11g certified will giveyou a distinct advantage in this highly competitive market.
Nội dung trích xuất từ tài liệu:
OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P14 Working with Schema Objects 581 3 FROM USER_CONSTRAINTS 4 WHERE TABLE_NAME LIKE ‘CUST%’;CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME-------------------- - -------------------- --------------------SYS_C002792 C CUSTOMER_ADDRESSESSYS_C002793 C CUSTOMER_ADDRESSESSYS_C002794 C CUSTOMER_ADDRESSESSYS_C002795 C CUSTOMER_ADDRESSESPK_CUST_ADDRESSES P CUSTOMER_ADDRESSESFK_CUST_ADDRESSES R CUSTOMER_ADDRESSES PK_CUSTOMER_MASTERCK_ADD_TYPE2 C CUSTOMER_ADDRESSESCK_ADD_TYPE C CUSTOMER_MASTERPK_CUSTOMER_MASTER P CUSTOMER_MASTERUQ_CUST_EMAIL U CUSTOMER_MASTERPK_CUST_REFS P CUSTOMER_REFERENCESSYS_C002804 R CUSTOMER_REFERENCES PK_CUSTOMER_MASTERSYS_C002805 R CUSTOMER_REFERENCES PK_CUSTOMER_MASTER13 rows selected.SQL>SQL> SELECT CONSTRAINT_NAME, GENERATED, INDEX_NAME 2 FROM USER_CONSTRAINTS 3 WHERE TABLE_NAME LIKE ‘CUST%’;CONSTRAINT_NAME GENERATED INDEX_NAME-------------------- -------------- --------------------SYS_C002792 GENERATED NAMESYS_C002793 GENERATED NAMESYS_C002794 GENERATED NAMESYS_C002795 GENERATED NAMEPK_CUST_ADDRESSES USER NAME PK_CUST_ADDRESSESFK_CUST_ADDRESSES USER NAMECK_ADD_TYPE2 USER NAMECK_ADD_TYPE USER NAMEPK_CUSTOMER_MASTER USER NAME PK_CUSTOMER_MASTERUQ_CUST_EMAIL USER NAME CUST_EMAILPK_CUST_REFS USER NAME PK_CUST_REFSSYS_C002804 GENERATED NAMESYS_C002805 GENERATED NAME13 rows selected.SQL>582 Chapter 10 N Allocating Database Storage and Creating Schema ObjectsSummaryThis chapter discussed the most important aspect of the Oracle Database: storing data.You learned to create both tablespaces and data files as well as to create schema objects thatstore the data. You found out how to create and manage tablespaces as well as how Oraclestores some schema objects as segments that are comprised of extents and data blocks. Inaddition, you learned how to create and modify tables, indexes, and constraints. I also cov-ered deferred constraint checking and how to configure foreign key constraints to supporteither deferrable or not deferrable implementations. A data file belongs to one tablespace, and a tablespace can have one or more data files.The size of the tablespace is the total size of all the data files belonging to that tablespace.The size of the database is the total size of all tablespaces in the database, which is the sameas the total size of all data files in the database. Tablespaces are logical storage units used togroup data depending on their type or category. Understand the relationship between datafiles and tablespaces because that is important information to know for the certification. Tablespaces can handle the extent management through the Oracle dictionary or locallyin the data files that belong to the tablespace. Locally managed tablespaces can have uni-form extent sizes; this reduces fragmentation and wasted space. You can also make Oracledo the entire extent sizing for locally managed tablespaces. A temporary tablespace is used only for sorting; no permanent objects can be created in atemporary tablespace. Only one sort segment will be created for each instance in the tempo-rary tablespace. Multiple transactions can use the same sort segment, but one transaction canuse only one extent. Although temporary files are part of the database, they do not appear inthe control file, and the block changes do not generate any redo information because all thesegments created on locally managed temporary tablespaces are temporary segments. You learned about tables, indexes, and constraints in this chapter. Also study Chapters6 and 7 before taking the certification exam. Tables are created using the CREATE TABLEcommand. By default, the table will be created in the current schema. To create the tablein another schema, you should qualify the table with the schema name. Storage parameterscan be specified when creating the table. Tables can be moved or reorganized using theMOVE clause. Indexes can be created as B-tree or bitmap. Bitmap indexes save storage space for low-cardinality columns. You can create reverse key or function-based indexes. An index-organized table stores the index and row data in the B-tree structure. Tablespace and storageshould be specified when creating indexes. Indexes can be created ONLINE; that is, the tablewill be available for insert/update/delete operations while the indexing is in progress. TheREBUILD clause of the ALTER INDEX command can be used to move the index to a differenttablespace or to reorganize the index. Constraints are ...