Create a table as a table -- -- -- -- -- the create table newtable as select * from a;
Create a table - the primary key constraint
CREATETABLE stu (
Sid CHAR (6) PRIMARY KEY,
Sname VARCHAR (20),
The age INT,
Gender VARCHAR (10)
);
Create a table - primary key growth since
The CREATE TABLE stu (
Sid INT PRIMARY KEY AUTO_INCREMENT,
Sname VARCHAR (20),
The age INT,
Gender VARCHAR (10)
);
- not null constraint
The CREATE TABLE stu (
Sid INT PRIMARY KEY AUTO_INCREMENT,
Sname VARCHAR (10) NOT NULL,
The age INT,
Gender VARCHAR (10)
);
-- -- -- -- -- -- -- -- -- -- -- -- the only constraint is similar to the primary key
The CREATE TABLE tab_ab (
SidINT PRIMARY KEY AUTO_INCREMENT,
UNIQUE snameVARCHAR (10)
);
-- -- -- -- -- -- -- -- -- -- -- --
specifies the foreign key constraintsThe CREATE TABLE t_section (
Sid INTPRIMARY KEY AUTO_INCREMENT,
Sname VARCHAR (30),
U_id INT,
The CONSTRAINT t_user FOREIGN KEY (u_id) REFERENCES t_user (uid)
);
-- -- -- -- -- - authorization statement
Grant connect, the resource to user1. - granted user rights
Grant connect, the resource to user1 with admin option;//permission obtained can be passed,
Grant select, update on the product to user02 with grant option;
Grant create any table, create procedure to role1.
Grant role1 to user1.
Grant select on table1 to user1.
Grant select any table to user1.
Given permission to users precise
GRANT CREATE USER, DROP the USER, the ALTER USER, CREATE ANY VIEW,
DROP ANY VIEW, EXP_FULL_DATABASE IMP_FULL_DATABASE,
The DBA, CONNECT, the RESOURCE, CREATE the SESSION TO user name
-- -- -- -- -- recycling permissions
Revoke the connect, the resource from user50;
Revoke the select, update on the product from user02;
- insert
Insert into newTable select * from oldTable;
Insert into Table1 (Table1. C1, Table1. The c2) select Table2. C1, Table2. C2 from Table2.
- database completely derived
Exp system/manager @ the TEST file=d: daochu. DMP full=y
Will database system users and sys derived table
Exp system/manager @ the TEST file=d: daochu. DMP owner=(system, sys)
Will be in the database table inner_notify, notify_staff_relat export
Exp aichannel/aichannel @ TESTDB2 file=d: datanewsmgnt. DMP tables=(inner_notify notify_staff_relat)
Field in the database tables in the table1 filed1 export data that starts with a "00"
Exp system/manager @ the TEST file=d: daochu. DMP tables (table1) query=="" "where filed1 like '00%' "" "- note the double quotes cannot little,
In the above command and compress=y to achieve compression,
Imp aichannel/aichannel @ HUST full=y file=d: datanewsmgnt. DMP ignore=y
It will probably be a bit of a problem, because some table already exists, then it is an error, on the table is not imported,
In the back and ignore=y is ok,
D: daochu. DMP tables in the table1 import
Imp system/manager @ the TEST file=d: daochu. DMP tables=(table1)
-- -- -- -- -- - whether can test the database connection
Tnsping former
Check the user table space usage
Select
B. ile_id file ID,
B.t ablespace_name table space name,
B.b ytes/1024/1024 | | 'M bytes,
(b.b ytes - sum (NVL (a. ytes, 0)))/1024/1024 | | 'M' has been used,
The sum (NVL (a. ytes, 0))/1024/1024 | | 'M' remaining space,
100 - the sum (NVL (a. ytes, 0))/(b.b ytes) * 100 occupancy percentage
The from dba_free_space a, dba_data_files b
Where a.f ile_id=b. ile_id
Group by b.t ablespace_name, b. ile_id, b.b ytes
The order by b. ile_id;
Check the temporary table space (dba_temp_files view) (v_ $tempfile as expected view)
Select tablespace_name, file_name, bytes/1024/1024 file_size, autoextensible from dba_temp_files;
Select the status, enabled, name, bytes/1024/1024 file_size from v $tempfile as expected; - sys user to see
Check the space address
Select file_name, tablespace_name from dba_data_files;
Check to see if the table space for automatic growth
The select tablespace_name, file_name, autoextensible from dba_data_files where tablespace_name='USERS';
Set to automatically increase the space
The alter database datafile '/home/oracle/ts01 DBF' autoextend on next 5 m maxsize unlimited;
Temporary data file will be set to automatic extension:
The alter database tempfile as expected '/u01/app/oracle/oradata/former/temp01. DBF' autoextend on next 5 m maxsize unlimited;
Increase the temporary file size:
The alter database tempfile as expected '/u01/app/oracle/oradata/former/temp01. DBF' resize 100 m;
Increase the file size
The alter database datafile '\ oracle \ oradata \ anita_2008 DBF' resize 4000 m,
Create a temporary table space
SQL> Create temporary in tablespace test1temp
Tempfile as expected '/home/u01/app/oracle/oradata/ytzx/test1temp01. DBF'
The size of 10240 m
Autoextend on next 1024 m
Maxsize 20480 m
Among management, local;
Create a data table space
The create in tablespace test1
Logging
Datafile '/home/u01/app/oracle/oradata/ytzx/test1. DBF'
The size 10240 m - 50-100 - g
Autoextend on next 2000 m
Maxsize unlimited
Among management, local autoallocate
The segment space management, auto;
Same space new store file
The ALTER table space in TABLESPACE name
The ADD DATAFILE 'data file path'
The SIZE of 500 m
AUTOEXTEND
ON the NEXT 1 m
MAXSIZE UNLIMITED;
For the user to specify the tablespace (set to auto growth)
The alter user username default in tablespace pulls.
CodePudding user response:
Good, learned,
CodePudding user response:
Learning,!!!!!!!!!!!!!!!!!!!!nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull