Creating Directory SQL> create or replace directory DW_DMP as ‘/u02/expdp’; SQL> GRANT READ, WRITE ON DIRECTORY DW_DMP to expdpadmin; To check on which directories user have privilege to read & write: ################################################################# SQL> SELECT grantee,privilege, directory_name,directory_path FROM dba_tab_privs t, all_directories d WHERE t.table_name(+)=d.directory_name and directory_name like ‘DW%’ ORDER BY 3;
Archive for the ‘Schema Objects’ Category
29 Apr
When to Rebuild Indexes
How can you determine if an index needs to be dropped and rebuilt? Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the [...]
15 Apr
Managing Indexes
BTree Index Creation CREATE INDEX hr.employees_last_name_idx ON hr.employees(last_name) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx; Bitmap Index Creation CREATE BITMAP INDEX orders_region_id_idx ON orders(region_id) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx; Index Guidelines • Balance query and DML needs • Place in separate tablespace [...]
15 Apr
Bitmap and BTree Indexes
According to conventional wisdom, Bitmap index is a preferred indexing technique for cases where the indexed attributes have few distinct values (i.e., low cardinality). The query response time is expected to degrade as the cardinality of indexed columns increase due to a larger index size. On the other hand, B-tree index is good if the [...]
12 Mar
Create Statistics
Analyze command The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied in DBMS_STATS.GATHER_SCHEMA_STATS. The analyze table can be used to create statistics for 1 table, index or cluster. Syntax: ANALYZE table tableName {compute|estimate|delete) statistics options ANALYZE table indexName {compute|estimate|delete) statistics options ANALYZE cluster [...]
10 Mar
Managing Tables
Creating a Table The CREATE TABLE command is used to create relational tables or object tables. Relational table: This is the basic structure to hold user data. Object table: Is a table that uses an object type for a column definition. An object table is a table that is explicitly defined to hold the object [...]
10 Mar
Types of Tables
Regular table: A regular table (generally referred to as a “table”) is the most commonly used form of storing user data. This is the default table and is the main focus of discussion in this lesson. A database administrator has very limited control over the distribution of rows in a table. Rows can be stored [...]
10 Mar
Row Format
Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row’s data cannot be inserted into a single [...]
10 Mar
ROWID Format
ROWID Format Extended ROWID An extended ROWID needs 10 bytes of storage on disk and is displayed by using 18 characters. It consists of the following components: •Data object number: Is assigned to each data object, such as table or index when it is created, and it is unique within the database •Relative file number: [...]
