-----Create tablespace----------
Create Tablespace Bdccs
Logging datafile ' D:\oracle\product\10.2.0\oradata\bdccs\bdccs.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;
-----Create user-to-table spaces------------------
Create user Bdccs identified by Bdccs
Default Tablespace Bdccs
Temporary tablespace temp;
------to authorize the user------------
Grant connect to Bdccs;
Grant resource to Bdccs;
Grant DBA to Bdccs;
---------How to import DMP files--------------
Imp platform/[email protected] file= ' d:/platform.dmp ' Fromuser=platform touser=platform
Imp gisqbpm/[email protected] file= ' d:/gisqbpm.dmp ' fromuser=gisqbpm touser=gisqbpm
(Note: The format is IMP username/password @ instance name file= ' file path ')
----Delete a user----
Dropuser PLATFORM Cascade
(Note: The deletion may prompt "Unable to delete the currently connected user", the user logged in PL/SQL is turned off, the relevant Tomcat stopped, restart the Oracle service)
--------Delete tablespace----
Drop tablespace PLATFORM including contents and datafiles
--Create a t_test table---------
CREATE TABLE T_test
(
TestID Number (3) NOT NULL primary key
)
(Note: T_test created table name, Testi field, Number (3) type, primary key primary key)
--Set the self-increment primary key-----Create sequence-----------------
Create sequence Test_seq
MinValue 1
MaxValue 2222222222
Start with 1
Increment by 1
Cache 20;
-----Create a trigger-----
Create or Replace Trigger Tr_test1
Before insert on t_test for each row
Begin
Select Test_seq.nextval into:new. TestID from dual;
End Tr_test; (note: Tr_test1 trigger name, T_test created for t_test table,)
---add a field---
ALTER TABLE t_test add MyName Varchar (2)
---inserting data--
Insert into T_test (MYNAME) VALUES (' AA ');
Insert into T_test (MYNAME) VALUES (' BB ');
---= Add a field---
ALTER TABLE T_test Add age number (3)
---= delete a field---
ALTER TABLE t_test Drop column age
---delete data----
Delete T_test where testid=5;
----All the contents of the output table---
SELECT * FROM t_test;-
Specify content----OUTPUT Table---
Select TestID as number, MYNAME as name from T_test;
-Build Table
CREATE TABLE my_test_table_20110414
(
AA number,
BB varchar2 (10)
);
--Modify table structure
ALTER TABLE my_test_table_20110414 add CC VARCHAR2 (10);
ALTER TABLE my_test_table_20110414 modify CC VARCHAR2 (100);
--Modify Table data contents
--Insert
Insert into my_test_table_20110414 (AA,BB)
VALUES (1, ' 2 ');
Commit
--Query
SELECT * from my_test_table_20110414;
--Modification
Update my_test_table_20110414
Set cc= ' Test '
where aa=1;
Commit
Create tablespace (with parameters):
CREATE tablespace zfmi logging datafile ' d:oracleoradatazfmizfmi.dbf '
Size 100m
Autoextend on next 32m
MaxSize 2048m Extent
Management local;
To create a temporary tablespace (with parameters):
CREATE temporary tablespace zfmi_temp tempfile ' d:oracleoradatazfmizfmi_temp.dbf '
Size 100m
Autoextend on next 32m
MaxSize 2048m Extent
Management local;
Parameter description:
Size: Specifies the initial size of the Tablespace database file
Autoextend: Size of table space Auto-growth
Maxsize: Maximum size of table space
UNIFORM Size: Specified area size, default is 64k
Pctfree: Used to control free space in data blocks
Initrans: The number of transactions used to control access to blocks of data, as well as the use of block head space
Maxtrans: The total number of transactions used to determine the block of data
Management Local: Default mode of Administration
Take the table space offline:
ALTER tablespace table_space name OFFLINE;
Bring table spaces Online
ALTER tablespace table_space name ONLINE;
Take the data file offline
ALTER DATABASE datafile name OFFLINE;
Bring a data file online
ALTER DATABASE datafile name ONLINE;
Make table space Read-only
ALTER tablespace table_space name READ only;
Make table spaces readable and writable
ALTER tablespace table_space name READ WRITE;
-Build Table
CREATE TABLE my_test_table_20110414
(
AA number,
BB varchar2 (10)
);
--Modify table structure
ALTER TABLE my_test_table_20110414 add CC VARCHAR2 (10);
ALTER TABLE my_test_table_20110414 modify CC VARCHAR2 (100);
--Modify table data Content--insert
Insert into my_test_table_20110414 (AA,BB)
VALUES (1, ' 2 ');
Commit
--Query
SELECT * from my_test_table_20110414;
--Modification
Update my_test_table_20110414
Set cc= ' Test '
where aa=1;
Commit
Create tablespace (with parameters):
CREATE tablespace zfmi logging datafile ' d:oracleoradatazfmizfmi.dbf '
Size 100m
Autoextend on next 32m
MaxSize 2048m Extent
Management local;
To create a temporary tablespace (with parameters):
CREATE temporary tablespace zfmi_temp tempfile ' d:oracleoradatazfmizfmi_temp.dbf '
Size 100m
Autoextend on next 32m
MaxSize 2048m Extent
Management local;
Parameter description:
Size: Specifies the initial size of the Tablespace database file
Autoextend: Size of table space Auto-growth
Maxsize: Maximum size of table space
UNIFORM Size: Specified area size, default is 64k
Pctfree: Used to control free space in data blocks
Initrans: The number of transactions used to control access to blocks of data, as well as the use of block head space
Maxtrans: The total number of transactions used to determine the block of data
Management Local: Default mode of Administration
Take the table space offline:
ALTER tablespace table_space name OFFLINE;
Bring table spaces Online
ALTER tablespace table_space name ONLINE;
Take the data file offline
ALTER DATABASE datafile name OFFLINE;
Bring a data file online
ALTER DATABASE datafile name ONLINE;
Make table space Read-only
ALTER tablespace table_space name READ only;
Make table spaces readable and writable
ALTER tablespace table_space name READ WRITE;
To delete a table space:
DROP tablespace table_space name including CONTENTS and datafiles;
Create user
CREATE User User
identified by password;
DROP tablespace table_space name including CONTENTS and datafiles;
Create user
CREATE User User
identified by password;
Oracle Code rules CREATE TABLE tablespace users, etc.