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
ADVERTISEMENT
Oct 11, 2012
Tablespace freespace query which will give output in terms of maxsize and not size allocated ?
View 14 Replies
View Related
Apr 29, 2012
which data dictionary view would you query to see the free extents in a tablespace.
i use dba_free_space;
SQL> select tablespace_name,sum(bytes),sum(blocks) from dba_free_space where
tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
SYSTEM 9830400 1200
View 2 Replies
View Related
Aug 9, 2013
I need to find the tablespace size (used and free) for a partitioned table
View 3 Replies
View Related
Feb 15, 2011
How to free up the tablespace size when it reaches its threshold (Max limit)?
Say for example USERS & SYSAUX tablespace reaches above 90%.
View 14 Replies
View Related
Jun 12, 2012
Our production DB version is 11.2.0.1 and we do use DBArtisian. Everyday morning we check table space usage using that tool and it generates the below listed query. It used to run in 15 secs. But since one week it is running for 5/6/8 mins. I have updated the statistics on sys objects and tuning advisor created the execution plan. So, now it is running in 2 mins. Nothing has been changed in the DB configuration. I see the same query running in secs in dev environment. understand what might be the issue and how could I improve the performance of this query.
SELECT SUB.TABLESPACE_NAME, SUB.STATUS, SUB.EXTENT_MANAGEMENT, SUB.SEGMENT_SPACE_MANAGEMENT, SUB.TOTAL_SPACE_MB, SUB.USED_SPACE_MB, SUB.FREE_SPACE_MB, SUB.PERCENT_FREE_SPACE, SUB.CONTENTS, SUB.TABLESPACE_GROUP, CASE WHEN SUB.TABLESPACE_NAME = P.VALUE AND SUB.CONTENTS = 'UNDO' THEN 'YES' ELSE 'NO' END
[code]...
View 5 Replies
View Related
Jun 3, 2013
I have about 200 tables, and each table has two columns : ( table_name_ID, local_id ).
example :
COST ( cost_id, local_id )
RATES ( rates_id, local_id )
SALARY ( salary_id, local_id )
I want the column local_id to be set to same value as table_id. ( local_id = cost_id for COST table; local_id = SALARY_ID in SALARY table etc.)
The code for trigger on these table will be generic and can be automtically generated.
But we are talking about 200 tables - means huge amount of testing with existing triggers on all these tables.
Is there any simpler method.
BTW - I purused following thread, and it talks about using AUDITING.
Re: Schema level Database triggers
But I wonder how AUDITING can be used in place of trigger to modify data ?
View 9 Replies
View Related
Dec 18, 2012
DB : 10/11g
OS : REHL
can we have a schema backed up into one rman piece?
View 1 Replies
View Related
Mar 24, 2013
Operating System - WindowXP
oracle version 10.2.0.1
I was learning some recovery part in my home laptop. Database is in Archivelog, flashback mode. All of sudden, i deleted it from OS level with out taking backup of it.
When i tried to open database, it failed to start. Database is in mount mode.while trying to open, it gives message -
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
I tried to create on file named "UNDOTBS01.DBF" but oracle is not recognizing it.
View 11 Replies
View Related
Jun 10, 2010
I have created trigger on database level in system schema. While i am creating new tables in system schema, trigger logged the entry but when i am creating table in scott schema it is not working for that.
CREATE OR REPLACE TRIGGER ddltrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO aud_log
(user_name, ddl_date, ddl_type, object_type,
owner, object_name
)
VALUES (ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name
);
END;
View 3 Replies
View Related
Jan 20, 2011
Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?
View 5 Replies
View Related
Feb 20, 2012
Quote:The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export and import.
what privileges are required to perform only schema level export and import?
View 3 Replies
View Related
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
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
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
View Related
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
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
Aug 19, 2011
Below is the output of Tom Kytes script show_space, which I have run on one of my indexes.
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 102,936
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 28,615,887
Total Blocks............................ 28,748,800
Total Bytes............................. 235,510,169,600
Total MBytes............................ 224,600
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 233
Last Used Ext BlockId................... 1,574,409
Last Used Block......................... 12,800
PL/SQL procedure successfully completed.
If I look at the unformatted blocks its zero, which tells me that data is being placed into every block (pretty well compressed). But what I don't understand is why there are 102,936 blocks that are only 25-50% full? I would have expected to see some blocks that in the75%-100% full range as this index was recently dropped and rebuilt 2 months ago.
This index is on a partitioned tabled, where the 90th day and higher partitions are dropped daily.
Here is the layout of the index
CREATE INDEX T1.FEAT_IDX ON T1.FEAT
(R_SEQ, SYSTEM_NAME, FEATURE, FLAG)
NOLOGGING
TABLESPACE TB1
[Code] .........
what I need to do to get the value of FS4 (# of block 75%-100% used higher)
View 3 Replies
View Related
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
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
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
Jul 11, 2012
I have schema level export for user SAMPLE1(Default tablespace USERS) on oracle 9.2.0.1 production database. I want to import into another 9i database on another server, so do i nneed to Create SAMPLE1 user and USERS tablespace in new database again.
View 5 Replies
View Related
Aug 1, 2011
when we ran SELECT statement against CUBE, we got below wait event: resmgr: cpu quantum.Further, we checked below 2 parameters :
NAME VALUE
------------------------- --------------------------------------------------
resource_limit TRUE
resource_manager_plan SCHEDULER[0x12B943C]:DEFAULT_MAINTENANCE_PLAN
[code]....
It has been found that these sessions did not get enough CPU to process the request. how to find out how many CPU has been allocated to this database ?
> uname -a
Linux dukedmts03db02.corp.cox.com 2.6.18-128.1.16.0.1.el5 #1 SMP Tue Jun 30 16:48:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
how to analyze how much % of CPU being utilized for a specific session ?
View 4 Replies
View Related
May 14, 2010
I have a data like,
1) manual_temp_master
auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010
2)manual_temp_detl
auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010
[code]....
I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:
sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010
View 8 Replies
View Related
Nov 17, 2010
what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.
View 1 Replies
View Related
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
Oct 22, 2012
Can you take an incremental backup level 1 or level 0 without archivelogs?
syntax would bebackup as compressed backupset cummulative level 1 database.
The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.
View 20 Replies
View Related
Apr 11, 2013
between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.
which will execute first on update DML event ?
View 2 Replies
View Related
Nov 30, 2006
i am looking for a free download by oracle meant specifically for Linux. i am using fedora core 5 32 bit version of Linux.
View 1 Replies
View Related
Apr 4, 2013
I have a business need to have a db function that would construct and return a (temporary) CLOB value.
here is its sample code:
create or replace package PKG_TEST_CLOB
as
function FN_TEST_TEMP_CLOB
return clob;
[code]....
when this function is invoked from a SQL Statement...
***
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB from dual;
***
... the NOCACHE_LOBS counter in V$TEMPORARY_LOBS for my session is incremented by 1
when this function is invoked via a PL/SQL block...
***
declare
l_clob clob;
begin
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB into l_clob from dual;
end;
/
declare
[code]....
... the counter doesn't budge
In real life, this function will be used by a Reporting Tool (cognos) via SQL. I tested it, and it seems that it is allocating a new temp lob segment with every invocation.
View 0 Replies
View Related
