Oracle Base 10 Tables Table

--View the structure of the table

Desc YGB;

SELECT * FROM User_tab_columns
where table_name= ' YGB ';

--New Table YGB

CREATE TABLE YGB (
BH Number (3),
Eid VARCHAR2 (6) Constraint Eid_p primary key,
ename VARCHAR2 (10),
Bdate Date,
Sex char (1),
City Varchar2 (20)
);

[Email protected] test10g> CREATE TABLE Tbstest
2 (id int,
3 DT Date
4)
5 tablespace tbs_test; --Specify table space for table storage

--Create a new table by querying

CREATE TABLE Emptest as
SELECT * from EMP;

If this table has millions of rows and your time is not allowed, there are two more ways to increase the speed of creating new tables with large amounts of data. You can use the parallel and nologging options to increase the speed at which large tables are loaded. The parallel option allows you to perform data loading in parallel with multiple processes, and the nologging option indicates that this information is not logged into the Redo log file and the rollback segment (except for housekeeping purposes). As shown below:

CREATE TABLE Employee_new
As SELECT * FROM Employees
Parallel degree 4
nologging;

Another way is to simply move a table from one table space to another table space.

ALTER TABLE employee move New_tablespace;

When you move a table, the rowid of the row changes so that the index of the table is not available. You must re-index or rebuild them after you move the table.

--Renaming a table

ALTER TABLE YGB Rename to newname;

--Delete Table

drop table emp; --Delete Table
drop table EMP Purge; --Delete the table and not put it on the Recycle Bin
DROP TABLE EMP cascade constraints; --Delete the constraint together

TRUNCATE TABLE EMP; --TRUNCATE TABLE

--Modifying the structure of a table

ALTER TABLE EMP Add (job_id number); --Append columns
ALTER TABLE EMP Modify (job_id number (4,0) default 1001); --Modify Columns
ALTER TABLE EMP drop column job_id; --Delete Columns
ALTER TABLE EMP Drop (job_id);
ALTER TABLE EMP Rename column job_id to Jobid; --Rename Columns
Income integer generated always as (Salary+jiangjin); --Virtual columns

If the table in which you want to delete a column contains a large amount of data, you can mark the column as unused without completely deleting the data. You will not see the column in any query or attempt, and all dependent objects (such as constraints and indexes) defined on that column are deleted. In fact, the word method can be used to quickly "delete" a large data column.

ALTER TABLE EMP set unused column job_id; --Mark unused columns

You can then permanently delete the two columns by using the following command during a maintenance time.

ALTER TABLE EMP drop unused columns; --Delete Unused columns

You can delete a column with an optional CHECKPOINT clause if you think that there are too many rows in the table that could potentially consume undo space. By using checkpoints after a certain number of rows, the column is deleted and the undo data is reduced. See the example below. Whenever you delete 10000 rows in the EMP, the database uses a checkpoint:

ALTER TABLE EMP drop unused columns checkpoint 10000;

ALTER TABLE EMP Read write; --Read and write status
ALTER TABLE EMP Read only; --read-only status

You can perform the following actions on a read-only table:
Select
Create/alter/drop Index
ALTER TABLE add/modify/drop/enable/disable constraint
ALTER TABLE for physical property changes
Rename table and ALTER TABLE rename to
drop table

--Create session-level staging tables

Create global temporary table temp_tb1 (col_a varchar2 (30))
on commit preserve rows;

--Create a transaction-level staging table

Create global temporary table TEMP_TB2 (col_a varchar2 (30))
On commit Delete rows

--insert

INSERT INTO YGB
VALUES (bhseq.nextval,101, ' A ', ' 2-3-86 ', ' Male ', ' Xian ');

--update

Update Gzb
Set salary=salary*1.1
where eid=108;

--delete

Delete from YGB
where eid=108;

--View the size of the table

[Email protected] test11g> Select Segment_name,segment_type,bytes from user_segments
2 where segment_name= ' EMP ';

Segment_name Segment_type BYTES
--------------- ----------    ----------
EMP TABLE 65536

Oracle Base 10 Tables Table