0

I am using Oracle and SQL Developer to create a database. I am fairly new to SQL, so this may be a simple question. I have my SYSTEM user, who owns several tables, for example this one:

CREATE TABLE T_SCHEDULE
(
    sched_id number(4) NOT NULL,
    master_id varchar2(5) REFERENCES T_MASTER(master_id) ON DELETE CASCADE NOT NULL,
    client_id varchar2(5) REFERENCES T_CLIENT(client_id) ON DELETE CASCADE NOT NULL,
    box_num number(5) REFERENCES T_BOX(box_num) ON DELETE CASCADE NOT NULL,
    car_num varchar2(10) REFERENCES T_CAR(car_num) ON DELETE CASCADE NOT NULL,
    price number(7),
    job_start timestamp NOT NULL,
    job_stop timestamp NOT NULL,
    PRIMARY KEY(sched_id)
);
INSERT INTO T_SCHEDULE VALUES (4001, 'M1001', 'C2002', 3002, 'Р232ХВ', 9000, TIMESTAMP '2021-10-02 12:30:00.0', TIMESTAMP '2021-10-02 17:30:00.0');
INSERT INTO T_SCHEDULE VALUES (4002, 'M1002', 'C2003', 3003, 'А847КР', 4000, TIMESTAMP '2021-10-02 08:15:00.0', TIMESTAMP '2021-10-02 12:15:00.0');
INSERT INTO T_SCHEDULE VALUES (4003, 'M1003', 'C2004', 3005, 'С966ЕС', 25000, TIMESTAMP '2021-10-02 10:45:00.0', TIMESTAMP '2021-10-02 13:45:00.0');
INSERT INTO T_SCHEDULE VALUES (4004, 'M1004', 'C2005', 3001, 'Т138УВ', 30000, TIMESTAMP '2021-10-02 10:30:00.0', TIMESTAMP '2021-10-02 15:30:00.0');
INSERT INTO T_SCHEDULE VALUES (4005, 'M1005', 'C2006', 3006, 'Р364ВЕ', 20000, TIMESTAMP '2021-10-02 09:00:00.0', TIMESTAMP '2021-10-02 11:00:00.0');
INSERT INTO T_SCHEDULE VALUES (4006, 'M1001', 'C2007', 3005, 'О117УУ', 24000, TIMESTAMP '2021-10-03 14:10:00.0', TIMESTAMP '2021-10-03 17:10:00.0');
INSERT INTO T_SCHEDULE VALUES (4007, 'M1002', 'C2008', 3002, 'Н439АМ', 6000, TIMESTAMP '2021-10-03 10:40:00.0', TIMESTAMP '2021-10-03 15:40:00.0');
INSERT INTO T_SCHEDULE VALUES (4008, 'M1003', 'C2009', 3003, 'О896МТ', 8000, TIMESTAMP '2021-10-02 14:30:00.0', TIMESTAMP '2021-10-02 18:30:00.0');
INSERT INTO T_SCHEDULE VALUES (4009, 'M1004', 'C2010', 3004, 'Т694КС', 12000, TIMESTAMP '2021-10-03 09:50:00.0', TIMESTAMP '2021-10-03 17:50:00.0');
INSERT INTO T_SCHEDULE VALUES (4010, 'M1005', 'C2001', 3001, 'У601КК', 22000, TIMESTAMP '2021-10-02 16:00:00.0', TIMESTAMP '2021-10-02 20:00:00.0');

I also have user m1001 with this permissions:

GRANT INSERT(sched_id, master_id, client_id, box_num, car_num, job_start, job_stop) 
    ON T_SCHEDULE TO m1001;
GRANT SELECT ON T_SCHEDULE TO m1001;

When I inserted a row in m1001's workspace:

INSERT INTO SYSTEM.T_SCHEDULE 
    (sched_id, master_id, client_id, box_num, car_num, job_start, job_stop) 
VALUES 
    (4011, 'M1001', 'C2001', 3001, 'У601КК', TIMESTAMP '2021-10-04 16:00:00.0', TIMESTAMP '2021-10-04 20:00:00.0');

it was successful. I can now view it with select query:

SELECT * FROM SYSTEM.T_SCHEDULE;

However, when I run this same query in SYSTEM user workspace I see the old version of the table:

Why is that and how can I make m1001's data visible?

1
  • 1
    I'm not an Oracle guy, but maybe you should prefix your CREATE TABLE T_SCHEDULE with SYSTEM too. (CREATE TABLE SYSTEM.T_SCHEDULE)
    – McNets
    Oct 6, 2021 at 13:07

1 Answer 1

3

I have my SYSTEM user, who owns several tables

Do not use any pre-defined usernames that come with the database itself.
You have no idea what Oracle might choose to do there in the future, and if Oracle were to introduce a SYSTEM-owned table with the name as yours, then either your application or the database itself would be in a lot of trouble.

Create your own [Tablespace and] User Account and put your tables, etc., in that.

When I inserted a row in m1001's workspace ... it was successful. I can now view it with select query ... However, when I run this same query in SYSTEM user workspace I see the old version of the table

And the Magic Word is - COMMIT.

Oracle absolutely, totally, definitively WILL NOT allow one User to read uncommitted changes made by another User. Our Friends in Redwood City have spent millions building a database architecture that guarantees this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.