In today’s article, we will be examining Tables In Oracle Database, which are the simplest structures in which data is stored.<\/p>\n
There are 8 types.<\/p>\n
We can learn detailed information about tables and columns from the views below.<\/p>\n
DBA_TAB_COL_STATISTICS\r\nDBA_TAB_COLS\r\nDBA_TAB_COLUMNS\r\nDBA_TAB_COMMENTS\r\nDBA_TAB_HISTGRM_PENDING_STATS\r\nDBA_TAB_HISTOGRAMS\r\nDBA_TAB_MODIFICATIONS\r\nDBA_TAB_PARTITIONS\r\nDBA_TAB_PENDING_STATS\r\nDBA_TAB_PRIVS\r\nDBA_TAB_STAT_PREFS\r\nDBA_TAB_STATISTICS\r\nDBA_TAB_STATS_HISTORY\r\nDBA_TAB_SUBPARTITIONS\r\nDBA_TABLES<\/pre>\nTables and indexes should be stored in different tablespace.<\/p>\n
If we want to write comments about tables and columns, we run the command below.<\/p>\n
SQL> COMMENT ON TABLE CALISANLAR IS 'CALISANLAR TABLOSU';\r\n\t\r\nComment created.\r\n\t\r\nSQL> COMMENT ON COLUMN CALISANLAR.ADI IS 'CALISANIN ADI';\r\n\t\r\nComment created.<\/pre>\nWe can give a default value for a column when creating a table. It is especially used in columns containing dates.<\/p>\n
SQL> CREATE TABLE ISLEM\r\n(\r\nID NUMBER,\r\nISLEM VARCHAR2 (300),\r\nTARIH DATE DEFAULT SYSDATE\r\n)\r\nTABLESPACE USERS;\r\n\t\r\nTable created.\r\n\t\r\nSQL> INSERT INTO SYS.ISLEM VALUES ('1', '1', '');\r\n\t\r\n1 row created.\r\n<\/pre>\nIn this case, when we query, the result comes as follows.<\/p>\n
SELECT * FROM SYS.ISLEM;\r\n\t\r\nID\tISLEM\tTARIH\r\n\t\r\n1\t1<\/pre>\nThe reason for this is that we say fill in the blank with the ” sign.<\/p>\n
If we want it to be history;<\/p>\n
SQL> INSERT INTO SYS.ISLEM (ID) VALUES (3);\r\n\t\r\n1 row created.\r\n<\/pre>\nIn this case, when we query, the result comes as follows.<\/p>\n
ID\tISLEM\tTARIH\r\n\t\r\n1\t1\t\r\n3\t\t20.07.2015 18:47:41<\/pre>\nA table can be created using virtual column as follows.<\/p>\n
CREATE TABLE YAS\r\n(\r\nID NUMBER,\r\nYAS NUMBER,\r\nYAS_DURUMU GENERATED ALWAYS AS\r\n(\r\nCASE WHEN YAS BETWEEN 18 AND 30 THEN 'GENC'\r\nWHEN YAS > 65 THEN 'YASLI'\r\nEND\r\n)\r\n);\r\n<\/pre>\nGENERATED ALWAYS can also be created without using the AS keyword.<\/p>\n
However, when the GENERATED ALWAYS AS keyword is used, data is not stored on the virtual column.<\/p>\n
When the table is queried with Select, virtual column values \u200b\u200bare generated.<\/p>\n
Another important issue when creating a table is the word LOGGING\/NOLOGGING.<\/p>\n
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.<\/p>\n
If the tablespace belonging to the table is nologging and nothing is specified when creating the table, NOLOGGING is applied to the table.<\/p>\n
The LOGGING status of a table is changed as follows.<\/p>\n
a. SQL> SELECT TABLE_NAME, LOGGING FROM DBA_TABLES WHERE TABLE_NAME='ISLEM';\r\n\t\r\nTABLE_NAME LOG\r\n------------------------------ ---\r\nISLEM YES\r\n<\/pre>\na. SQL> ALTER TABLE SYS.ISLEM NOLOGGING;\r\n\t\t\r\nTable altered.<\/pre>\na. SQL> SELECT TABLE_NAME, LOGGING FROM DBA_TABLES WHERE TABLE_NAME='ISLEM';\r\n\t\t\r\nTABLE_NAME LOG\r\n------------------------------ ---\r\nISLEM NO<\/pre>\n<\/div>\n