Imp Concepts

Create New Table from Existing Table.

CREATE TABLE <databasename>.<tablename> AS
(Select   <columnnames>
FROM    <tablename>
WHERE condition)
with data;

CREATE TABLE <databasename>.<tablename> AS
(Select   <columnnames>
FROM    <tablename>
WHERE condition)
with no data;

What is Perm space , Spool space and Temp space.

Perm space=> It is a maximum amount of disk space for storing user data rows in any table located in the database. However, if no tables are stored within a database,  it is not required to have PERM space. Although a database without PERM space cannot store tables, it can store views and macros because they are physically stored in the Data Dictionary (DD) PERM space and require no user storage space. The DD is in a “database” called DBC.

Spool Space=> SPOOL space is workspace used for the temporary storage of rows during the execution of user SQL statements. unused PERM space is automatically available for use as SPOOL.

Temp Space=> TEMP space is allocated to any databases/users where Global Temporary Tables are used. Unused perm space is available for TEMP space.