Oracle 10g object management

In Oracle databases, tables, indexes, stored procedures, and functions are all objects.
The schema name is the same as the user name, but not the same as the user. If the user does not have any objects, the schema does not exist.
I. Table
ORACLE tables fall into four categories:
Normal table --- a table corresponds to a segment
Partition Table virtual Table, no corresponding segment
Index organization Table Index Organized Table (IOT virtual Table)
Cluster table-a virtual table first creates a cluster. A cluster corresponds to a segment.

1. Normal table
1) In the tablespace section, set the logging attribute of the table. Yes indicates that the redo entries are generated for table DML operations and No is generated.
2) In the Extents section, set the Initial size.
3) In Space Usage, PCTFREE is set. The default value is 10. If the available Space of the data block is lower than 10%, insert is not allowed for the data block. PCTUSED indicates under which conditions can be inserted. The default value is 40%, indicating
When the data block space is less than 40%, the data block can be inserted again. When ASSM is enabled, PCTUSED cannot be set.
2. rowid is a pseudo column. Each record has a pseudo rowid column.
Corresponding to ORACLE10g, rowid format: OOOOOOFFFBBBBBBRRR
OOOOOO: the object number of the Data row
FFF: The relative file number of the Data row.
BBBBBB: The data block number of the Data row
RRR: the row number in the data block where the data row is located
The rowid is in 64-digit format, that is, ~ Z ~ Z 0 ~ 9/+ 64 characters.
A ~ Z: 0 ~ 25
A ~ Z: 26 ~ 51
0 ~ 9: 52 ~ 61
/: 62
+: 63

Example rowid = AAAM0hAAEAAAAGnAAA
SQL> select object_id from user_objects where object_name = 'books '; -- object number
SQL> select dbms_rowid.rowid_relative_fno (rowid) as "File No" from dual; -- File number
SQL> select dbms_rowid.rowid_block_number (rowid) as "File No" from dual; -- block number
SQL> select dbms_rowid.rowid_row_number (rowid) as "File No" from dual; -- row number

3. Manage common tables
1) extended tables: sometimes it is necessary to expand the space occupied by the table or distribute the table data to multiple files to distribute the table I/O to multiple disks.
SQL> alter table books allocate extent (size 1 M datafile '/u01/app/El/oradata/ora10g/users02.dbf ');
2) Reorganization table-removes block-level fragments from the currency table.
Causes of sparse Table Generation: There are many insert, delete,
In the table's segment header, a value is recorded, called the High Water Mark (HWM), indicating the position of the last data block used by the current segment.
Note: after the table is deleted, the HWM position will not change. During a full table scan, the HWM is still scanned. Before ORACLE10g, use move or export to import the table to reduce the WHM, as shown below:
SQL> alter table books move tablespace example; -- move the table to the example tablespace. If no tablespace is added, the corresponding tablespace is reorganized in the current table. After the reorganization, the entire table index fails.
ORACLE 10 Gb can use shrink to contract tables.
3) shrink the table
Condition: The table's tablespace must use ASSM (automatic segment space management)
The row movement option must be enabled to migrate data rows in different data blocks due to table shrinking.
SQL> alter table t enable row movement;
Shrink TABLE statement:
SQL> alter table t shrink space compact;
SQL> alter table t shrink space; -- only perform the compression phase on table t to decrease the HWM
SQL> alter table t shrink space cascade; -- the table t is only compressed to reduce HWM, and other segments related to table t are also reduced.
Note: You can use the Segment Advisor to help which segment can be shrunk.
4) Table Truncation
The truncate command is a DDL command, and then the WHM is minimized.
SQL> truncate table t;
Note: sometimes it takes a long time to truncate a large table, so that the table cannot be used for a long time. When the data is truncated, all data blocks are not released immediately after the data dictionary is updated, but the WHM has dropped to the lowest level.
You can release data blocks multiple times in the idle system to release part of the space each time. The command is as follows:
SQL> truncate table t1 reuse storage;
SQL> alter table t1 deallocate unused keep 30 M; -- release unused data blocks of table t1 to 30 M of the remaining table space.
SQL> alter table t1 deallocate unused keep 15 M;
SQL> alter table t1 deallocate unused keep 0 M;
NOTE: If other users have inserted data into t1, data blocks that do not exist are released.
5) delete a table
Deleting a table is a DDL command that updates the data dictionary information. ORACLE does not read the data block information contained in the table. Therefore, the table can be deleted even if it is in a read-only tablespace.
SQL> drop table t;
SQL> drop table t cascade constraints;
Add the cascade constraints option and delete the foreign key that references table t.
6) modify or delete a column
SQL> alter table t rename column to code;
SQL> alter table t drop column code;
SQL> alter table t drop column code cascade constraints; -- delete both referenced Foreign keys
Note: During column deletion, oracle will consume undo tablespaces. If there are many records, it will consume too many undo tablespaces.
SQL> alter table t drop column code cascade constraints checkpoint 2000;
Checkpoint 2000 indicates that each 2000 records are submitted once to release undo resources.
Note: During the column deletion process, oracle locks the table and cannot perform DML operations on the table. If the data volume is large, it takes a long time. During peak business hours, the impact is serious.
SQL> alter table t set unused column code;
SQL> select * from user_unusedd_col_tabs; ---- invalid columns can be queried.
You can invalidate the column logically and delete the invalid column physically in the off-peak hours.
SQL> alter table t drop umused columns;
SQL> alter table t drop umused columns checkpoint 2000;

4. constraints
The ORACLE database has the following 5 constraints:
1) non-null not null --- in essence, not null belongs to check: col_name is not null
2) unique
3) primary key
4) foreign key
5) check

Constraint status:
1) enable and disable: when inserting or modifying a table, check the inserted or modified data to determine whether it violates the constraints.
2) validate and novalidate: checks the existing data in the table to determine whether the data violates the constraints.
The preceding combination has four states.
SQL> alter table books enable validate constraint pk_books;
SQL> alter table books enable novalidate constraint pk_books;
SQL> alter table books rename constraint pk_books to pk_books_id;

Timing of Constraints Verification
Latency constraint deferred constraint: the constraint is verified when it is submitted.
1) deferrable: indicates whether the constraint can be delayed. Adding this option indicates that the constraint can be delayed.
2) initially deferred or initially immediate: specifies when the data is verified when the constraint is created.
Initially deferred: Submit the verification. Deferrable must be set for this option.
Initially immediate: default value. check now.
SQL> alter table sales add constraint chk_sales check (price * qty = value) deferrable initially deferred;

SQL> alter session set constraint = deferred;
When this statement is issued, it indicates that in the current session, all the tables involved in the issued DML statements, as long as the constraints on these tables define the deferrable option, all of these constraints are delay checks.

5. Use partition tables, index tables, and cluster tables
Oracle 10 Gb provides five partitioning Methods
1) Range Partition
Create table t2 (id number, createdate date)
Partition by range (createdate)
(
Partition p1 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace ts01,
Partition p2 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace ts02,
Partition p3 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace ts03,
Partition pmax values less than (maxvalue) tablespace ts04
);
2) Hash Partition
Create table t3 (id number, name varchar2 (10 ))
Partition by hash (id)
Partitions 4
Store in (ts01, ts02.ts03. ts04 );
Create table t3 (id number, varchar2 (10 ))
Partition by hash (id)
(
Partition p1 tablespace ts01,
Partition p2 tablespace ts02,
Partition p3 tablespace ts03,
Partition p4 tablespace ts04
):
3) List Partition
Create table t4 (id number, name varchar2 (10), category varchar2 (10 ))
Partition by list (category)
(
Partition p1 values ('01', '02') tablespace ts01,
Partition p2 values ('03', '04 ') tablespace ts02,
Partition p3 values ('05 ', '06', '07') tablespace ts03,
Partition p4 values (default) tablespace ts04,
);
4) Range Hash combined Partition Range-Hash Partition
Create table t5 (id, number, name varchar2 (10), createdate date)
Partition by range (createdate)
Subpartition by hash (id)
Subpartitions 4 store in (ts01, ts02)
(Partition p1 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition p2 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition p3 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition pmax values less than (maxvalue)
Subpartitions 2 store in (ts03 ));
Note: range partitioning is performed by createdate, and then hash partitioning is performed by id. By default, each partition contains four hash subpartitions, which are in ts01 and ts02 respectively.
This is also true for p1, p2, and p3, but for pmax, the default settings are modified. pmax has two hash partitions, both of which are located in ts03.
5) Range List combined Partition Range-List Partition
Create table t6 (id number, name varchar2 (10), category varchar2 (10), createdate date)
Partition by range (createdate)
Subpartition by list (category)
(Partition p1 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace ts01
(Subpartition p1_1 values ('01', '02 '),
Subpartition p1_2 values ('03', '04 '),
Subpartition p1_3 values (default) tablespace ts02 ),
Partition p2 values less than (maxvalue) tablespace ts03
(Subpartition p1_1 values ('01', '02 '),
Subpartition p1_2 values ('03', '04 '),
Subpartition p1_3 values (default) tablespace ts04)
);

Index organization table index organized table