Oracle Database and tablespace

Log on as an administrator:
1. First, create (new) Users:
Create user username identified by password;
Username: username of The New User Name
Password: password of the new user
You can also use the previous user instead of creating a new user. For example, you can continue to use the scott user.

2. Create a tablespace:
Create tablespace tablespacename datafile 'd: \ data. dbf' size xxxm;
Tablespacename: name of the tablespace
D: \ data. dbf': storage location of the tablespace
The size of the xxx tablespace, in MB)
3. Allocate the space to the user:
Alert user username default tablespace tablespacename;
Allocate the tablespace named tablespacename to username

4. Authorize the user:
Grant create session, create table, unlimited tablespace to username;

5. log on to the user created by the landlord, and then create a table.
Conn username/password;
Create table for creating a table.
 
 
| |
 
The SYS user logs in as a DBA under CMD:
Run sqlplus/nolog in CMD.
Then
Conn/as sysdba
// Create a temporary tablespace
 
Create temporary tablespace user_temp
Tempfile 'd: \ oracle \ oradata \ Oracle9i \ user_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
 
// Create a data table space
Create tablespace test_data
Logging
Datafile 'd: \ oracle \ oradata \ Oracle9i \ user_data.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
 
// Create a user and specify the tablespace
Create user username identified by password
Default tablespace user_data
Temporary tablespace user_temp;
 
// Grant permissions to users
 
Grant connect, resource to username;
 
// Log on to the user later. Any database objects created belong to the user_temp and user_data tablespaces,
This eliminates the need to specify a tablespace for each created object.
Withdrawal:
Revoke permission... from user name;
Delete USER commands
Drop user user_name cascade;
Create a tablespace
Create tablespace data01
DATAFILE '/oracle/oradata/db/data01.dbf' SIZE 500 M
Uniform size 128 k; # specify the area SIZE as 128 k. If not specified, the default area SIZE is 64 k.
Delete a tablespace
Drop tablespace data01 including contents and datafiles;
1. Create a tablespace
Create tablespace data01
DATAFILE '/oracle/oradata/db/data01.dbf' SIZE 500 M
Uniform size 128 k; # specify the area SIZE as 128 k. If not specified, the default area SIZE is 64 k.
2. Create an UNDO tablespace
Create undo tablespace UNDOTBS02
DATAFILE '/oracle/oradata/db/undotbs02.dbf' SIZE 50 M
# Note: In the OPEN state, only one UNDO tablespace can be used at some time. To use a new tablespace, you must switch to the tablespace:
Alter system set undo_tablespace = UNDOTBS02;
3. Create a temporary tablespace
Create temporary tablespace temp_data
TEMPFILE '/oracle/oradata/db/temp_data.dbf' SIZE 50 M
Iv. Change the tablespace status
1. Take the tablespace offline
Alter tablespace game OFFLINE;
If the data file is accidentally deleted, the RECOVER option must be included.
Alter tablespace game offline for recover;
2. Bring the tablespace online
Alter tablespace game ONLINE;
3. offline data files
Alter database datafile 3 OFFLINE;
4. Bring data files online
Alter database datafile 3 ONLINE;
5. Read-Only tablespace
Alter tablespace game read only;
6. Make the tablespace readable and writable
Alter tablespace game read write;
5. Delete tablespaces
Drop tablespace data01 including contents and datafiles;
Vi. Extended tablespace
First, check the name and file of the tablespace.
Select tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) total_space
From dba_data_files
Order by tablespace_name;
1. Add data files
Alter tablespace game
Add datafile '/oracle/oradata/db/game02.dbf' SIZE 1000 M;
2. manually add data file size
Alter database datafile '/oracle/oradata/db/GAME. dbf'
RESIZE 4000 M;
3. Set automatic expansion of data files
Alter database datafile '/oracle/oradata/db/GAME. dbf
Autoextend on next 100 M
MAXSIZE 10000 M;
View tablespace information after setting
Select a. TABLESPACE_NAME, A. bytes total, B. BYTES USED, C. BYTES FREE,
(B. BYTES * 100)/A. BYTES "% USED", (C. BYTES * 100)/A. BYTES "% FREE"
From sys. SM $ TS_AVAIL A, SYS. SM $ TS_USED B, SYS. SM $ TS_FREE C
Where a. TABLESPACE_NAME = B. TABLESPACE_NAME AND A. TABLESPACE_NAME = C. TABLESPACE
 
| Creates a table View | |
Create or replace view pagenew_view
As
Select pn. newid, pn. title, nt. tid, nt. tname from page pg, pagenew pn, newtype nt where pg. tid <> '0' and pg. tid = nt. tid and pg. pageid = pn. pageid order by pg. createtime desc
 
 
Select * from pagenew_view

Author: "Soy Milk"