ORACLE _ create and manage tables, oracle Creation

ORACLE _ create and manage tables, oracle Creation
Zookeeper

ORACLE _ create and manage tables

① Common database objects
Table: A Basic Data Storage set consisting of rows and columns.
View: logically related data set extracted from the table.
Sequence: provides regular values.
Index: Improves query efficiency
Synonym: alias for an object

② Tables in the Oracle database
1. User-Defined table:
A group of tables created and maintained by the user, including the information required by the user
For example: SELECT * FROM user_tables; view the table created by the user
2. Data Dictionary:
A group of tables automatically created by Oracle Server
Contains database information

③ Query the data dictionary
View user-defined tables.
SELECT table_name FROM user_tables;
View User-Defined database objects
Select distinct object_type FROM user_objects;
View user-defined tables, views, synonyms, and Sequences
SELECT * FROM user_catalog

④ Naming rules for table names and column names:
1. It must start with a letter
2. It must be 1-30 characters long
3. Must contain only A-Z, a-z, 0-9, _, $, and #
4. It must not be the same as other user-defined objects.
5. It must not be a reserved character of Oracle.

⑤ Create table statement
Required:
1. create table permission
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 Length Value Data
DATE: DATE type data
LONG: variable length data, up to 2 GB
CLOB: character data, up to 4 GB
RAW (long raw): RAW binary data
BLOB: binary data, up to 4 GB
BFILE: stores binary data of external files, up to 4 GB
ROWID: Row address

5. Create a table using a subquery
Use the AS subquery option to combine table creation and data insertion
Create table table
[(Column, column...)]
AS subquery;
The specified columns must correspond to the columns in the subquery.
Use column names and default values to define Columns

Note: The data in the original table is inserted into the newly created table. If no data is required, add a FALSE condition.

⑥ Alter table statement
1. Use the alter table statement to append, modify, or delete the column syntax
Append Column
Alter table table
ADD (column datatype [DEFAULT expr]
[, Column datatype]...);

Modify columns
Alter table table
MODIFY (column datatype [DEFAULT expr]
[, Column datatype]...);

NOTE: If data in a column cannot be modified, the default value is only valid for the data added later.

Delete column
Alter table table
Drop column column_name;

Rename a column
Alter table table_name
Rename columm old_column_name TO new_column_name

Set column unavailable
Alter table table_name
Set unused COLUMM column_name

7. delete a table
Data and structure are deleted
All running related transactions are committed.
All related indexes are deleted.
The drop table statement cannot be rolled back.

Drop table table_name;

Distinct clear table
Truncate table statement:
Delete all data in the table
Release the table's storage space
The TRUNCATE statement cannot be rolled back.
You can use the DELETE statement to DELETE and roll back data.

Truncate table table_name;

Alias changes the object name.
Execute the RENAME statement to change the name of the table, view, sequence, or synonym.
Must be the object owner

RENAME old_object_name TO new_object_name;

Note: none of the above DDL languages can be rolled back. After the execution is complete, it will be submitted by default.

How to create tables and tablespaces in Oracle?

1. creating a tablespace in ORACLE is the basic method for data management. All user objects must be stored in the tablespace, that is, the user can use the space to create user objects. otherwise, you do not need to create objects because you want to create objects, such as tables and indexes, and there is no place to store them. Oracle will prompt that there is no storage quota. therefore, before creating an object, you must first allocate a bucket.
To allocate storage space, create a tablespace:
Example of creating a tablespace:
Create tablespace "SAMPLE" LOGGINGDATAFILE 'd: \ ORACLE \ ORADATA \ ORA92 \ LUNTAN. ora 'size 5 M
The statements above extent management local segment space management auto are divided into the following parts: 1. create tablespace "SAMPLE" to CREATE a TABLESPACE named "SAMPLE.
To name a table space, follow the naming rules of Oracle.
There are three types of tablespaces that can be created in ORACLE:
(1) TEMPORARY: TEMPORARY tablespace for storing TEMPORARY data;
The syntax for creating a temporary tablespace is as follows:
Create temporary tablespace "SAMPLE "......
(2) UNDO: Restore the tablespace. used to store the redo log file.
The syntax for creating a restored tablespace is as follows:
Create undo tablespace "SAMPLE "......
(3) User tablespace: it is the most important and used to store user data table space.
You can directly write it as: create tablespace "SAMPLE"
TEMPORARY and UNDO tablespaces are special tablespaces managed by ORACLE. They are only used to store system-related data.
Second: LOGGING
There are two options: NOLOGGING and LOGGING,
NOLOGGING: when creating a tablespace, no redo logs are created.
LOGGING is the opposite of NOLOGGING, that is, the redo log is generated when the tablespace is created.
When using NOLOGGING, the advantage is that logs are not generated during creation, so that the tablespace can be created quickly but cannot be logged. After data is lost, it cannot be recovered. However, when creating a tablespace, there is no data. Generally, after creating a tablespace and importing data, you need to back up the data. Therefore, you do not need to create a tablespace. Therefore, when creating a tablespace, select NOLOGGING to accelerate the creation of the tablespace. third, DATAFILE is used to specify the location and size of the data file.
For example, DATAFILE 'd: \ ORACLE \ ORADATA \ ORA92 \ LUNTAN. ora SIZE 5 M
The storage location of the file is D: \ ORACLE \ ORADATA \ ORA92 \ LUNTAN. ora, and the file size is 5 MB.
If multiple files exist, separate them with commas:
DATAFILE 'd: \ ORACLE \ ORADATA \ ORA92 \ LUNTAN. ora 'size 5 M, 'd: \ ORACLE \ ORADATA \ ORA92 \ dd. ora 'size 5 MB, but each file needs to specify the SIZE. the Unit is subject to the specified unit, such as 5 m or 500 K.
Specific files can be stored on different media, such as disk arrays, based on different needs to reduce IO competition.
When the file name is specified, it must be an absolute address. The relative address cannot be used. Fourth: EXTE ...... the remaining full text>

Create a table with oracle

Create table statement
-- Create tablecreate table CAPITAL_ADJUST (CAPITAL_ID VARCHAR2 (10), CAPITAL_NAME VARCHAR2 (50), TYPE VARCHAR2 (10), BELONG VARCHAR2 (50), IN_DEP VARCHAR2 (50 ), ID VARCHAR2 (10) not null, IN_DEP_LEADER VARCHAR2 (50), OUT_DEP_LEAD VARCHAR2 (50) -- Add comments to the columns comment on column CAPITAL_ADJUST.CAPITAL_ID is 'asset number '; comment on column CAPITAL_ADJUST.CAPITAL_NAME is 'asset name'; comment on column CAPITAL_ADJUST.TYPE is 'Type and model '; comment on column CAPITAL_ADJUST.BELONG is 'department'; comment on column comment is 'transferred to departments '; comment on column CAPITAL_ADJUST.ID is 'No.'; comment on column CAPITAL_ADJUST.IN_DEP_LEADER is 'allocate department owner '; comment on column CAPITAL_ADJUST.OUT_DEP_LEAD is 'transfer department owner'; -- Create/Recreate, unique and foreign key constraints alter table CAPITAL_ADJUST add constraint PK_CAPITAL primary key (ID); insert data SQL
Insert into CAPITAL_ADJUST (ID, CAPITAL_ID, CAPITAL_NAME, TYPE, BELONG, IN_DEP, IN_DEP_LEADER, OUT_DEP_LEAD) VALUES (1, '001', '', 'v001 ', 'department 1', 'department 2', 'Incoming owner ', 'outgoing owner'); Delete data SQL
Delete from CAPITAL_ADJUST t where t. ID = 1; modify the table
Alter table CAPITAL_ADJUST drop column OUT_DEP_LEAD; query records
SELECT * FROM CAPITAL_ADJUST;