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? .
I want to send mail whenever the schema password is changed by the developers (clients) . I created this trigger to send mail. I am able to know which schema's password is changed but i want know how to get the client details who is changing the password.
Create Trigger Pwd_chg_trigger
before alter
on database
declare
begin
if (ora_dict_obj_type = 'USER') then
DBMS_OUTPUT.PUT_LINE(' ORA_DICT_OBJ_OWNER = ' || ORA_DICT_OBJ_NAME); -- to know which schema
-- using the above value and additional formatting i am sending the mail using my SMTP
end if;
end;
/
Our product uses Oracle11gR1 and in new release we are going to use Oracle11gR2. For this we are performing following steps:
(1) Install Oracle11gR2 on a machine where our product (Oracle11gR1) is already installed.
(2)Upgrade Oracle11gR1 schema to Oracle11gR2.
(3)For using upgraded schema in our product installer we create clone of upgraded schema.
(4)For creating clone we are using Oracle11gR2 DBCA utility.
(5) Clone files are successfully created (DBC, CTL, DBF).
Now we performed same steps from another machine and DBF file size changed very much. On one machine it was 89 MB and on second machine it was 150 MB. There is no different in schema and both machines are Windows 7 machines.
Tor educe size of schema we tried different space reclamation commands but size is not changing.
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%'
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.
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?
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.
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
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?
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 ?
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.
Using Application Express 4.2.1.00.08
Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.
The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.
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]....
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 .
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
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.
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
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.
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.
how to take all schema metadata export except one schema (scott)
can i use like EXCLUDE=schema:"IN('SCOTT')
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.
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 RelatedHow to use same oracle sequence name in Oracle Database schema as well as timesten schema?
View 1 Replies View Relatedsingle 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 RelatedI 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
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?
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.
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]....
I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).
what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.
I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
View 4 Replies View Related