/*************************************** ***************************/
-- Query table space parameters
Select tablespace_name, min_extents, max_extents, pct_increase, status from dba_tablespaces; -- query data file information
-- Autoextensible: whether to automatically expand when the database is full
Select tablespace_name, bytes, autoextensible, file_name from dba_data_files; /*************************************** ***************************/
-- Create a tablespace -- General information
-- Datafile: data file directory
-- Storage
-- Autoextend: automatically expanded when the data file is full
-- On next: Incremental
-- Maxsize unlimited: the maximum capacity is unlimited.
-- Size: File Size
-- Storage
-- Enable event logging: logging logs are generated and recoverable, while nologging does not generate logs and cannot be recovered for fast updates.
-- Management local: Local Management
-- Default: automatic allocation
-- Uniform size: uniform allocation
-- Management dictionary: manage in the dictionary
-- Default storage: overwrite the default partition Value
-- Initial: initial size
-- Next: Next size
-- Minextents: Minimum Number
-- Maxextents unlimited: the maximum number is unlimited.
-- Pctincrease: Incremental, in the unit of "%"
-- Minimum extent: maximum cell size
Create tablespace "test"
Nologging
Datafile 'G: \ oracle \ oradata \ myoracle \ test. ora 'size 5 m Reuse
Autoextend on next 1 m maxsize unlimited extent
Management local uniform size 12 K;
-- It is best to write relative paths to avoid errors
Create tablespace "test"
Nologging
Datafile '../database/test. ora 'size 5 m reuse -- we recommend that you use' ../oradata/test. ora'
Autoextend on next 1 m maxsize unlimited extent
Management local uniform size 12 K;
Create tablespace "test"
Logging
Datafile 'G: \ oracle \ oradata \ myoracle \ test. ora 'size 5 m extent
Management dictionary default storage (initial 1 K next 2 K
Minextents 5 maxextents 67 pctincrease 4) minimum extent 3 K; /*************************************** ***************************/
-- Add a tablespace. Note that test. ora1 cannot be the same as the name of the original tablespace file.
-- Add a new database file named test. ora1 with a size of 5 MB.
Alter tablespace mytesttablespace add datafile 'C: \ test. ora1 'size 5 m; /*************************************** ***************************/
-- Modify the tablespace database file attribute. myoracle is Sid.
-- Change the database file test. ora1 to 3 m, where resize can be online, offline, resize, autoextend, or end/drop.
Alter database myoracle datafile 'C: \ test. ora1 'resize 3 m;
Alter database myoracle datafile '$ ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10 m maxsize 500 m; /*************************************** ***************************/
/* Delete a tablespace
Syntax: Drop tablespace tablespace_name including contents and datafiles;
When you delete a tablespace, the system does not delete the tablespace database files. You need to delete it manually.
*/
Drop tablespace mytesttablespace including contents and datafiles; /*************************************** ***************************/
-- Create a user
-- Command:
-- Create user name identified by password default tablespace "default tablespace name" temporary tablespace "Temp" account unlock
-- General Attributes
-- Default tablespace: the default tablespace name.
-- Temporary tablespace: Temporary tablespace name, default temp
-- Account: User status. The default value is "unlock" and "Lock ".
Create user "test" Profile "default"
Identified by "test" default tablespace "test"
Temporary tablespace "Temp"
Account unlock;
-- Can be written
Create user "test" Profile "default"
Identified by "test" default tablespace "test "; /*************************************** ***************************/
-- User authorization
-- Grant "Connect, resource, dba" to "someuser" with admin option;
-- With admin option: Management Option
-- Grant usertest DBA Permissions
Grant DBA to "usertest" with admin option;
-- Cancel authorization
-- Revoke "resource" from "Scott "; /*************************************** ***************************/
-- Create a table
-- Create a table in usertest solution, note that the table name cannot use keywords, otherwise the error ORA-00903: The table name is invalid
-- Syntax:
-- Create Table [schema.] <table_name> (
-- <Column_name> <data type> [Default <expression>] <constraint> [,]
--.......
--) Tablespace tablespace_name;
-- Schema: indicates the name of the user or schema to which the schema belongs.
-- Table_name: the name of the new table.
-- Column_name: field name
-- Data Type: indicates the Data Type of the field.
-- Default <expression>: indicates the default value of a field.
-- Constraint: Constraints
-- Tablespace_name: used tablespace
Create Table "test". "test "(
"ID" number (10) default 0 not null,
"Name" varchar2 (20) not null,
"Info" varchar2 (1000 ),
Primary Key ("ID ")
) Tablespace "test ";
-- Can also be written
Create Table "test". "test "(
"ID" number (10) default 0 primary key,
"Name" varchar2 (20) not null,
"Info" varchar2 (1000)
) Tablespace "test "; /*************************************** ***************************/
-- Insert data
-- Syntax:
-- Insert into tablename (column1, column2 ,...) Values (expression1, expression2 ,...);
Insert into "test". "test" ("ID", "name", "info") values (1, 'testname ','');
-- Can also be written
Insert into "test ". "test" values (1, 'testname ',''); /*************************************** ***************************/
