update : 28-06-2017

DBA Script : example_create_user_tables.sql

-- | FILE : example_create_user_tables.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : example_create_user_tables.sql |
-- | CLASS : Examples |
-- | PURPOSE : Yet another SQL script that demonstrates how to sample tables |
-- | and a PL/SQL routine that populates those tables. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
/*
** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
/*
CONNECT /
CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/ORA901/users01.dbf' SIZE 10M
/
CREATE TABLESPACE idx
DATAFILE '/u09/app/oradata/ORA901/idx01.dbf' SIZE 10M
/
CREATE USER jhunter IDENTIFIED BY jhunter
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
/
GRANT dba, resource, connect TO jhunter
/
*/
/*
** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
CONNECT jhunter/jhunter
/*
** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
DROP TABLE user_names CASCADE CONSTRAINTS
/
CREATE TABLE user_names (
name_intr_no NUMBER(15)
, name VARCHAR2(30)
, age NUMBER(3)
, update_log_date DATE
)
TABLESPACE users
STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0
)
/
ALTER TABLE user_names
ADD CONSTRAINT user_names_pk PRIMARY KEY(name_intr_no)
USING INDEX
TABLESPACE idx
STORAGE (
INITIAL 28K
NEXT 28K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0
)
/
ALTER TABLE user_names
MODIFY ( name CONSTRAINT user_names_nn1 NOT NULL
, age CONSTRAINT user_names_nn2 NOT NULL
, update_log_date CONSTRAINT user_names_nn3 NOT NULL
)
/
/*
** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
DROP TABLE user_names_phone
/
CREATE TABLE user_names_phone (
name_intr_no number(15)
, phone_number varchar2(12)
, country_code varchar2(15)
)
TABLESPACE users
STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0
)
/
ALTER TABLE user_names_phone
ADD CONSTRAINT user_names_phone_pk PRIMARY KEY(name_intr_no, phone_number)
USING INDEX
TABLESPACE idx
STORAGE (
INITIAL 28K
NEXT 28K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0
)
/
ALTER TABLE user_names_phone
MODIFY ( country_code CONSTRAINT user_names_phone_nn1 NOT NULL
)
/
ALTER TABLE user_names_phone
ADD CONSTRAINT user_names_phone_fk1 FOREIGN KEY (name_intr_no)
REFERENCES user_names(name_intr_no)
/
/*
** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
DROP TABLE user_names_company
/
CREATE TABLE user_names_company (
name_intr_no number(15)
, company_code varchar2(15)
)
TABLESPACE users
STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 0
)
/
/*
** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/
create or replace procedure insert_user_names (
num_records IN number)
IS
CURSOR csr1 IS
SELECT max(name_intr_no)
FROM user_names;
max_intr_no NUMBER;
BEGIN
/*
|| ENABLE DBMS_OUTPUT
*/
DBMS_OUTPUT.ENABLE;
/*
|| SET ROLLBACK SEGMENT TO THE LARGEST ONE: rbs2
*/
DECLARE
incomplete_transaction EXCEPTION;
pragma EXCEPTION_INIT (incomplete_transaction, -01453);
BEGIN
SET TRANSACTION USE ROLLBACK SEGMENT rbs2;
EXCEPTION
WHEN incomplete_transaction THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Needed to rollback previous transaction');
SET TRANSACTION USE ROLLBACK SEGMENT rbs2;
END;
/*
|| INSERT DUMMY RECORDS INTO THE USER DEFINED
|| TABLE: user_names. THE FIRST PARAMETER TO THIS
|| FUNCTION WILL DETERMINE THE NUMBER OF RECORDS
|| TO INSERT.
*/
DECLARE
fail_rollback_segment EXCEPTION;
pragma EXCEPTION_INIT (fail_rollback_segment, -01562);
BEGIN
OPEN csr1;
FETCH csr1 INTO max_intr_no;
CLOSE csr1;
max_intr_no := NVL(max_intr_no,0) + 1;
FOR loop_index IN max_intr_no .. (max_intr_no + (num_records-1))
LOOP
INSERT INTO user_names
VALUES (loop_index, 'Oracle DBA', 30, SYSDATE);
INSERT INTO user_names_phone
VALUES (loop_index, '412-555-1234', 'USA');
INSERT INTO user_names_company
VALUES (loop_index, 'DBA Zone');
IF (MOD(loop_index, 100) = 0) THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Commit point reached at: ' || loop_index || '.');
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Successfully inserted ' || num_records ||
' records into table: user_names');
EXCEPTION
WHEN fail_rollback_segment THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Fail to extent rollback segment: RBS2');
END;
END;
/
show errors;

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.