Create Oracle tablespaces and tables using SQL syntax

This document creates two database spaces: one for storing data and the other for storing indexes. Note that the index space is small. User: SA, password: liuxuezong, service name: rtsos.

1. Create a service name

2. Create a tablespace File

----------------------------------------------------------------------------- Title: rtsos_tablespace. SQL ---- function: Create tablespaces and users for the 'rtsos 'database. ---- Project Team: rtsos tablespace/* tablespace ** create tablespace ** tablespace */create tablespace rtsos nologging extent management local datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ rtsos. DBF 'size 128 M autoextend on next 16 m maxsize unlimited; Create tablespace rtsos_index nologging extent management local datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ rtsos_index.dbf 'size 16 m autoextend on next 2 m maxsize unlimited; added users by adding users: sa -- Query users: select username from dba_users; create user SA identified by liuxuezong default tablespace rtsos temporary tablespace temp profile default; grant connect to SA; grant DBA to SA; grant resource to SA; grant unlimited tablespace to SA;

3. Create a table File

/* --------------------------------------------------------------------------- Title: rtsos_createtable. SQL ---- function: Create related tables in the 'rtsos 'database. ---- Project Team: rtsos ---- schedule */connect SA/liuxuezong @ rtsos; schedule: trpw_sample100hz schedule prompt creating trpw_sample100hz... create Table trpw_sample100hz (type number (2) not null, sample_time number (12) not null, val0 number (9,3) null, val1 number (9,3) null, val2 number (9,3) null, val3 number (9,3) null, val4 number (9,3) null, val5 number (9,3) null, val6 number (9,3) null, val7 number (9,3) null, val8 number (9,3) null, val9 number (9,3) null, val10 number (9,3) null, val11 number (9,3) null, val12 number (9,3) null, val13 number (9,3) null, val14 number (9,3) null, val15 number (9,3) null, val16 number (9,3) null, val17 number (9,3) null, val18 number (9,3) null, val19 number (9,3) null, val20 number (9,3) null, val21 number (9,3) null, val22 number (9,3) null, val23 number (9,3) null, val24 number (9,3) null, val25 number (9,3) null, val26 number (9,3) null, val27 number (9,3) null, val28 number (9,3) null, val29 number (9,3) null, val30 number (9,3) null, val31 number (9,3) null, val32 number (9,3) null, val33 number (9,3) null, val34 number (9,3) null, val35 number (9,3) null, val36 number (9,3) null, val37 number (9,3) null, val38 number (9,3) null, val39 number (9,3) null, val40 number (9,3) null, val41 number (9,3) null, val42 number (9,3) null, val43 number (9,3) null, val44 number (9,3) null, val45 number (9,3) null, val46 number (9,3) null, val47 number (9,3) null, val48 number (9,3) null, val49 number (9,3) null, val50 number (9,3) null, val51 number (9,3) null, val52 number (9,3) null, val53 number (9,3) null, val54 number (9,3) null, val55 number (9,3) null, val56 number (9,3) null, val57 number (9,3) null, val58 number (9,3) null, val59 number (9,3) null, val60 number (9,3) null, val61 number (9,3) null, val62 number (9,3) null, val63 number (9,3) null, val64 number (9,3) null, val65 number (9,3) null, val66 number (9,3) null, val67 number (9,3) null, val68 number (9,3) null, val69 number (9,3) null, val70 number (9,3) null, val71 number (9,3) null, val72 number (9,3) null, val73 number (9,3) null, val74 number (9,3) null, val75 number (9,3) null, val76 number (9,3) null, val77 number (9,3) null, val78 number (9,3) null, val79 number (9,3) null, val80 number (9,3) null, val81 number (9,3) null, val82 number (9,3) null, val83 number (9,3) null, val84 number (9,3) null, val85 number (9,3) null, val86 number (9,3) null, val87 number (9,3) null, val88 number (9,3) null, val89 number (9,3) null, val90 number (9,3) null, val91 number (9,3) null, val92 number (9,3) null, val93 number (9,3) null, val94 number (9,3) null, val95 number (9,3) null, val96 number (9,3) null, val97 number (9,3) null, val98 number (9,3) null, val99 number (9,3) null) storage (initial 64 K next 64 K minextents 1 maxextents unlimited pctincrease 0) nologging; alter table trpw_sample100hz add primary key (type, sample_time) using index tablespace rtsos_index storage (initial 64 K next 64 K minextents 1 maxextents unlimited pctincrease 0) nologging; grant select on trpw_sample100hz to public; Revoke prompt 1 tables created...

3. log on to sqplus using System

 

4. Execute the rtsos_tablespace. SQL File

 

5. Execute the rtsos_createtable. SQL File

6. Use PLSQL developer to view the generated database and table

 

 7. Delete tablespace files

----------------------------------------------------------------------------- Title: rtsos_droptablespace. SQL ---- function: delete tablespaces and users of the 'rtsos 'database. ---- Project Team: rtsos ---- tablespace/* tablespace ** Delete tablespace ** tablespace */drop tablespace rtsos including contents and datafiles; drop tablespace rtsos_index including contents and datafiles; ------------------------------------------------------------------------------- delete user ------------------------------------------------------------------------------- prompt delete user: SA drop user SA Cascade