| General |
Table Related Data Dictionary Objects |
| col$
|
tab$
|
|
| |
|
|
| DBA |
ALL |
USER |
| dba_col_comments |
all_col_comments |
user_col_comments |
| dba_external_tables |
all_external_tables |
user_external_tables |
dba_external
_locations |
all_external
_locations |
user_external
_locations |
dba_partial_drop
_tabs |
all_partial_drop
_tabs |
user_partial_drop
_tabs |
| dba_tables |
all_tables |
user_tables |
| dba_tab_cols |
all_tab_cols |
user_tab_cols |
| dba_tab_columns |
all_tab_columns |
user_tab_columns |
dba_tab_col_
statistics |
all_tab_col
_statistics |
user_tab_col
_statistics |
| dba_tab_comments |
all_tab_comments |
user_tab_comments |
| dba_tab_histograms |
all_tab_histograms |
user_tab_histograms |
dba_tab
_modifications |
all_tab
_modifications |
user_tab
_modifications |
| dba_tab_privs |
all_tab_privs |
user_tab_privs |
| dba_tab_statistics |
all_tab_statistics |
user_tab_statistics |
dba_tab_stats
_history |
all_tab_stats
_history |
user_tab_stats
_history |
| dba_unused_col_tabs |
all_unused_col_tabs |
user_unused_col_tabs |
|
Table Related System Privileges |
| alter any table |
flashback any table |
| create any table |
insert any table |
| comment any table |
lock any table |
| create table |
select any table |
| delete any table |
under any table |
| drop any table |
update any table |
|
Types Of Tables |
|
Table Type |
Description |
| Heap |
Default Oracle table type |
| Clusters |
One or more tables in a single database
block |
| External |
External files readable as tables |
| Index Organized |
Merger between a table and an index |
| Partitions |
Partition/Subpartitioned by hash, range, or list |
| XML |
XML Tables |
|
| |
| Create Global Temporary Table |
Global temporary tables have two major
benefits:
1. Non-interference between private sets of data.
2. Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete
it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the
implicit "truncate on commit / exit" effect to do
the same thing).
3. Decreased redo generation as, by definition, they are non-logging.
However:
Mixing temporary tables (GTTs) with permanent tables usually causes some grief to the CBO. It has no information
about the number of rows in the GTT, and therefore guesses (badly).
Even if you analyze table .. or dbms_stats.gather_table_stats() you don't get stats on the temporary table.
Set the init parameter dynamic_sampling
to at least 2 for GTTs to be sampled at run-time.
Note:
All DDL includes two implicit commits so any rows in a GTT specified
with ON COMMIT DELETE ROWS will empty the table. |
Create Global Temporary Table That Empties On
Commit |
You can not specify a tablespace with global
temporary tables. GTT's are built in the TEMP tablespace.
CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>);
-- or explicitly specifying the ON COMMIT action
CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>,
<column_name> <column_data_type>,
<column_name> <column_data_type>)
ON COMMIT DELETE ROWS;
|
CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE);
-- or explicitly specifying the ON COMMIT action
CREATE GLOBAL TEMPORARY
TABLE gtt_zip2 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE)
ON COMMIT DELETE ROWS;
set linesize 121
SELECT table_name, tablespace_name, temporary, duration
FROM user_tables;
INSERT INTO gtt_zip1
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);
SELECT *
FROM gtt_zip1;
COMMIT;
SELECT *
FROM gtt_zip1; |
Create Global Temporary Table That Empties
At End Of Session |
You can not specify a tablespace with global
temporary tables. GTT's are built in the TEMP tablespace.
CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>,
<column_name> <column_data_type>,
<column_name> <column_data_type>)
ON COMMIT PRESERVE ROWS; |
CREATE GLOBAL TEMPORARY TABLE gtt_zip3 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE)
ON COMMIT PRESERVE ROWS;
set linesize 121
SELECT table_name, tablespace_name,
temporary, duration
FROM user_tables;
INSERT INTO gtt_zip3
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);
SELECT *
FROM gtt_zip3;
COMMIT;
SELECT *
FROM gtt_zip3;
-- log on as a different user
-- log back on as original user
SELECT *
FROM gtt_zip3; |
GTT Demo |
CREATE OR REPLACE PACKAGE redo_diff IS
PROCEDURE diff_it;
END redo_diff;
/
CREATE OR REPLACE PACKAGE BODY redo_diff IS
s NUMBER;
--=========================================
FUNCTION get_size RETURN NUMBER IS
s_ NUMBER;
BEGIN
SELECT value
INTO s_
FROM sys.v_$sysstat
WHERE name = 'redo size';
RETURN s_;
END get_size;
--=========================================
PROCEDURE diff_it IS
s_new NUMBER;
BEGIN
s_new := get_size;
dbms_output.put_line('redo diff: ' || to_char(s_new - s));
s := s_new;
END diff_it;
BEGIN
s := get_size;
END redo_diff;
/
CREATE OR REPLACE TYPE subst_ AS OBJECT (
rn number,
ob varchar2(128));
/
CREATE OR REPLACE TYPE subst_t_ AS TABLE OF subst_;
/
-- To create the all_objs table: Click
Here
set serveroutput on
DECLARE
t subst_t_;
time1_ number;
time2_ number;
sz1_ number;
sz2_ number;
BEGIN
redo_diff.diff_it;
time1_ := dbms_utility.get_time;
-- Filling 500000 records
SELECT CAST(MULTISET(SELECT rownum,a.object_name
FROM all_objs a,all_objs b,all_objs c,all_objs d
WHERE rownum <= 500000) AS subst_t_)
INTO t
FROM dual;
sz1_ := t.count;
time2_ := dbms_utility.get_time;
dbms_output.put_line('filled ' || sz1_ || ' records, time used: '
|| TO_CHAR((time2_ - time1_)/100, '99999.00') || ' secs');
redo_diff.diff_it;
-- deleting approx 50%
SELECT CAST(MULTISET(SELECT rn, ob
FROM TABLE(CAST(t AS subst_t_))
WHERE SUBSTR(ob,1,2) > 'DB') AS subst_t_)
INTO t
FROM dual;
sz2_ :=t.count;
time1_ := dbms_utility.get_time;
dbms_output.put_line('deleted ' || to_char(sz1_ - sz2_) ||
' records, time used: ' || TO_CHAR((time1_-time2_)/100, '99999.00')
|| ' secs');
redo_diff.diff_it;
END;
/
drop type subst_t_;
drop type subst_; |
| |
| Commenting |
| Comment a table |
COMMENT ON TABLE <table_name> IS
'<comment>'; |
COMMENT ON TABLE
gtt_zip1 IS 'US Postal Service Zip Codes';
SELECT table_name, comments
FROM user_tab_comments
WHERE comments IS NOT NULL; |
| Comment a column |
COMMENT ON COLUMN <table_name.column_name>
IS '<comment>'; |
COMMENT ON COLUMN
gtt_zip1.zip_code IS '5 Digit Zip Code';
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE comments IS NOT NULL; |
| |
| Alter Table Column
Clauses |
| Add a new column |
ALTER TABLE <table_name>
MODIFY (<field_name data_type>); |
desc gtt_zip1
ALTER TABLE gtt_zip1
ADD (map_id NUMBER(10));
desc gtt_zip1 |
| Add More Than One New Column |
ALTER TABLE <table_name>
MODIFY (<field_name data_type>, <field_name data type>); |
ALTER TABLE
gtt_zip1 ADD (map_coor VARCHAR2(10),
map_ver VARCHAR2(3));
desc gtt_zip1 |
| Rename A Column |
ALTER TABLE <table_name>
RENAME COLUMN <current_name> TO <new_name>; |
ALTER TABLE
gtt_zip1
RENAME COLUMN map_coor TO map_coord;
desc gtt_zip1 |
| Drop A Column |
ALTER TABLE <table_name>
DROP COLUMN <column_name>; |
ALTER TABLE
gtt_zip1
DROP COLUMN map_coord;
desc gtt_zip1 |
| Alter Table Change Data Type |
ALTER TABLE <table_name>
MODIFY (<column_name new_data_type); |
desc gtt_zip1
ALTER TABLE gtt_zip1 MODIFY (zip_code
VARCHAR2(6));
desc gtt_zip1 |
| Alter Table Change Data Type Multiple
Fields |
ALTER TABLE <table_name>
MODIFY (<column_name> <data_type>,
<column_name> <data_type>); |
desc gtt_zip1
ALTER TABLE gtt_zip1
MODIFY (zip_code VARCHAR2(7), entry_Date TIMESTAMP WITH TIME
ZONE);
desc gtt_zip1 |
| |
| Drop Table |
| Drop Table Into Recycle Bin |
DROP TABLE <table_name>; |
| DROP TABLE
gtt_zip1; |
| |
| Foreign Key
Constraints |
GTT and Foreign Key |
CREATE TABLE ptemp
(
pid NUMBER(5),
zip_code VARCHAR2(5));
ALTER TABLE ptemp
ADD CONSTRAINT pk_ptemp_pid
PRIMARY KEY (pid);
CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE);
ALTER TABLE gtt_zip1
ADD CONSTRAINT pk_gtt_zip1
PRIMARY KEY (zip_code);
ALTER TABLE ptemp
ADD CONSTRAINT fk_ptemp_gtt
FOREIGN KEY (zip_code)
REFERENCING gtt_zip1 (zip_code);
ALTER TABLE gtt_zip1
ADD CONSTRAINT fk_gtt_ptemp
FOREIGN KEY (zip_code)
REFERENCING ptemp (zip_code); |
| |
| Indexes |
Index Creation |
DROP TABLE <table_name> CASCADE CONSTRAINTS; |
CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE);
desc gtt_zip1
CREATE INDEX ix_gtt_zip1_user
ON gtt_zip1(by_user);
SELECT table_name, index_name, tablespace_name
FROM user_indexes;
-- indexes are stored in temp |