Results 1 to 6 of 6

Thread: New Table Space

  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: New Table Space

    Hi

    I am creating new tables in my existing DB, and every table is getting created in the SYSTEM tablespace.

    I want the new tables to get created in a new tablespace called USER. Where should I issued commands for the same, and also does it require the DB to be shutdown. Also I need to know if I could set a limit for the tablespace to be 200Mb as the information in the tables would be static for a while, as it is being used to generate reports.

    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    write after your script

    TABLESPACE USER

    eg.



    Create Table TESR.CLI (
    CLIENT_ID VARCHAR2 (9) NOT NULL,
    NAME VARCHAR2 (30) NOT NULL
    )

    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    TABLESPACE TESR
    STORAGE (
    INITIAL 128K
    NEXT 128K
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 4096
    FREELISTS 1
    FREELIST GROUPS 1)


    you do'nt need to stop your database


    Regards
    Philippe

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    There is a clause in the create table/index for specifying tablespace. You can also use alter table move & alter index rebuild to move tables/indexes to different tablespaces. And no you dont have to shutdown the database.

    As for tablespace size use the create tablespace command (or alter database datafile) to specify the size of the datafile (or maximum size if autoextend is on).

    Try the Oracle Concepts manual and SQL reference manual on technet for more info and syntax.

    Alan

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Hi

    Hi

    Actually I want to take the question to a second level, and want to know how this would be done, and would it be a performance issue on a live production server.

    Sp I have 2 table spaces, one SYSTEM (oracle default) and other USER (mine).
    Suppose for some reasons, I have 10 tables in the SYSTEM tablespace existing, and I need to get them transferred to the USER tablespace. Is it possible. Also would it pose any issues on get the data copied or transferred somehow.

    Thanx and Regards
    Aruneesh

  5. #5
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    The answer is above

    Alan says
    "You can also use alter table move & alter index rebuild to move tables/indexes to different tablespaces".

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    alter user user_name default tablespace tablespace_name;

    alter user user_name quota unlimited on tablespace_name;

    alter user user_name temporary tablespace default_temp;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •