PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

DBA Related Stuff 2

DBA Stuff Part - 1

SQL> cl scr 
 
SQL> SET SQLPROMPT DBASTUFF> 
DBASTUFF>cl scr 
 
DBASTUFF>COLUMN Empno FORMAT 9999 
DBASTUFF>COLUMN MGR FORMAT 9999 
DBASTUFF>COLUMN Sal FORMAT 9999 
DBASTUFF>COLUMN Comm FORMAT 9999 
DBASTUFF>COLUMN Deptno FORMAT 99 
DBASTUFF>cl scr 
 
DBASTUFF>SELECT * FROM Emp; 
SELECT * FROM Emp 
              * 
ERROR at line 1: 
ORA-00942: table or view does not exist  
 
 
DBASTUFF>DESC TAB 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 TNAME                                     NOT NULL VARCHAR2(30) 
 TABTYPE                                            VARCHAR2(7) 
 CLUSTERID                                          NUMBER 
 
DBASTUFF>SELECT * FROM TAB; 
 
no rows selected 
 
DBASTUFF>DESC USER_TABLES 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 TABLESPACE_NAME                                    VARCHAR2(30) 
 CLUSTER_NAME                                       VARCHAR2(30) 
 IOT_NAME                                           VARCHAR2(30) 
 PCT_FREE                                           NUMBER 
 PCT_USED                                           NUMBER 
 INI_TRANS                                          NUMBER 
 MAX_TRANS                                          NUMBER 
 INITIAL_EXTENT                                     NUMBER 
 NEXT_EXTENT                                        NUMBER 
 MIN_EXTENTS                                        NUMBER 
 MAX_EXTENTS                                        NUMBER 
 PCT_INCREASE                                       NUMBER 
 FREELISTS                                          NUMBER 
 FREELIST_GROUPS                                    NUMBER 
 LOGGING                                            VARCHAR2(3) 
 BACKED_UP                                          VARCHAR2(1) 
 NUM_ROWS                                           NUMBER 
 BLOCKS                                             NUMBER 
 EMPTY_BLOCKS                                       NUMBER 
 AVG_SPACE                                          NUMBER 
 CHAIN_CNT                                          NUMBER 
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER 
 NUM_FREELIST_BLOCKS                                NUMBER 
 DEGREE                                             VARCHAR2(10) 
 INSTANCES                                          VARCHAR2(10) 
 CACHE                                              VARCHAR2(5) 
 TABLE_LOCK                                         VARCHAR2(8) 
 SAMPLE_SIZE                                        NUMBER 
 LAST_ANALYZED                                      DATE 
 PARTITIONED                                        VARCHAR2(3) 
 IOT_TYPE                                           VARCHAR2(12) 
 TEMPORARY                                          VARCHAR2(1) 
 SECONDARY                                          VARCHAR2(1) 
 NESTED                                             VARCHAR2(3) 
 BUFFER_POOL                                        VARCHAR2(7) 
 ROW_MOVEMENT                                       VARCHAR2(8) 
 GLOBAL_STATS                                       VARCHAR2(3) 
 USER_STATS                                         VARCHAR2(3) 
 DURATION                                           VARCHAR2(15) 
 SKIP_CORRUPT                                       VARCHAR2(8) 
 MONITORING                                         VARCHAR2(3) 
 CLUSTER_OWNER                                      VARCHAR2(30) 
 DEPENDENCIES                                       VARCHAR2(8) 
 COMPRESSION                                        VARCHAR2(8) 
 DROPPED                                            VARCHAR2(3) 
 
DBASTUFF>SELECT * FROM USER_TABLES; 
 
no rows selected 
 
DBASTUFF>DESC USER_TAB_COLUMNS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 COLUMN_NAME                               NOT NULL VARCHAR2(30) 
 DATA_TYPE                                          VARCHAR2(106) 
 DATA_TYPE_MOD                                      VARCHAR2(3) 
 DATA_TYPE_OWNER                                    VARCHAR2(30) 
 DATA_LENGTH                               NOT NULL NUMBER 
 DATA_PRECISION                                     NUMBER 
 DATA_SCALE                                         NUMBER 
 NULLABLE                                           VARCHAR2(1) 
 COLUMN_ID                                          NUMBER 
 DEFAULT_LENGTH                                     NUMBER 
 DATA_DEFAULT                                       LONG 
 NUM_DISTINCT                                       NUMBER 
 LOW_VALUE                                          RAW(32) 
 HIGH_VALUE                                         RAW(32) 
 DENSITY                                            NUMBER 
 NUM_NULLS                                          NUMBER 
 NUM_BUCKETS                                        NUMBER 
 LAST_ANALYZED                                      DATE 
 SAMPLE_SIZE                                        NUMBER 
 CHARACTER_SET_NAME                                 VARCHAR2(44) 
 CHAR_COL_DECL_LENGTH                               NUMBER 
 GLOBAL_STATS                                       VARCHAR2(3) 
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER 
 CHAR_LENGTH                                        NUMBER 
 CHAR_USED                                          VARCHAR2(1) 
 V80_FMT_IMAGE                                      VARCHAR2(3) 
 DATA_UPGRADED                                      VARCHAR2(3) 
 HISTOGRAM                                          VARCHAR2(15) 
 
DBASTUFF>SELECT * FROM USER_TAB_COLUMNS; 
 
no rows selected 
 
DBASTUFF>DESC USER_CONSTRAINTS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 OWNER                                     NOT NULL VARCHAR2(30) 
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30) 
 CONSTRAINT_TYPE                                    VARCHAR2(1) 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 SEARCH_CONDITION                                   LONG 
 R_OWNER                                            VARCHAR2(30) 
 R_CONSTRAINT_NAME                                  VARCHAR2(30) 
 DELETE_RULE                                        VARCHAR2(9) 
 STATUS                                             VARCHAR2(8) 
 DEFERRABLE                                         VARCHAR2(14) 
 DEFERRED                                           VARCHAR2(9) 
 VALIDATED                                          VARCHAR2(13) 
 GENERATED                                          VARCHAR2(14) 
 BAD                                                VARCHAR2(3) 
 RELY                                               VARCHAR2(4) 
 LAST_CHANGE                                        DATE 
 INDEX_OWNER                                        VARCHAR2(30) 
 INDEX_NAME                                         VARCHAR2(30) 
 INVALID                                            VARCHAR2(7) 
 VIEW_RELATED                                       VARCHAR2(14) 
 
DBASTUFF>SELECT * FROM USER_CONSTRAINTS; 
 
no rows selected 
 
DBASTUFF>DESC USER_INDEXES 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 INDEX_NAME                                NOT NULL VARCHAR2(30) 
 INDEX_TYPE                                         VARCHAR2(27) 
 TABLE_OWNER                               NOT NULL VARCHAR2(30) 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 TABLE_TYPE                                         VARCHAR2(11) 
 UNIQUENESS                                         VARCHAR2(9) 
 COMPRESSION                                        VARCHAR2(8) 
 PREFIX_LENGTH                                      NUMBER 
 TABLESPACE_NAME                                    VARCHAR2(30) 
 INI_TRANS                                          NUMBER 
 MAX_TRANS                                          NUMBER 
 INITIAL_EXTENT                                     NUMBER 
 NEXT_EXTENT                                        NUMBER 
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER 
 PCT_INCREASE                                       NUMBER 
 PCT_THRESHOLD                                      NUMBER 
 INCLUDE_COLUMN                                     NUMBER 
 FREELISTS                                          NUMBER 
 FREELIST_GROUPS                                    NUMBER 
 PCT_FREE                                           NUMBER 
 LOGGING                                            VARCHAR2(3) 
 BLEVEL                                             NUMBER 
 LEAF_BLOCKS                                        NUMBER 
 DISTINCT_KEYS                                      NUMBER 
 AVG_LEAF_BLOCKS_PER_KEY                            NUMBER 
 AVG_DATA_BLOCKS_PER_KEY                            NUMBER 
 CLUSTERING_FACTOR                                  NUMBER 
 STATUS                                             VARCHAR2(8) 
 NUM_ROWS                                           NUMBER 
 SAMPLE_SIZE                                        NUMBER 
 LAST_ANALYZED                                      DATE 
 DEGREE                                             VARCHAR2(40) 
 INSTANCES                                          VARCHAR2(40) 
 PARTITIONED                                        VARCHAR2(3) 
 TEMPORARY                                          VARCHAR2(1) 
 GENERATED                                          VARCHAR2(1) 
 SECONDARY                                          VARCHAR2(1) 
 BUFFER_POOL                                        VARCHAR2(7) 
 USER_STATS                                         VARCHAR2(3) 
 DURATION                                           VARCHAR2(15) 
 PCT_DIRECT_ACCESS                                  NUMBER 
 ITYP_OWNER                                         VARCHAR2(30) 
 ITYP_NAME                                          VARCHAR2(30) 
 PARAMETERS                                         VARCHAR2(1000) 
 GLOBAL_STATS                                       VARCHAR2(3) 
 DOMIDX_STATUS                                      VARCHAR2(12) 
 DOMIDX_OPSTATUS                                    VARCHAR2(6) 
 FUNCIDX_STATUS                                     VARCHAR2(8) 
 JOIN_INDEX                                         VARCHAR2(3) 
 IOT_REDUNDANT_PKEY_ELIM                            VARCHAR2(3) 
 DROPPED                                            VARCHAR2(3) 
 
DBASTUFF>SELECT * FROM USER_INDEXES; 
 
no rows selected 
 
DBASTUFF>DESC USER_VIEWS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 VIEW_NAME                                 NOT NULL VARCHAR2(30) 
 TEXT_LENGTH                                        NUMBER 
 TEXT                                               LONG 
 TYPE_TEXT_LENGTH                                   NUMBER 
 TYPE_TEXT                                          VARCHAR2(4000) 
 OID_TEXT_LENGTH                                    NUMBER 
 OID_TEXT                                           VARCHAR2(4000) 
 VIEW_TYPE_OWNER                                    VARCHAR2(30) 
 VIEW_TYPE                                          VARCHAR2(30) 
 SUPERVIEW_NAME                                     VARCHAR2(30)
 
DBASTUFF>SELECT * FROM USER_VIEWS; 
 
no rows selected 
 
DBASTUFF>DESC USER_SEQUENCES 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30) 
 MIN_VALUE                                          NUMBER 
 MAX_VALUE                                          NUMBER 
 INCREMENT_BY                              NOT NULL NUMBER 
 CYCLE_FLAG                                         VARCHAR2(1) 
 ORDER_FLAG                                         VARCHAR2(1) 
 CACHE_SIZE                                NOT NULL NUMBER 
 LAST_NUMBER                               NOT NULL NUMBER 
 
DBASTUFF>SELECT * FROM USER_SEQUENCES; 
 
no rows selected 
 
DBASTUFF>cl scr 
 
DBASTUFF>CREATE TABLE SampleTAB 
  2    ( 
  3     SampID NUMBER(2), 
  4     SampName VARCHAR2(10), 
  5     SampDate DATE 
  6    ); 
CREATE TABLE SampleTAB 
* 
ERROR at line 1: 
ORA-01031: insufficient privileges  
 
 
DBASTUFF>cl scr 
 
DBASTUFF>SELECT * FROM TAB; 
 
no rows selected 
 
DBASTUFF>DESC USER_OBJECTS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 OBJECT_NAME                                        VARCHAR2(128) 
 SUBOBJECT_NAME                                     VARCHAR2(30) 
 OBJECT_ID                                          NUMBER 
 DATA_OBJECT_ID                                     NUMBER 
 OBJECT_TYPE                                        VARCHAR2(19) 
 CREATED                                            DATE 
 LAST_DDL_TIME                                      DATE 
 TIMESTAMP                                          VARCHAR2(19) 
 STATUS                                             VARCHAR2(7) 
 TEMPORARY                                          VARCHAR2(1) 
 GENERATED                                          VARCHAR2(1) 
 SECONDARY                                          VARCHAR2(1)
 
DBASTUFF>SELECT * FROM USER_OBJECTS; 
 
no rows selected 
 
DBASTUFF>cl scr 
 
DBASTUFF>SELECT * FROM TAB; 
 
no rows selected 
 
DBASTUFF>SELECT * FROM Emp; 
SELECT * FROM Emp 
              * 
ERROR at line 1: 
ORA-00942: table or view does not exist  
 
 
DBASTUFF>SELECT * FROM SCOTT.Emp; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
SELECT * FROM SCOTT.Dept 
                    * 
ERROR at line 1: 
ORA-00942: table or view does not exist  
 
 
DBASTUFF>DELETE FROM SCOTT.Emp 
  2    WHERE SCOTT.Emp.Empno = 7654; 
DELETE FROM SCOTT.Emp 
                  * 
ERROR at line 1: 
ORA-01031: insufficient privileges  

 
DBASTUFF>SPOOL OFF 
SQL> SET SQLPROMPT DBASTUFF> 
DBASTUFF>cl scr 
 
DBASTUFF>DESC USER_TAB_PRIVS_RECD 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 OWNER                                     NOT NULL VARCHAR2(30) 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 GRANTOR                                   NOT NULL VARCHAR2(30) 
 PRIVILEGE                                 NOT NULL VARCHAR2(40) 
 GRANTABLE                                          VARCHAR2(3) 
 HIERARCHY                                          VARCHAR2(3) 
 
DBASTUFF>COLUMN OWNER FORMAT A15 
DBASTUFF>COLUMN "Table" FORMAT A15 
DBASTUFF>COLUMN GRANTOR FORMAT A15 
DBASTUFF>COLUMN PRIVILEGE FORMAT A15 
DBASTUFF>SELECT 
  2   OWNER, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_RECD; 
 
no rows selected 
 
DBASTUFF>cl scr 
 
DBASTUFF>SELECT * FROM TAB; 
 
no rows selected 
 
DBASTUFF>COLUMN Empno FORMAT 9999 
DBASTUFF>COLUMN Sal FORMAT 9999 
DBASTUFF>COLUMN Comm FORMAT 9999 
DBASTUFF>COLUMN MGR FORMAT 9999 
DBASTUFF>COLUMN Deptno FORMAT 99 
DBASTUFF>cl scr 
 
DBASTUFF>SELECT 
  2   OWNER, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_RECD; 
 
OWNER           Table           GRANTOR         PRIVILEGE                        
--------------- --------------- --------------- ---------------                  
SCOTT           EMP             SCOTT           SELECT                           
 
DBASTUFF>SELECT * FROM Emp; 
SELECT * FROM Emp 
              * 
ERROR at line 1:
ORA-00942: table or view does not exist  
 
 
DBASTUFF>SELECT * FROM SCOTT.Emp; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
DBASTUFF>SELECT 
  2   OWNER, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_RECD; 
 
OWNER           Table           GRANTOR         PRIVILEGE                        
--------------- --------------- --------------- ---------------                  
SCOTT           DEPT            SCOTT           DELETE                           
SCOTT           DEPT            SCOTT           SELECT                           
SCOTT           EMP             SCOTT           SELECT                           
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
 
DBASTUFF>DELETE FROM SCOTT.Dept 
  2    WHERE SCOTT.Dept.Deptno = 30; 
DELETE FROM SCOTT.Dept 
* 
ERROR at line 1: 
ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated - child record  
found 
 
 
DBASTUFF>DELETE FROM SCOTT.Dept 
  2    WHERE SCOTT.Dept.Deptno = 40; 
 
1 row deleted. 
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
 
DBASTUFF>ROLLBACK; 
 
Rollback complete. 
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
 
DBASTUFF>INSERT INTO SCOTT.Dept 
  2    VALUES(50, 'SHIPPING', 'CHENNAI'); 
 
1 row created. 
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
    50 SHIPPING       CHENNAI                                                    
 
DBASTUFF>ROLLBACK; 
 
Rollback complete. 
 
DBASTUFF>SELECT * FROM SCOTT.Dept;
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
 
DBASTUFF>INSERT INTO SCOTT.Dept 
  2    VALUES(50, 'SHIPPING', 'CHENNAI'); 
INSERT INTO SCOTT.Dept 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.DEPT_PRIMARY_KEY) violated  
 
 
DBASTUFF>SELECT 
  2   OWNER, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_RECD; 
 
no rows selected 
 
DBASTUFF>cl scr 
 
DBASTUFF>SELECT 
  2   OWNER, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_RECD; 
 
no rows selected 
 
DBASTUFF>SELECT * FROM SCOTT.Emp; 
SELECT * FROM SCOTT.Emp 
                    * 
ERROR at line 1: 
ORA-00942: table or view does not exist   
 
DBASTUFF>CONN HR/HR 
Connected. 
DBASTUFF>CONN DBASTUFF/DBASTUFF 
Connected. 
DBASTUFF>SELECT * FROM SCOTT.Emp; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                   
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
SELECT * FROM SCOTT.Dept 
                    * 
ERROR at line 1: 
ORA-00942: table or view does not exist  
 
 
DBASTUFF>SELECT * FROM SCOTT.Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    50 SHIPPING       CHENNAI                                                    
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
 
DBASTUFF>SELECT * FROM SCOTT.Emp; 
SELECT * FROM SCOTT.Emp 
                    * 
ERROR at line 1: 
ORA-01031: insufficient privileges  
 
 
DBASTUFF>SELECT 
  2   USERNAME, 
  3   GRANTED_ROLE 
  4  FROM USER_ROLE_PRIVS; 
 
USERNAME                       GRANTED_ROLE                                      
------------------------------ ------------------------------                    
DBASTUFF                         EMPSEC                                            
 
DBASTUFF>SELECT 
  2   ROLE, 
  3   PRIVILEGE 
  4  FROM ROLE_TAB_PRIVS; 
 
no rows selected 
 
DBASTUFF>cl scr
 
DBASTUFF>SELECT * FROM Employee; 
SELECT * FROM Employee 
              * 
ERROR at line 1: 
ORA-00942: table or view does not exist  
 
 
DBASTUFF>SELECT * FROM SCOTT.Employee; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
DBASTUFF>SELECT * FROM Employee; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 

DBASTUFF>DESC Employee 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                              VARCHAR2(10) 
 JOB                                                VARCHAR2(9) 
 MGR                                                NUMBER(4) 
 HIREDATE                                           DATE 
 SAL                                                NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 DEPTNO                                    NOT NULL NUMBER(2) 
 
DBASTUFF>SELECT * FROM TAB; 
 
no rows selected 
 
DBASTUFF>SELECT * FROM USER_OBJECTS; 
 
no rows selected 
 
DBASTUFF>SPOOL OFF 

Go Back