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
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.
It 's excellent posting and useful for developers
ReplyDeleteSql server DBA Online Course Hyderabad