I have a query ,
I need to create a structure DATABASE=>SCHEMA=>TABLE as
DB=>SC=>EMPLOYEE ...but after connecting database i could create table only user my user schema(own schema)only . I want to create a new schema called SC as public and need to create a table .
how can i grant a user permission to create tables and indexes in other user schema.Grant create table permits the user to create tables in his own schema which is part of Resource role.Grant any table will permit him to create table in any schema including system which i don't want.
View 8 Replies View RelatedI am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
View 4 Replies View RelatedWe 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.
We have a vendor-supplied 11g database where records are split between two schemas -- an ACTIVE schema and an ARCHIVE schema. Each object has both a corresponding object in both of the ACTIVE and ARCHIVE schemas.
The vendor also has a third schema where each object is merely a UNION ALL of the associated ACTIVE and ARCHIVE schema objects. For the sake of example, I'll call that schema COMBO.Over the years, we've created queries and reports that reference both the COMBO and ARCHIVE schemas and that has worked just fine.
The vendor has now set up a secondary database for us that we can use when the primary database is offline for patching/upgrades/etc. The trouble is, this secondary database only has the ACTIVE schema and records. The vendor will not be writing any ARCHIVE records to it.
Primary DB: ACTIVE, ARCHIVE, and COMBO schemas
Secondary DB: only the ACTIVE schema.
is there a way to set up the missing ARCHIVE and COMBO schemas on the secondary DB such that we won't have to rewrite our SQL to accomodate the lack of an ARCHIVE schema when we move reports over to the backup database?
Of course, no records would need to be returned from the virtual ARCHIVE schema, but I'd love for the untouched SQL to run without error.
I'm supposed to create a database for an application. The server where this will be running previously had a database for a pretty similar app. I don't know much about Oracle so I reused the ZFS filesystems and left them how they were created (because honestly, I didn't knew why they were created that way, but I'm pretty sure it was for a good reason).
CODEapp/oradata_smart_ora1 858M 12.2G 858M /oradata/SMART/ora1
app/oradata_smart_ora2 7.18M 18.0G 7.18M /oradata/SMART/ora2
app/oradata_smart_ora3 7.18M 36.0G 7.18M /oradata/SMART/ora3
app/oradata_smart_ora4 60.6K 400G 60.6K /oradata/SMART/ora4
app/oradata_smart_redo1 400M 2.61G 400M /oradata/SMART/redo1
app/oradata_smart_redo2 200M 2.80G 200M /oradata/SMART/redo2
app/oradata_smart_redo3 200M 2.80G 200M /oradata/SMART/redo3
Since I reused the filesystems I created my database and placed the controlfiles in the same places where the old database files were (/oradata/SMART/ora1,/oradata/SMART/ora2,/oradata/SMART/ora3). Thinking like MySQL works I created CODEapp/oradata_smart_ora4 60.6K 400G 60.6K /oradata/SMART/ora4 specifically to store the database there.
The databases startups and mounts no problem. Note that this server will manage with millions/billions records throughout its lifetime.
1. Now that my Database is created, whats the next step? Create the Schema or Tablespace?
2. Tablespace Questions: Tablespace datafile(s) is where actual data from tables is stored? how many are needed? Default or Temporary? How much space will I need for it? Autoextend?
I have SQL Server database I would like to migrate into Oracle. The database supports a large application. It is around 10GB. I requested a new instance but was advised I would have to pay for that but if I asked for a new Schema it could go in our current company instance. I am fine with that since it wont cost more money if I just add a new schema to our Company Oracle instance. Just curious what is the advantage of getting a new instance compared to creating a new schema for 10 GB of data?I assume the advantage of creating a new instance is our Schema (in new instance) and work will have its own space/house and can grow in size without any issues?
View 3 Replies View RelatedJust installed the new 2.0.8 version. Its been a while I am interested on using PM for our company.The problem I have is creating an Oracle DB connection. When I create a new Oracle DB connection, and test it, Steps 3 and 4 fail.What is strange is that I followed the following procedure in PM 1.9-825 and worked fine. I am wondering if something changed in the new PM 2.0.8 version (I noticed that php_oci8.dll was commented out in php.ini).
Oracle Version is 10GR2
PM installed on clean XP sp3
1. Got a new Windows XP SP3 PC
2. Installed PM 2.0.8
3. Edited php.ini and uncommented [PHP_OCI8] and extension=php_oci8.dll
4. Installed oracle instant client “instantclient-basic-win32-10.2.0.5” in c:oracle
5. Installed oracle instant client “instantclient-sqlplus-win32-10.2.0.4” in c:oracle
6. Added c:oracle in PATH environment variable
7. Created ORACLE_HOME = c:oracle environment variable
8. Added TNS_ADMIN = c: environment variable
9. Copied working tnsnames.ora file into c:
10. Rebooted.
-------------------------------------
1. Tried sqlplus connection to my oracle db and worked.
2. Launched PM
3. Created a new process
4. Created a new DB connection as follows:
a. Engine:Oracle
b. Server:192.168.xxx.xxx
c. Database name: my_name (where my_name appears in tns_names.ora)
d. Username:my_username
e. Password:my_password
[code]....
I do a schema in oracle ... an m my question is if I can create 2 o more users for access to a schema..for example i have schema Project and i need that one administrator and 2 more users with others permission.
View 3 Replies View RelatedTo make users under one schema.
1) user should have access of all objects with limited privileges.
How can i make it under schema?
I'm trying to hide the password for the batch programs that connect to the DB Server
as Cadot pointed out in
[URL].........
Quote:
use secure external password store
with reference to
[URL].........
when I create wallet, the system does not prompt me for password
C:>mkstore -wrl "C:ora102NETWORKADMIN" -create
when creating login credentials, again the system never prompts me for password
C:>mkstore -wrl "C:ora102NETWORKADMIN" -createCredential db10g scott tiger
here's my sqlnet.ora configurations
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY =C:ora102NETWORKADMIN)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0
here's my tnsname.ora settings
DB10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mike)
)
)
here's the outcome
C:Documents and SettingsAdministrator>sqlplus /@db10g
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 13 22:53:06 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-12534: TNS:operation not supported
Enter user-name:
so I Google around for the solution to the ORA-12534 error, one of the site,
[URL].......
here's my lsnrctl services
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "MIKEXDB" has 1 instance(s).
Instance "mike", status READY, has 1 handler(s) for this service...
Handler(s):
[code].....
The command completed successfully
right now I think I will be a fool to think that the solution is to resolve the ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. so what is wrong with my setup, or is it some patch that I need to apply?
I've created a password verification function (verify_pwd) in a schema which is not in SYS, but an equivalent of SYS. However, the problem arises when I'm trying to create a profie (MAIN_PROFILE) with the following attributes :
CREATE PROFILE MAIN_PROFILE LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_VERIFY_FUNCTION verify_pwd;
The above script is resulting in an error.
ORA-02376 : invalid or redundant resource...Can I create the function verify_pwd not in the schema SYS but instead in a schema equivalent to SYS?
how to check that audit is enabled for any particular schema.
The below is my audit parameter from database level:-
SQL>SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /db17/dbdump/xxxx/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB_EXTENDED
I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.
I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.
Enabling of auditing in oracle database, can this be done on some tables or applied on the whole schema/user.
View 5 Replies View Related 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.
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.
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.
how can we mask value of some columns in table? For example: user A is supervisor, he can query salary column in employee table, but for user B, he is staff member, he can query salary column but system just shows ***** or something like that for salary value.
View 6 Replies View RelatedI 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]....
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies View RelatedCan we update a table using another schema table of same style?
Example:
Update employees
set employee_name=hr.employees.employees_name
where employee_id=100;
how to i insert a constraint of words into the table example below.I am new to this stuff man.
Create table Orders
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)
);
The catch is I am required to enter a constraint of the methPmt will only take values of "CASH", "CREDIT" or "CHEQUE" only.
How am I suppose to enter this constraint value into the creation of this table?
I have a directory defined as TDM_IN where a directory has been created. There is a table which has been created as
create table abc.IFP_GRP_D_INT
(
IFP_GRP_DIM_KEY VARCHAR2(10),
.....
[Code]...
Now how can i select the records from this table?
I have a table which contains secret data, i want that nobody can query,insert,update or delete that table, we can do by creating a table and giving rights to specific person but problem is that our programmers can query that table while working on that database.
Is there any other event or trigger which check that if person is performing DML on secret table then an error may generate.
One of our auditing recommendation is to move table AUD$ to a separate tablespace from system. Why this recommendation is important and how to do this action ?
View 2 Replies View RelatedIf i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...
ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,
[code]....
Having trouble creating a trigger to populate another table.
The SQL:
CREATE OR REPLACE TRIGGER "P_M_YES"
AFTER INSERT OR UPDATE ON DOMAIN
REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M
FOR EACH ROW
WHEN (NEW.P_M = YES)
BEGIN
INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID);
END P_M_YES;
/
ALTER TRIGGER "P_M_YES" ENABLE
/
I get an invalid trigger specification.
I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:
Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT
(...);
CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType;
PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
....
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));
....
How do I return the table (referenced by a cursor) without creating objects?
I am trying to create a csv file with summarized data. We have a huge table with claim details that is constantly being updated. I am pulling a subset of records that match my criteria into a tempory table (not technically an Oracle temporary table, a regular table that will only exist until I drop it when I am done). This table has multiple entries per claim with different effective dates, paid dates and amounts paid. The result file needs to have one entry per claim with the oldest effective and paid dates and the total of all of the amounts paid on that claim.
Originally I was doing create table new_table as select claim_nbr,other data...,min(ymdeff),min(ymdpaid),sum(amtpay) from my_table group by claim_nbr,other data...
If I ran a select sum(amtpay) from my_table and select sum(amtpay) from new_table I was not getting the same results... If I ran select count (*) from (select distinct claim_nbr from my_table) and select count (*) from (select distinct claim_nbr from new_table) or select count (*) from new_table I was getting the same number of rows. So I wasn't completely losing claims from one table to the next, just some of the details. So, I tried running this:
select * from
(select claim_nbr,sum(amtpay) paysum from my_table
group by claim_nbr
order by claim_nbr) m,
(select claim_nbr,sum(amtpay) paysum from new_table
group by claim_nbr
order by claim_nbr) n
where
m.claim_nbr = n.claim_nbr and
m.paysum <> n.paysum;
It came back with the claim number causing the issue. I looked at all the entries in my_table for that claim and every field was identical except the ymdeff, ymdpaid and amtpay. There were 4 records in my_table however the amtpay in new_table was only a sum of 2 of the records... I our admin look over my shoulder to see what was wrong and they wanted me to recreate new_table. So I dropped new_table and ran the exact same SQL to recreate the table. The number of distinct claim numbers was still the same in both tables and the sum of new_table was off but not by the same amount. I ran my comparison to see which claim was off and now there were two claims where the totals didn't match and neither were the same as the claim that was wrong that first time. We dropped new_table and recreated it several times and every time we got different results... No one else knows the name of my_table so no one was messing with it at the same time plus the sum of amtpay in my_table always comes back the same.
Our admin said he thought he remembered there being something "funny" with the min function sometime so he had me remove those fields. Ran the query several times and the total came out correct each time. Well I still need the dates so I came up with another way (very convoluted) using subqueries and ranking. It seemed to work at first then it started losing random numbers of claims (fewer rows in new_table than distinct claims in my_table) or keeping all the claims but dropping detail lines like I had using the min functions.
Here is the backwards way around using min that drops whole claims sometimes but works fine other times:
CREATE table new_table
as
(select claim_nbr,other data...,amtpay,ymdeff,ymdpaid
from
(select claim_nbr,other data... ,sum(amtpay) amtpay
from my_table
group by claim_nbr,other data...
[Code] ...........