Oracle_ Creating and Managing tables



Oracle_ Creating and Managing tables

① Common Database objects
Table: A basic collection of data stores, consisting of rows and columns.
Views: Logically related collections of data extracted from a table.
Sequence: Provides a regular value.
Indexing: Improving the efficiency of queries
Synonyms: Aliases An object

Tables in the ②oracle database
1. User-defined tables:
A set of tables created and maintained by the user themselves, containing the information required by the user
such as: SELECT * from user_tables; View user-created tables
2. Data dictionary:
A set of tables created automatically by Oracle Server
Contains database information

③ Querying data dictionary
View user-defined tables.
SELECT table_name from User_tables;
View user-defined various database objects
SELECT DISTINCT object_type from User_objects;
View user-defined tables, views, synonyms, and sequences
SELECT * from User_catalog

④ table name and column name naming conventions:
1. Must start with a letter
2. Must be between 1–30 characters
3. Must contain only A–Z, A–Z, 0–9, _, $, and #
4. You must not duplicate the name of another user-defined object
5. Must not be a reserved word for Oracle

⑤create TABLE Statement
Must have:
1.CREATE Table Permissions
2. Storage space
CREATE TABLE [schema.] Table
(column datatype [DEFAULT expr][, ...]);

3. You must specify:
Table name
Column name, data type, size

4. Data type: Description
VARCHAR2 (size): variable long character data
CHAR (size): fixed-length character data
Number (P,S): variable Long Value data
Date: datetime data
Long: variable long character data, up to 2G
CLOB: Character data, up to 4G
Raw (LONG raw): Raw binary data
BLOB: Binary data, up to 4G
BFILE: Store binary data for external files up to 4G
ROWID: Line Address

5. Create a table using a subquery
Use the AS subquery option to combine creating tables and inserting data
CREATE Table Table
[(Column, column ...)]
as subquery;
Columns in the specified column and subquery correspond to each other
Defining columns by column name and default value

Note: The data in the original table is inserted into the newly created table, and if no data is required, add a false condition to

⑥alter TABLE Statement
1. Use the ALTER TABLE statement to append, modify, or delete the syntax of a column
Append columns
ALTER Table Table
ADD (column datatype [DEFAULT expr]
[, column datatype] ...);

modifying columns
ALTER Table Table
MODIFY (column datatype [DEFAULT expr]
[, column datatype] ...);

Note: If there is data in the column that cannot be modified, the default value is only valid for the data that is added later

Delete Column
ALTER Table Table
DROP COLUMN column_name;

renaming columns
ALTER TABLE table_name
RENAME Columm Old_column_name to New_column_name

Setting column is not available
ALTER TABLE table_name
Set unused Columm column_name

⑦ Deleting a table
Data and structure are deleted
All related transactions that are running are committed
All related indexes are deleted
DROP TABLE statement cannot be rolled back

DROP TABLE table_name;

⑧ emptying the table
TRUNCATE TABLE Statement:
Delete all data in a table
Freeing the table's storage space
Truncate statement cannot be rolled back
You can use the DELETE statement to delete data and roll back

TRUNCATE TABLE table_name;

⑨ changing the name of an object
Executes a rename statement to change the name of a table, view, sequence, or synonym
Must be the owner of the object

RENAME Old_object_name to New_object_name;

Note: None of the above DDL languages can be rolled back, and the default commit is done

Oracle_ Creating and Managing tables