Oracle Database Test Data

SQL> create tablespace tbs_jove datafile '/u01/app/oracle/oradata/orcl/tbs_jove_1.dbf' size 1m autoextend on next 10m maxsize unlimited;

SQL> create user jove identified by jove default tablespace tbs_jove quota unlimited on tbs_jove;
SQL> grant connect,resource to jove;
SQL> grant create job to jove;

-- Create table
conn jove/jove

DROP TABLE IF EXISTS tbl01;

CREATE TABLE tbl01 (
    name VARCHAR2(10),
    amount NUMBER(20,2),
    time TIMESTAMP
);

-- Create Procedure

CREATE OR REPLACE PROCEDURE p_random_tbl01 IS
    v_name VARCHAR(10);
    v_amount NUMBER(20,2);
    v_timestamp TIMESTAMP;
BEGIN
    v_name := DBMS_RANDOM.STRING('A', 3);
    v_amount := ROUND(DBMS_RANDOM.VALUE(1, 10000), 2);
    v_timestamp := CURRENT_TIMESTAMP;
    INSERT INTO tbl01 (name, amount, time) VALUES (v_name, v_amount, v_timestamp);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END p_random_tbl01;
/

-- Create Job

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_INSERT_TBL01',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN p_random_tbl01; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=SECONDLY; INTERVAL=1',
        enabled         => TRUE
    );
END;
/

-- Query Job

SELECT job_name, state, enabled, next_run_date, comments
  FROM user_scheduler_jobs
  WHERE job_name = 'JOB_INSERT_TBL01';

-- Query Table

SELECT count(*) FROM tbl01;
SELECT count(*) FROM tbl01 WHERE time <= TIMESTAMP '2026-05-17 23:59:59';
SELECT SUM(amount) FROM tbl01 WHERE time <= SYSDATE;

DDL Test

--00. conn as jove
conn jove/jove

--01. table
create table table01(
  id number(12),
  name varchar2(30),
  age varchar2(10)
);

insert into table01 values (01, 'table01', 18);
insert into table01 values (02, 'table01', 19);
commit;

create table table02 (
id number(12),
name varchar2(30),
age varchar2(10))
partition by range(age)
(partition p1 values less than (20),
partition p2 values less than (40),
partition p3 values less than (60),
partition p_other values less than (maxvalue))
enable row movement;

select * from table01;
select * from table02;

truncate table table01;
select * from table01;

insert into table02 values(01,'itpux01',18);
insert into table02 values(02,'itpux02',28);
insert into table02 values(03,'itpux03',58);
insert into table02 values(04,'itpux04',68);
commit;
select * from table02;
select * from table02 partition(p1);
select * from table02 partition(p2);
select * from table02 partition(p3);
select * from table02 partition(p_other);
drop table table02;

--02. column
alter table table01 add (tel varchar2(11));
alter table table01 rename column name to myname;
alter table table01 drop (myname);
desc table01;

--03. constraints
alter table table01 add constraints c_age check (age >17);
alter table table01 modify constraints c_age novalidate;
alter table table01 drop constraints c_age;
select CONSTRAINT_NAME from dba_constraints where owner='JOVE';

--04.index
create index idx1 on table01(id);
select INDEX_NAME from dba_ind_columns where index_owner='JOVE';
alter index idx1 rebuild tablespace users;
select owner,index_name,tablespace_name from dba_indexes where owner='JOVE';
drop index idx1;

--05.view
create view v1 as select id from table01;
select * from v1;
drop view v1;

--06.synonym
create synonym syn1 for table01;
select * from all_objects where owner='JOVE' and object_type='SYNONYM';
drop synonym syn1;

--07.directory
create or replace directory jovedir as '/home/oracle';
select * from dba_directories;
drop directory jovedir;

--08.sequence
create sequence seq1 start with 0 increment by 1 minvalue 0 maxvalue 100;
select * from dba_sequences where sequence_owner='JOVE';
select seq1.nextval id from dual;
alter sequence seq1 increment by 2;
drop sequence seq1;

--09. functions
create function func1 return number is m1 number;
begin
m1:=0;
end func1;
/

select * from all_objects where owner='JOVE' and object_type='FUNCTION';
drop function func1;

--10. stored procedure
create or replace procedure proc1 is i1 number;
begin
i1:=0;
end proc1;
/

select * from all_objects where owner='JOVE' and object_type='PROCEDURE';
drop procedure proc1;