In today’s article, we will be examining Tables In Oracle Database, which are the simplest structures in which data is stored.
There are 8 types.
We can learn detailed information about tables and columns from the views below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DBA_TAB_COL_STATISTICS DBA_TAB_COLS DBA_TAB_COLUMNS DBA_TAB_COMMENTS DBA_TAB_HISTGRM_PENDING_STATS DBA_TAB_HISTOGRAMS DBA_TAB_MODIFICATIONS DBA_TAB_PARTITIONS DBA_TAB_PENDING_STATS DBA_TAB_PRIVS DBA_TAB_STAT_PREFS DBA_TAB_STATISTICS DBA_TAB_STATS_HISTORY DBA_TAB_SUBPARTITIONS DBA_TABLES |
Tables and indexes should be stored in different tablespace.
If we want to write comments about tables and columns, we run the command below.
|
1 2 3 4 5 6 7 |
SQL> COMMENT ON TABLE CALISANLAR IS 'CALISANLAR TABLOSU'; Comment created. SQL> COMMENT ON COLUMN CALISANLAR.ADI IS 'CALISANIN ADI'; Comment created. |
We can give a default value for a column when creating a table. It is especially used in columns containing dates.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> CREATE TABLE ISLEM ( ID NUMBER, ISLEM VARCHAR2 (300), TARIH DATE DEFAULT SYSDATE ) TABLESPACE USERS; Table created. SQL> INSERT INTO SYS.ISLEM VALUES ('1', '1', ''); 1 row created. |
In this case, when we query, the result comes as follows.
|
1 2 3 4 5 |
SELECT * FROM SYS.ISLEM; ID ISLEM TARIH 1 1 |
The reason for this is that we say fill in the blank with the ” sign.
If we want it to be history;
|
1 2 3 |
SQL> INSERT INTO SYS.ISLEM (ID) VALUES (3); 1 row created. |
In this case, when we query, the result comes as follows.
|
1 2 3 4 |
ID ISLEM TARIH 1 1 3 20.07.2015 18:47:41 |
A table can be created using virtual column as follows.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE YAS ( ID NUMBER, YAS NUMBER, YAS_DURUMU GENERATED ALWAYS AS ( CASE WHEN YAS BETWEEN 18 AND 30 THEN 'GENC' WHEN YAS > 65 THEN 'YASLI' END ) ); |
GENERATED ALWAYS can also be created without using the AS keyword.
However, when the GENERATED ALWAYS AS keyword is used, data is not stored on the virtual column.
When the table is queried with Select, virtual column values are generated.
Another important issue when creating a table is the word LOGGING/NOLOGGING.
If LOGGING is used, a redo log is generated for each DML operation on the table. If NOLOGGING is used, log production is not done in some cases.
If the tablespace belonging to the table is nologging and nothing is specified when creating the table, NOLOGGING is applied to the table.
The LOGGING status of a table is changed as follows.
|
1 2 3 4 5 |
a. SQL> SELECT TABLE_NAME, LOGGING FROM DBA_TABLES WHERE TABLE_NAME='ISLEM'; TABLE_NAME LOG ------------------------------ --- ISLEM YES |
|
1 2 3 |
a. SQL> ALTER TABLE SYS.ISLEM NOLOGGING; Table altered. |
|
1 2 3 4 5 |
a. SQL> SELECT TABLE_NAME, LOGGING FROM DBA_TABLES WHERE TABLE_NAME='ISLEM'; TABLE_NAME LOG ------------------------------ --- ISLEM NO |
