Tuesday 30 August 2016

How to get demonstration tables pupbld.sql?

SQL> create user abc identified by abc;

User created.

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
SYS
SYSTEM
ABC
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM

8 rows selected.

SQL> conn abc/abc
ERROR:
ORA-01045: user ABC lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

when connecting to user abc we gets above error since we didnt given privileges to the user abc.
SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to abc identified by abc;

Grant succeeded.

SQL> conn abc/abc
Connected.
SQL> show user;
USER is "ABC"
SQL> select * from tab;

no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@appsdb ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@appsdb admin]$ ls -ltr utlsampl.sql
-rw-r--r-- 1 oracle oinstall 3676 Jun  2  2006 utlsampl.sql
[oracle@appsdb admin]$ cd $ORACLE_HOME/sqlplus
[oracle@appsdb sqlplus]$ ls
admin  bin  doc  lib  mesg
[oracle@appsdb sqlplus]$ mkdir demo
[oracle@appsdb sqlplus]$ ls
admin  bin  demo  doc  lib  mesg

[oracle@appsdb admin]$ ls -ltr utlsampl.sql
-rw-r--r-- 1 oracle oinstall 3676 Jun  2  2006 utlsampl.sql
[oracle@appsdb admin]$ cp utlsampl.sql $ORACLE_HOME/sqlplus/demo/demobld.sql
[oracle@appsdb admin]$ cd $ORACLE_HOME/sqlplus/demo
[oracle@appsdb demo]$ ls -ltr demobld.sql
-rw-r--r-- 1 oracle oinstall 3676 Aug 30 17:13 demobld.sql

[oracle@appsdb demo]$ vi demobld.sql
after opening demobld.sql go to the line create table dept and place the cursor above create table dept 
and press d g g 
it will delete upper lines above create table dept and come to down remove exit.
above create table dept place these lines:

SET TERMOUT ON
PROMPT Building Demonstration Tables.Please Wait.
SET TERMOUT OFF
DROP TABLE DEPT;
DROP TABLE EMP;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

come to last line above commit place these lines:
CREATE TABLE DUMMY (DUMMY number);
INSERT INTO DUMMY VALUES(0);
COMMIT;
SET TERMOUT ON
PROMPT Building Demonstration Tables Build is Complete
:wq

SQL> @?/sqlplus/demo/demobld.sql
Building Demonstration Tables Please wait.
Building Demonstration Table Build is Complete
SQL> select * from tab;

TNAME                           TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                             TABLE
DUMMY                        TABLE
EMP                               TABLE
SALGRADE                  TABLE

so here we get the tables emp,dept,bonus,dummy,salgrade.











1 comment: