Multiple Schema In A Tablespace

Sep 10, 2010

I'm having issues with things disappearing from my database. I know what you are thinking, OK Crazy things don't just disappear. The only thing I can think of that could be a possible problem is the way the oracle server is set up. (For the record, I don't agree with this set up. I was like this when I started with the company and they don't want to change it "because it works").

We currently have 90 + schemas on the server all in one tablespace. So the server has a data tablespace and an index tablespace. Every schema shares those tablespaces. It gets much worse that this but I'm not going to get into it.

Do you think that this set up would cause things to become missing?

View 6 Replies


ADVERTISEMENT

Server Administration :: Single Big Tablespace Versus Multiple Tablespace?

Jan 26, 2011

My database version is

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

My os version is

Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux

My database is OLP system.

My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?

Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.

View 7 Replies View Related

Server Administration :: Accessing Single Schema From Multiple Schema Logging?

May 16, 2011

A single master schema where many developers are accessing. all share same password.

now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema

View 2 Replies View Related

Server Administration :: One Schema Versus Multiple Schema

Feb 1, 2010

single schema setup or multiple schema setups for an application development. Which option is recommended and pros and cons of these methods?

View 4 Replies View Related

SQL & PL/SQL :: Schema Vs Tablespace?

May 2, 2010

what is the data dictionary table to find out the default table space and temporary table space for a user.Let say i have a user called 'Pointers'.How to find out the default table space and temporary table space for this.I have checked in 'dict' table but no luck.

SELECT *
FROM dict
WHERE table_name LIKE '%TABLESPACE%'

View 4 Replies View Related

How To Delete Schema (Tablespace)

Jun 15, 2007

i have used toad to create a tablespace say XYZ and user ABC from the system schema.

Now i want to delete this tablespace xyz.

View 1 Replies View Related

PL/SQL :: Moving Schema From One To Another Tablespace?

Oct 30, 2012

We need to move schema from one tablespace to another tablespace.

we are going to use following method:

alter table <tab> move tablespace <new_tbsp>;

my question is do we need to disbale primary keys and foreign keys before move and enable them at end.

View 4 Replies View Related

Changing Tablespace Of Schema

Oct 26, 2012

I have created my schema in SYSTEM schema way back accidentally by forgetting to specify the database tablespace. There is about 60 objects (tables, indexes, functions etc) and 6MB Of data.

What would be the best way migrate the schema to the tablespace I planned to put it into? .

View 7 Replies View Related

PL/SQL :: Tablespace Allocated / Used / Free And % - Used By Every Schema Level

Mar 11, 2013

One tablespace used in two different schemas ,in this case how much space used,free space and persentage of used in each schema level.

Example: USERS tablespace used in two different schemas HR,SCOTT. in this suche case to find the total size of tablespace, used and free space of each schema level.I used the below query not giving appropriate result.

SELECT TABLESPACE_NAME,
OWNER, round(SUM(BYTES/1024/1024),2) "Used Space in MB"
FROM DBA_SEGMENTS
where tablespace_name not in ('SYSTEM','TEMP','SYSAUX','EXAMPLE','UNDOTBS1') 
group by tablespace_name,owner
order by tablespace_name

View 4 Replies View Related

Backup & Recovery :: Tablespace Versus Schema Export

Aug 22, 2012

I have taken the tablespace export it came 2.1gb and for the same user i have taken the schema export it came 5.1gb

why their is a lot of difference in size?

View 3 Replies View Related

Server Administration :: USERS Tablespace Should Not Contain Schema Objects?

Jan 31, 2012

I heard that USERS tablespace should not contain any other application schema objects.

If the above statements is true , why it should not contain other schema objects ?

View 7 Replies View Related

Security :: Generating Traffic Report Of Tablespace Or Schema?

Nov 11, 2013

 at an Oracle 11gR2 (11.2.0.3.0) Enterprise Edition (fully licensed) with RHEL 6 as OS

I'm trying to generate a statistic, that shows me a weekly or monthly report over the traffic on a tablespace or a schema. Until now, I've worked with the

HIST_VIEW dba_hist_tbspc_space_usage

(I mean there is also one nearly called so for schema), but that view show me only the grow of a tablespace in a timeline of 1 week.

But that would not be the hole traffic. Because SELECT statements will nothing do on the grow of the tablespace but it generates traffic. In that case it wouldn't be so important, if it is more tablespace or schema sided because at this database each tablespace has it's own schema and timeline would be nice if it is weekly or monthly.

View 5 Replies View Related

Merge Over Multiple Schema

Aug 31, 2007

Procedure A and Table A are located in Schema A. Procedure A performs a merge between View B of Schema B and Table A. Procedure A is giving a ORA-00942(table or view does not exist) for the following line ... USING (SELECT * FROM B.VIEWB) D

Code excerpt
---------------------
MERGE INTO A.TableA C
USING (SELECT * FROM B.ViewB) D
ON (C.dealerid = D.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

C.dealername = D.di_dealer_name,
C.dealerno = D.di_dealer_no,
C.fedid = D.di_fed_id,
C.oldate = D.di_online_dte, ...

Permissions given are as follows

Schema A
----------------
ProcedureA - execute on User A
TableA- Insert, Delete, Update, Select on User A

Schema B
----------------
ViewB - Select on User A, Select on UserARole

I believe appropriate permissions have been given.

View 1 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Server Utilities :: How To Import Dump Into Specific Tablespace Instead Of Default Tablespace Users

Jan 18, 2012

How to import dump into specific tablespace instead of default tablespace users.

I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .

View 2 Replies View Related

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?

creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces

View 9 Replies View Related

Security :: Schema Consolidation And User Schema Mapping Based On Service

Jul 24, 2011

We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.

Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.

Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.

View 5 Replies View Related

Client Tools :: How To Create A Table In Another Schema As In Existing Schema

Apr 26, 2010

I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.

How could I do this without using export import. I am using TOAD 9.0.1.

View 10 Replies View Related

Server Utilities :: How To Take All Schema Metadata Export Except One Schema (scott)

Jul 5, 2012

how to take all schema metadata export except one schema (scott)

can i use like EXCLUDE=schema:"IN('SCOTT')

View 4 Replies View Related

Export/Import/SQL Loader :: Imp Records Of One Schema Into Another Schema Of Same Table

Nov 3, 2012

I had done following steps,

schemas(toy,toys)

1) i open the session of toy schema

First i taken backup of table

create table bck20121103_himan as select * from himan;

Backup table is created.

After taking the Backup table

delete himan;(deleting the records)

2) i log in to another session(toys)

exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/

3) i log in to toy schema

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y

i tried the above statement it taken so much of time..

Later i tried

I log in to toy session

i rename the table with other name.

later i imported

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y

it's successfully imported.

View 3 Replies View Related

Server Utilities :: How To Move Schema To Another Schema In Same Database

Jan 5, 2009

move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.

View 14 Replies View Related

Sequence Name In Oracle Database Schema As Well As Timesten Schema?

Jan 13, 2011

How to use same oracle sequence name in Oracle Database schema as well as timesten schema?

View 1 Replies View Related

SQL & PL/SQL :: Accessing Schema Object From Another Schema?

Apr 20, 2010

I have create one procedure under my user schema. In that procedure , I am selecting data from another schema's table.

While compiling that I am getting following error->

PL/SQL: ORA-00942: table or view does not exist
PL/SQL: Statement ignored

how I grant access of one schema object to another schema. Currently I am using oracle 10g

View 2 Replies View Related

How To Fire A Schema Trigger From Outside Schema

Sep 24, 2012

A user is using an ad hoc tool similar to SQL Developer called PeopleSoft Application Designer.

He creates a connection to the db, then issues an alter session set current_schema = 'restricted_schema'. The connected user does not have direct privileges on the "restricted_schema" which they call SYSADM.

After changing the schema context in that manner he creates objects in SYSADM. A schema trigger is then fired and grants privileges on the new objects created in SYSADM. Doing the same in either SQL Plus or SQL Developer does not fire the schema trigger.

I think SQL Plus and SQL Dev are working as they should. Altering the session like that does not change your identity - just the schema context. But, when you examine v_$session, the connection with this other tool looks exactly the same as one from SQL Plus or SQL Dev when changing the schema context in the session.

Instead of trying to figure out what this other tool is doing, is there any way for that schema trigger to fire when using this process from one of our tools?

View 6 Replies View Related

Server Administration :: Convert Dictionary Managed Tablespace To Locally Managed Tablespace?

Jan 6, 2012

I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.

The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.

This database is a mission critical database and very less downtime can be allowed.

View 6 Replies View Related

Server Utilities :: Compare Column Name Of Schema With Other Schema's Column Name

Dec 15, 2012

I have a standard schema named ABC and 600 more schema's over there in my database.They all has same table name and column name as on standard schema. But in some tables number of columns varying. So I need to compare all schemas with my standard schemas column name. I create below script but it is generating output in infinite loop.

SET SERVEROUTPUT ON
DECLARE
V_COLS VARCHAR2(20);

BEGIN
FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER FROM ALL_TABLES
WHERE OWNER LIKE 'CCD_MAIN'
[code]....

View 16 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

Server Administration :: Multiple Database With Multiple Instance On Same Machine?

Mar 16, 2011

can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?

View 15 Replies View Related

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved