16

In order to find out the users and the profile assigned you can use the commands below. DESC DBA_USERS; This will show you all the fields name for which you want to query SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS; And this command will show you the user name, profile and account status i.e. which profile is assigned to which user


14

This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm. Here is an example: https://www.orafaq.com/wiki/Binary CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS binval varchar2(64); N2 number := N; BEGIN while ( N2 > 0 ) loop binval := mod(N2, 2) || binval; N2 := trunc( N2 / 2 ); ...


10

First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure. begin hotel.fill_city(10000); end; / should correctly invoke your procedure. Second, you have issues with the naming of your local variables. Normally, you would not create local variables like city and postal_number that are the ...


9

The document that has the client/server compatibility matrix is 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions. (Oracle support account required) Oracle client version 12c is compatible with Oracle server version 11.2 (and below), so no your error isn't likely to come from that. Check that the listener is indeed ...


8

You can do this by using a save point. CREATE OR REPLACE PROCEDURE SPTest AS BEGIN -- We create a savepoint here. SAVEPOINT sp_sptest; INSERT INTO emptest(empid, empname, deptno) VALUES(1, 'ravi', 10); INSERT INTO test1(id, name, sal) VALUES(1, 'raju', 4444); UPDATE emptest SET empname = 'hari' WHERE empid = 1; -- ...


8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


7

Generate the statements you need select 'DELETE FROM '||table_name||' ;' from user_tables; Even better would be using TRUNCATE instead of DELETE. That would prevent generating extra redo vectors in the redo log - in the end all you want to do is just get rid of the data. It would also avoid all actions by any DELETE triggers that the tables have. select '...


7

Short version: CREATE TABLE Relation ( stu_id INT NOT NULL REFERENCES Student, par_id INT NOT NULL REFERENCES Parent, PRIMARY KEY (stu_id, par_id) ) ; Long version: Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and ...


7

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file. Your entry in tnsnames.ora file should be like this: AARCER1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524)) (CONNECT_DATA = (SID = ...


7

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';


7

Oracle SQL Developer ships with a report to find these unindexed columns. Here's the SQL behind this report: select a.owner "Owner", a.table_name "Table_Name", a.constraint_name "Constraint_Name", a.columns "Foreign_Key_Column_1", ...


7

There are different ways to achieve the goal. SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual; OR SQL> select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'YYYYMM') from dual; Regarding the second one, what happens if the SYSDATE or the current date supplied happens to be a leap day? SQL> select to_char(trunc(...


6

A new subpartition can be added to the existing partitioned table using the following command: ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS');


6

Because you are not logged in as the user system. When you execute conn system/password as sysdba, Oracle won't even check the user and password (--> from the local systeem where the db is running on). If you enter the system/password as sysdba and then execute 'show user', you'll notice you are logged in as user 'SYS'. So you are actually entering the wrong ...


6

That is the MySQL documentation, not the Oracle Database documentation. There is no such clause in Oracle Datababase. Use a trigger. Here is an example: link


6

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered. (This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ...


6

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( to_char(...


6

Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the child table. Why do you need to duplicate these columns in the child table? EDIT: Locking at you child table you say that: create table bit_2015_sep_cit4114_fyp_G_ ( ... , Full_Name VARCHAR2 (50) NOT NULL REFERENCES STUDENTINFO(Full_Name)...


6

The error has occurred because you are referencing a column in another table which is not unique. The good answers are already given by Lennart and Balazs Papp. I would like to explain why do we need a unique column in the parent table. As you said you want to keep duplicate values in the column used for the foreign key which is not possible while creating ...


6

Use grouping sets. You can try it here. select cat1, cat2, cat3, sum(val) as val from t1 join t2 on t1.f_id = t2.f_id group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3)) order by cat1, cat2 nulls first, cat3 nulls first OUTPUT cat1 cat2 cat3 val A 15 A a 10 A a aa 4 A a ab 6 A b 5 A b ba 4 A b bb ...


6

For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details. If you want to see 'the tables' - you need to expand the 'Other Users' node, and explore the users there, and go to their respective tables list. If you can query these objects in a worksheet w/o adding a SCHEMA. ...


6

SQL> create table t1 (c1 date); Table created. SQL> insert into t1 values (date'0000-01-05'); 1 row created. SQL> commit; Commit complete. SQL> select c1, to_char(c1, 'dd/MM/yyyy'), dump(c1) from t1; C1 TO_CHAR(C1 ------------------- ---------- DUMP(C1) -----------------------------------------------------------------------...


5

From ParnassusData: This error indicates that a mismatch has been detected between redo records and rollback (undo) records. ARGUMENTS: Arg [a] - Maximum Undo record number in Undo block Arg [b] - Undo record number from Redo block Since we are adding a new undo record to our undo block, we would expect that the new record number ...


5

I just wanted to chime in and share what got me past this error. In my case (Ora 11.2.0.3 on Win 7) I found I needed the trailing '\' in my directory path. Replaced: CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID' With: CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID\'


5

As the documentation describes: The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2. So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion. LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert ...


5

The standard approach to get rows N through M is to do something like SELECT * FROM (SELECT a.*, rownum rnum FROM (SELECT emp_id, last_name FROM employees WHERE positionID in (1,3) ORDER BY <<something>>) a WHERE rownum <= 60) b WHERE rnum > 50 Note that you need to ...


5

The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them): The order by has ties. There is not one "first row" but 3 of them. So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!


5

Alter the session: declare a number; begin EXECUTE IMMEDIATE 'ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE'; select count(*) into a from item w where w.Item_num='MOH601' ; dbms_output.put_line(a); end ;


5

From Oracle documentation- When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role. SQL> conn system Password: SQL> select * from session_roles; ROLE ------------------------------ CONNECT RESOURCE DBA SQL> show user; USER is "SYSTEM" SQL> grant execute on dbms_lock to jay; grant execute ...


5

The reason why your trigger does not work for users like SYS or SYSTEM is because they have the ADMINISTER DATABASE TRIGGER privilege. The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe. ...


Only top voted, non community-wiki answers of a minimum length are eligible