#tablespace

Tabletop Enquirer:

#설명

자주 사용하고 자주 까먹는 테이블 스페이스 관련 쿼리들을 정리해보았다

Movendo objetos no Postgres

Quando você cria um objeto seja ele uma tabela, visão, função, etc, a não ser que você especifique o contrário, ele vai ser criado no esquema ‘public’, no tablespace ‘pg_default’ e o donodo objeto será o ‘postgres’. Dependendo da aplicação, não há nenhum problema nisso. Sério, existem aplicações que funcionam muito bem com este arranjo padrão. Mas você pode querer organizar melhor os seus objetos…

How to change all table to other tablespace

Moving Table Partitions

You can use the MOVE PARTITION clause to move a partition. For example, a DBA wishes to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O). The DBA can issue the following statement:

This statement always drops the partition’s old segment and creates a new segment, even if you don’t specify a new tablespace.

When the…

New Post has been published on http://www.varindersandhu.in/2013/04/29/oracle-create-tablespace-and-user-schema/

Oracle – Create tablespace and User / Schema

Creating a tablespace and user is a very common task after creating an oracle database. Before going further must read the post Schema vs. User

In this post we will see how to create tablespace and user with example

Tablespace

Each tablespace in an Oracle database consists of one or more files called data files.

A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.

Script 1: Create Tablespace

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

CREATE TABLESPACE "test01" LOGGING DATAFILE 'D:\myTBS\ test01.ora' SIZE 10M AUTOEXTEND ON NEXT 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.

Script 2: Create Temporary Tablespace

CREATE TEMPORARY TABLESPACE "test01_TEMP" TEMPFILE 'D:\myTBS\ test01 _TEMP.ora' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

Script 3: Create User

CREATE USER " testuser" PROFILE "DEFAULT" IDENTIFIED BY "TEST123" DEFAULT TABLESPACE " test01" TEMPORARY TABLESPACE " test01_TEMP" ACCOUNT UNLOCK;

Using the GRANT statement one can authorize database users to do the specific task on the system.

Script 4 : Grant to User

GRANT "CONNECT" TO " testuser "; GRANT "RESOURCE" TO " testuser "; GRANT "DBA" TO " testuser "; GRANT create any table to testuser;

How to check total avaible on tablespace from user Oracle

–Normal, if you want to check space for tablespace in oracle, you must log in to server oracle and use query. You can used sqlplus to query from client:

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 26 14:02:03 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real…