Oracle Base 10 Tables Table

Source: Internet
Author: User
Tags one table

--View the structure of the table

Desc YGB;

SELECT * FROM User_tab_columns
where table_name= ' YGB ';

--New 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
5 tablespace tbs_test; --Specify table space for table storage

--Create a new table by querying

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

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


--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:
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


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


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


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
--------------- ----------    ----------

Oracle Base 10 Tables Table

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.