Getting Started with Oracle table spaces and tables instructions

Oracle database-Common operations directives

View port number Directives

Netstat–a

Clear screen:

Sql> Clear Scre; SQL> Host CLS;

Table Space

CREATE TABLE Space

sql> Create tablespace tc20 datafile  'e:\tc20_data_dbf' size 20m  autoextend on Next 5m maxsize 500m;

Querying table space Names

Select  from Dba_data_files order by file_name;

Query the name of all tablespace, and the physical file name. Queries from the data dictionary (dba_data_files) . And sort the results of the query. Sort by file name (in ascending order by default). ）

Querying the default table space

Select  from Dba_users order by user_id;

Each user is automatically created in the user's default tablespace if it performs a build operation after logging into the database and does not explicitly indicate that the table is in that table space. This default table space is equivalent to the user's working space. This space can be queried by a single statement.

To modify the default table space

default tablespace table_name;

It's important to note that. If a tablespace is no longer used to be deleted, but it is the default tablespace, then the default is to be lifted before deletion.

To rename a tablespace

sql> alter tablespace  old_name Rename to new_name;

Delete Table space

Drop tablespace Name

This removes the content and deletes the local file. If you want to delete together you need to use

Drop tablespace tc20 including contents and datafiles;

Table

To create a table:

CREATE TABLE Table name (column 1 data type, column 2 Data Type,... column N data type) tablespace table space.

Sql> CREATE TABLE student (  2  ID number,  3  name VARCHAR2 (  ),  4  gender number) tablespace MYWORK;

Adding data to a table

sql> INSERT into student values (20001,' Turing ', +);

Querying the data tables created by the user

Select  from where LOWER (table_name) ' Student ';

View the data structure of an existing table.

The use of select does not give you a concrete statement of the build table. Want to get a build statement only by following the statement

Sql> describe dual;

For example, when inserting data, you suddenly forget the data type of the column, which can be viewed by this statement.

Add New Column

Sql> ALTER TABLE student Add (phonenumber number);

Modifying the data type of a column

sql> ALTER TABLE student Modify (age varchar2);

Delete an existing column

ALTER TABLE student drop column PhoneNumber;

Delete Use drop note that the use of the Delete column is used with the companion column. This is because only column is used to indicate that you want to delete the columns.

Naming the Rename

sql> ALTER TABLE Student rename column ID to IDs;

Note that the same as delete to add column. Indicates that the name of a column is to be changed.

Change Table Space

sql> ALTER TABLE student move tablespace USERS;

Delete entire table

sql> drop table student;

Add data to the table:

INSERT into students (Mid,name,age) VALUES (1,' Zhang San ',+); INSERT INTO Students (Mid,name,age) VALUES (2,' John Doe ',+);

Single-Table query:

Select syntax format for the command:

Select column 1, column 2,..., column n from table where Conditional Order by (Desc );

There is one point of note for using groupings in Oracle: The criteria for group by must appear in the list of select. or in a compound function. That is, grouping two ways:

1.select column (A) from table Group By column (B)

The A and B here must be the same column

2.select column (A), function (column B) from table Group By column (C)

Here A and C must be the same as columns. A and B do not need to be the same.

Getting Started with Oracle table spaces and tables instructions