--check tablespace

select c.tablespace_name, c.contents,
 a.total_mb, b.free_mb,
 to_char(100*b.free_mb/a.total_mb, '999.99') free_pct
from
 (select tablespace_name, to_char(sum(bytes)/1048576, '99999999.99') total_mb
  from dba_data_files group by tablespace_name) a,
 (select tablespace_name, to_char(sum(bytes)/1048576, '99999999.99') free_mb,
   count(*) free_exts, to_char(max(bytes)/1048576, '99999999') max_mb,
   to_char(min(bytes)/1048576, '99999999') min_mb
  from dba_free_space group by tablespace_name) b,
 dba_tablespaces c
where a.tablespace_name=b.tablespace_name(+) and c.tablespace_name=b.tablespace_name
order by free_pct, tablespace_name;
--创建表空间并修改数据文件为自动拓展
create tablespace test01 DATAFILE '/dmdata/data/data/POWERON/TETS01.DBF' SIZE 128;

ALTER TABLESPACE test01 datafile '/dmdata/data/data/POWERON/TETS01.DBF' AUTOEXTEND on next 100 maxsize 1024;

create tablespace power datafile '/dmdata/data/data/POWERON/power0102.DBF' size 128 autoextend on next 100 maxsize 10240 CACHE = NORMAL encrypt with RC4;

--create user default tablespace
create USER test identified by "Dameng0102!" hash with SHA512 salt 
ENCRYPT by "123456"
DEFAULT TABLESPACE power
DEFAULT INDEX TABLESPACE power;

grant public,soi to test;
grant resource to test;
--create table

CREATE TABLE TEST.city
(
 city_id CHAR(3) NOT NULL,
 city_name VARCHAR(40) NULL,
 region_id INT NULL
);

INSERT INTO test.city(city_id,city_name,region_id) VALUES('BJ','北京',1);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('SJZ','石家庄',1);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('SH','上海',2);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('NJ','南京',2);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('GZ','广州',3);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('HK','海口',3);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('WH','武汉',4);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('CS','长沙',4);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('SY','沈阳',5);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('XA','西安',6);
INSERT INTO test.city(city_id,city_name,region_id) VALUES('CD','成都',7);

select * from TEST.CITY

create view test.v_city as 
select city_id,city_name,region_id
from test.city
where region_id < 4;

select * from test.v_city;

create PROCEDURE TEST.proc_1
 (a IN OUT INT)
 AS
 b INT:=10;
 BEGIN
 a:=a+b;
 PRINT 'test.proc_1 answer:'||a;
 end;
 
 call TEST.proc_1(3);
 
 --function
 create FUNCTION TEST.fun_1
 (a INT , b INT)
 RETURN INT
 AS
  s INT;
  begin
  s:=a+b;
 RETURN s;
 END;
 
 select test.fun_1(4,5);
 
 --seq
 
 CREATE SEQUENCE test.seq_quantity START WITH  5 increment by 2 maxvalue 200;
 select test.seq_quantity.nextval from dual;
 
 --trigger
 
 create trigger  test.tri_upd AFTER
 UPDATE ON TEST.city
 for each row
 begin 
 print 'update operation on city!!';
 end;
 
 UPDATE TEST.CITY set city.REGION_ID=9 where CITY.CITY_ID='XA';

社区地址：https://eco.dameng.com