Better late than never!: 02/01/2010

Thursday, February 25, 2010

Create tablespace, user, table in oracle 10g

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 25 15:19:56 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLESPACE mytbs DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/mytbs.dbf' SIZE 10M;

Tablespace created.

SQL> show user
USER is "SYS"
SQL>
SQL> CREATE USER ora1 IDENTIFIED BY ora1 DEFAULT TABLESPACE mytbs;

User created.

SQL> exit

[oracle@localhost ~]$ sqlplus ora1/ora1
ERROR:
ORA-01045: user ORA1 lacks CREATE SESSION privilege; logon denied

Enter user-name:

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> GRANT CONNECT TO ora1;

Grant succeeded.
SQL> connect ora1/ora1
Connected.
SQL> show user
USER is "ORA1"
SQL> CREATE TABLE ora1.table1(col1 number);
CREATE TABLE ora1.table1(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> exit

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> show user
USER is "SYS"
SQL> GRANT CREATE TABLE TO ora1;

Grant succeeded.

SQL> connect ora1/ora1
Connected.

SQL> show user
USER is "ORA1"

SQL> CREATE TABLE ora1.table1(col1 number);
CREATE TABLE ora1.table1(col1 number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'MYTBS'

SQL>exit

[oracle@localhost ~]$ sqlplus sys/oracle as sysdba
SQL> show user
USER is "SYS"
SQL> ALTER USER ora1 QUOTA UNLIMITED ON mytbs;

User altered.

SQL> connect ora1/ora1
Connected.
SQL> CREATE TABLE ora1.table1(col1 number);

Table created.

SQL>

Monday, February 8, 2010

Oracle Answer

Instructor: Chalit Pojarutsangkul [cpojarut@yahoo.com]

Answer question or choose the best choice. Some of the questions, you can use the EM GUI to simulate and click the SHOW SQL button to see the SQL command.

1. Use dbca to create a database named orcl3 with instance name orcl3. Have the dbca generate scripts which will create database for you. There are many scripts created by the dbca. You need to execute just one file, it will execute the rest and your database will be created. What is the one file you need to execute?

Answer.

The one file that we have to execute is orcl3.sh.

2. In what environment would you execute that file? At the o/s prompt or inside SQL*PLUS?

Answer.

We use o/s prompt to execute that file.

3. You want to make sure next time you startup, the parameter JOB_QUEUE_PROCESSES is set to 15 without affecting current setting of this parameter. What is the SQL command you will issued?

Answer.

1-SQL>CREATE PFILE FROM SPFILE;
2-SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=15 SCOPE=SPFILE;

4. What is the SQL command to create a tablespace MYTBS with the file named /u01/app/mytbs.dbf. The file size should be 10m. The tablespace is locally managed and with automatic space management.

Answer is D.

A. CREATE TABLESPACE mytbs DATAFILE ‘/u01/app/mytbs.dbf’ SIZE 10M EXTENT MANAGEMENT LOCAL AUTOMATIC SEGMENT SPACE MANAGEMENT

B. CREATE TABLESPACE mytbs DATAFILE ‘u01/app/mytbs.dbf’ SIZE 10 LOCAL MANAGEMENT EXTENT AUTOMATIC SEGMENT SPACE MANAGEMENT

C. CREATE TABLESPACE mytbs DATAFILE ‘u01/app/mytbs.dbf’ SIZE 10M EXTENT LOCALLY MANAGED SEGMENT SPACE MANAGEMENT AUTO

D. CREATE TABLESPACE mytbs DATAFILE ‘u01/app/mytbs.dbf’ SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO