SQL & PL/SQL :: Created Table With Date In Database
Feb 22, 2011Me working on Oracle application Express,there are lots of tables created in it, how will i get to know who created which table and on which date.
View 7 Replies
ADVERTISEMENT
SQL & PL/SQL :: Date Search - No Index Created On Table
Feb 25, 2010DT1 is a column of date datatype and there is no index created on the table
I want to add the below lines in the sql
TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
Which one should I use in where condition to query and why?
1. TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
2. DT1 BETWEEN '01/01/2005' AND '31/12/2009' (as NLS date format will not change)
3. DT1 BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND ('31/12/2009' ,'dd/mm/yyyy')
Server Administration :: Find When Database Role Created And Who Has Created?
Feb 14, 2013Is there a way to find when was a database role created and who has created?
View 5 Replies View RelatedREFRESH GROUP Is Automatically Created When A Materialized View Is Created?
Aug 17, 2012tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies View RelatedForms :: Setup Form And Database Table For Time Entry Only (exclude Date)?
Feb 19, 2010I have a form that the user has to enter a time of an event.
The time entered will most likely be in the past and NOT the current time.
What is the best way to set up a form and a database table for time entry only (exclude the date)?
I have tried datetime on the form with format HH12:MM PM and date on the database.
I have also tried the same with a timestamp on the database.
The time always appears to get entered correctly. However, the time is always stored as AM regardless of what is specified by the user.
SQL & PL/SQL :: When Database Trigger Created
Jun 28, 2010if i want to know when (date/time) that the Database Trigger created? I am using Oracle 8i, so is it possible to check when (date/time) ?
View 2 Replies View RelatedPL/SQL :: Created Two Jobs In Sys Database
Mar 11, 2013I have created two jobs in sys database as follows,
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'JOB_FCDB_CHARGES',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN FCDBCHARGES; END;',
repeat_interval => 'FREQ=DAILY;BYHOUR=19',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Job to process Internet Banking Charges.',
auto_drop => FALSE,
enabled => FALSE);
[code]....
It has been successfully created.Even i get the entry in dba_objects table as follows,
select owner, object_name, object_type from dba_objects where owner = 'SYS' and object_name like '%JOB_FCDB_CHARGES%';
But, my problem is when i log in to PL/SQL developer i am not able to see anything under job.
Created Database Link But Does Not Function
Jun 19, 2012in a 9.2.0 db I create a db link but does not function :
CREATE DATABASE LINK "ONEDB"
CONNECT TO "user1" IDENTIFIED BY "****" USING 'ONEDB';
SQL> select * from dual@"ONEDB";
select * from dual@"ONEDB"
*
ERROR : ORA-12154: TNS:could not resolve service nameBut on the same server I can connect to 11g in sqlplus :
Z:>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mar. Juin 19 10:13:45 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect user1@ONEDB
password :
Connected.Of cours the other DB links toward 9.2.0 databases work well.
SQL & PL/SQL :: How To Know Index Is Created On Table Or Not
Jun 30, 2011how to know weather procedure is working or not in a database ..
how to know index is created on table or not.
PL/SQL :: How Many Foreign Key Can Be Created On Table
May 3, 2013in a table how many foreign keys can we create.
View 10 Replies View RelatedServer Administration :: Connect To Newly Created Database?
Jan 16, 2012i create new database (Student) using command line, now i want to connect with newly created database (using sys as sysdba), but again it directly connected with old database (ORCL). So what should i do to connect with newly created database (Student).
View 3 Replies View RelatedSQL & PL/SQL :: Created 2 Users Named User_a And User_b In Database
Mar 2, 2012I have created 2 users named "user_a" and "user_b" in my database and i gave "connect","dba" and "resource" roles to both users,then i created a table,synonym in user_a and i observe that user_b can also access this table and synonym of user_a without "granting" to "user_b", how it is possible. is it because of any of above role?
View 12 Replies View RelatedSecurity :: Fire Trigger If And Database Object Will Created?
Nov 7, 2013How to write and fire trigger if any database object will created?
View 1 Replies View RelatedSQL & PL/SQL :: Created Trigger On Database Level In System Schema
Jun 10, 2010I 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;
SQL & PL/SQL :: Make Partition Of Table Which Is Already Created
Sep 21, 2011I want to make partition of table which is already created and has huge amount of Data I have done indexing already
my structure are as follows
EMPLOYEE CHAR(10),
MONTH_YEAR NUMBER (6),
TYPE CHAR (1),
SALARY_CODE CHAR (5),
AMOUNT NUMBER (16,2)
Indexing on Employee,Month,Salary_Code
How can I do partitioning on this table.
SQL & PL/SQL :: How To Find Who Created The Table In Oracle
Feb 8, 2011somebody has created the table , who had created the table Note: not required the schema name,
View 4 Replies View RelatedSecurity :: View / Obtain Password Once Database Link Has Been Created?
Dec 24, 2011Is there a way to view/obtain the password once a database link has been created. Some sys dba table?
View 7 Replies View RelatedSecurity :: How To Edit Created Policy (Virtual Private Database)
Mar 28, 2012I Created the Security Policy as user sysadmin_vpd as follows
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'scott',
object_name => 'orders_tab',
policy_name => 'orders_policy',
function_schema => 'sysadmin_vpd',
policy_function => 'get_user_orders',
statement_types => 'select');
END;
After creation this policy I want to view & edit them.By default where they are saved.
Backup & Recovery :: RMAN Configuration For Newly Created Database
Oct 1, 2011i am trying to configure rman for newly created database .when i entered
rman target /
it is throwing following error
[/u01/app/oracle/product]rman target /
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12162: TNS:service name is incorrectly specified
Server Utilities :: How To Import Backup On Newly Created Database Using Imp
Nov 18, 2011I have near 114 export.dmp.z* export backup. I am trying to import it on newly created database using imp.
But i am not getting how can i select all export.dmp.z* files using imp. Its easy in impdp, but i have exported backup.
Server Administration :: How Many Users Can Be Created In Oracle 11g Release 2 Database
Dec 27, 2012how many users we can create in oracle 11g release 2 database. Is there any specific limit or parameter for that.
View 1 Replies View RelatedAutomatic Storage Management :: 10.2.0.1 RAC - ASM Created - DBCA Could Not Create Database
Jul 30, 2012I am using 10.2.0.1 on OEL5. I have installed CRS, Oracle home, ASM on both nodes and everything is fine.
When invoked dbca to create a database, it says:
*'DBCA could not startup the ASM instance configured on this node. To proceed with database creation using ASM you need the ASM instance to be up and running. Do you want to recreate the ASM instance on this node?'*
Is it a bug? because some blogs say this is a bug. And patch 8288940 will solve this. They also say this patch is to solve the incompatibility between 11g ASM incompatibility with 10g. But here I am using everything of 10.2.0.1.
Forms :: Use Created Table In Data Block?
May 1, 2013I have data that the user is modifying on the form and a data block that is supposed to reflect those change (summaries) So I created
TYPE VehicleInfo IS RECORD (Vehicle_Desc ctl_fleet_vehicles.Vehicle_Desc%Type,
Miles timecapture.miles%Type,
Hours TimeCapture.Equipment_hours%Type);
TYPE VehicleTab IS TABLE OF VehicleInfo
INDEX BY BINARY_INTEGER;
VehicleObj VehicleTab;
I fill this up with the correct data, but I would like to be able to use this as a table in the data block (called Vehicle_Summary) In the past I have piped tables into data blocks from the server, but I'm unsure how to use a table I created in forms in the Query Data Source Name of the data block.
PL/SQL :: Created A Table With CLOB As Datatype For One Of Columns
Aug 5, 2013<ORACLE VERSION : 11.2.0.2.0> i have created a table with CLOB as datatype for one of the columns, I am trying to store a string ( I am not sure about the length of the string) , when i am querying on my table for the CLOB column,instead of the actual string "(HUGECLOB)" is coming. How to get the actual string in case the problem is with the SIZE.
View 1 Replies View RelatedPL/SQL :: Created Partition And Parallel Degree 4 On Table A
Oct 10, 2012I have table A with size 120 Million and two more tables are of size 2 Million on Table B and less than 1 Million size on table C.I had created Partition and Parallel degree 4 on the table A. Created table B with Parallel degree 2 and Created table C with NOPARALLEL.
My query is using above tables with joins and inserting into table D using HINT /*+ APPEND NOLOGGING*/I had executed the explain on the above criteria the cost is showing 20767.
Later created Tables A,B and C with NOPARALLEL. Applied HINT on Table D /*+ APPEND NOLOGGING*/ and als applied HINT /*+ PARALLEL(A, 4) PARALLEL(B C, 2) on select query which uses to insert into Table D.
My question which is best practice on PARALLEL degree creation at table level or query level:
a) Creating table with Paralle (degree 4)
b) Applying HINT /*+ PARALLEL (TABLE A , 4) */ at query level
SQL & PL/SQL :: Created Function For Knowing Status Of Link Which Connect With Remote Database
May 22, 2013I have created a function for knowing the status of link which connect me with remote database.function is as follows
---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_status_db_link (p_db_link_in VARCHAR2)
RETURN NUMBER AS
rows NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);
[code]....
Now functin should returns 1 when link is up and it should return 0 when link down but sometime when link is down it continues showing executing it hang my pc.
Server Utilities :: Load 780 CSV Files Into 12 Tables Created In Database - Sql Loader?
Jul 22, 2011I have 780(12*65) csv files generated from 65 databases.Now I have to load this 780 csv files into 12 tables created in my database for some monitoring and reporting purpose.to call the sql loader I am plannig to create 780 lines like below.
sqlldr abc@tns/pwd control='E:htmlctlhtml_broken_jobs_rpt.ctl' log='E:htmlreportloghtml_broken_jobs_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_db_size_rpt.ctl' log='E:htmlreportloghtml_db_size_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_fragmentation_rpt.ctl' log='E:htmlreportloghtml_fragmentation_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_index_stats_rpt.ctl' log='E:htmlreportloghtml_index_stats_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_invalid_object_rpt.ctl' log='E:htmlreportloghtml_invalid_object_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_long_running_queries_rpt.ctl' log='E:htmlreportloghtml_long_running_queries_rpt.log'
we know creating 780 control files is the difficult task.So I have created only 12 control files. is there any mechanism to pass a varible (planning to declare it in the sqlldr line) to the infile clause like below in sql loader?
infile "E:htmlreportoutput&a_html_broken_jobs_rpt.csv"
here a is the variable name. it will change every 12 csv files once.
or
is there anyother way to achive this?
SQL & PL/SQL :: Employee Table In Scott Schema Created By Default
Oct 3, 2011I am using Oracle 11g Release 11.2.0.1.0
OS: Windows
I am using the Employee table in Scott schema which created by default.
10,20,30,40,50 are the department ids.
I would like to have the output like below. I am having lots of values in dep id and lots of employees in each dept ids
Emp name Dep ID
John 20
Mike 40
Ram 10
Guru 50
Kumar 30
Can Partition A Table Based On Date If It Does Not Have A Date Column
Jun 21, 2012How can we partition a table based on date if it does not have a date column.
Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.
I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.
Forms :: Created Block For Table On Tabular Style - Validating Item?
Oct 10, 2011I created a block for EMP table on tabular style. if in first line empno entered is 1then i don't want to allow the end user to enter 1 in second line. but when-validate-item is not working because first_record and next_record is not allowed. I need to validate before saving the data into emp table and immediately after empno is entered into empno item.
View 16 Replies View Related