I have created different scripts to monitor growth planning of the database my idea was generate a simple output file to a directory and create a report and send it as mail thru mail server. but the client requested me to create new schema and population all the stats i have collected into the tables in this schema .
How to pool all the stats from different databases into this new schema efficiently.
[URL]... says that session memory is maintained in the PGA (unlike shared server) and then gives a calculation for memory usage that I don't understand: it suggests that instead of there being one PGA per session, there is one PGA per pooled server plus a small allocation of 35k per session, but this is not what I see.
It's easy to demonstrate. As sys,
exec dbms_connection_pool.start_pool
and then launch any number of session with
sqlplus scott/tiger@127.0.0.1:1521/orcl:POOLED
and you'll see a process called Lnnn launched for each session:
[code]...
There is a mention of a process called the connection broker, but I can't see this in v$process.There is more detail in the OCI developers guide [URL]... which goes beyond my knowledge of application development.
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
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 Relatedi need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)
CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/
Meta table contains Table name and column name..i attached the table data in atext file
I have 10 important schemas in my database,and i want to give only select privileges from SCHEMA PRODUSER to other 10 schemas. And also want that the new objects that are created in PRODUSER after granting the privileges are also have select privileges. Is it possible that i should directly grant select privileges from one schema to other without granting via individual objects(script to grant individual privileges from individual objects)
Details are as below:
database version: 9.2.0.8.0
OS version:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2
we have a two different databases at different locations and on different servers, like one in our company with SID='A' and remote database with SID='B', We have recently implemented new module in database 'A' by creating lots of tables, functions, indexes, sequences, synonyms etc and now we need to install this on 'B' but the problem is we have not documented which tables we created, first we need to create a DB link between these two and then we need a tool to compare what are all the tables that we need to create in database 'B' , is there a tool for doing all this.
View 7 Replies View Relateda table can have many schema or just 1 schema which is the table owner?
View 5 Replies View RelatedI need to see all schema users password in a text.I am database admin,but it difficult to remember the password of all users created in single database So we also dont want to change password of all time,table to view password in text. Also we all know we can see using dba_users, but it was showing in hash value i need it in a text
View 3 Replies View RelatedI want to get clear with one thing yesterday i installed oracle9i and dev 2000 to my client.
when they run one report they got stuck with pl/sql compilation errorrep-1247
when i checked that report in the report builder, in the query they are using some other table which is not belongs to that schema,then I give that schema.tablename and compiled, but this is coming for other reports also, then only i came to know that they are acceessin other schema also, how can i sort this out.
can i fix this by givin full access privilige or what privilige can i give to get full access of other schema table.
how can i check in the old database what are all the roles and privileges given to this user,
difference between a user and schema on Oracle.By difference, I do not mean what is a user and what is a schema.
I understand that if we create a user, the schema gets created by default for that user. Does that mean that we cannot create a user/schema alone.
What if I just want to create a user and grant him some read access on an existing schema or just want to create a schema.
When we want to gather schema statistics which method we should follow and why ?
exec dbms_utility.compile_schema('SHIKHAR');
or
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'JACK'
[Code].....
I was importing one schema from Oracle 10g to 11g using traditional import. I imported as a SYS user, so all the objects created in SYS schema. how can I remove these objects and retain only default SYS objects
View 11 Replies View RelatedI 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 ?
I have exported data of one user an importing into another schema at another server. when i am trying to imoport it is working fine for quite no of imports into tables, but after some time it starts giving me below mention error...
IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<ے
IMP-00008: unrecognized statement in the export file:
+A
IMP-00008: unrecognized statement in the export file:
[code]...
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]....
I have problem/misunderstanding with gather schema stat utility of oracle. Herewith i'm posting my try and output of it. My main question is why the column 'LAST_ANALYZED' of dba_tables not updated on gathering fresh schema level statistics.
SQL>select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL
[code]...
10 rows selected.
SQL>execute dbms_stats.gather_schema_stats(OWNNAME => 'STO', OPTIONS => 'GATHER AUTO');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------- ----------- ---------
STO BILLSLIPB 7695 26 36 7695 29-MAR-10
STO BILL_CHECKING_SLIP 2634 71 28 2634 29-MAR-10
STO FACTORYBILLA 2 119 1 2 29-MAR-10
[code]...
10 rows selected. SQL>
Steps to Refresh test Oracle 9i schema from production schema on a Windows platform..
View 3 Replies View RelatedI am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.
SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;
*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1
[code]....
Presently have one instance, one Schema on my DB Server. Want to split the existing schema into 3 logical divisions. Have created the 3 Schemas. Through Oracle Export from the old Schema am able to Export (using wild cards) the tables and indices for each grouping and am able to Import into the new specific schema. The problem i'm having is that i cannot seem to Export the specific sequences due for each of the new schemas. Is there an easy way to accomplish this?
View 6 Replies View RelatedI am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.
For this I wanted to:
first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.
However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.
We transferred our Oracle database 11.1.0.7 from windows 2003 enterprise edition 32 bit to windows 2008 enterprise edition server 64 bit.Database is working fine but we have 53 uncompiled objects which are related to OLAPSYS and public as follows
OLAPSYSALL$OLAP2_AW_CUBE_AGG_LVLVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_MEASVIEW
OLAPSYSMRAC_OLAP2_AW_DIMENSIONS_VVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_OPVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_SPECSVIEW
[code]....
Our business objec is working fine and all other schema does not have any uncompiled objects.How can we validate the OLAPSYS and PUBLIC schema.
Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.
View 12 Replies View RelatedI am trying to move data from one schema in database A to another schema in database B and only move data not others (procedure, view, function). What is the best way to do it? I am thinking database link then insert into select from database link. Is there a better way?
View 8 Replies View RelatedThis message for my clarification,we have two schemas MAD_LIVE and MAD_TEST we want to take data from MAD_LIVE to MAD_TEST for particular period of time. way to import data from MAD_LIVE to MAD_TEST for particular period
My SCHEMA MAD_LIVE using from year 2010 till now,we want to take only 2012 data from MAD_LIVE and restore it in MAD_TEST.
How to load data to another schema's table through Sql*loader.
I tried the below things in the control file.
Load data
"
Into table scott.emp
I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).
For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?
Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.
We are currently using OCI to connect to Oracle DB using c language. Each process has its own dedicated Connection and Env handle, session handle ...
Right now we decided to use Connection Pooling. In the documentation , Connection pooling examples were using multi-threaded environment. But in our case its multi-process system and we cant modify products architecture to multi-thread.
I would like to know how we can use Connection Pooling on multiple processes.
I am trying to upgrade my database approx 20GB size from 8.1.7.2.1 to 8.1.7.4 runningon windows NT 32bit server.I found that we can download from metalink.oracle.comIs that any cost involved in downloading and installing or is it provided freely by Oracle.
View 7 Replies View Relatedhow to take all schema metadata export except one schema (scott)
can i use like EXCLUDE=schema:"IN('SCOTT')